Cannot open database error every time

Question

I have a local Sitecore 8.2 instance.
Whenever, I browse this instance for the first time in a day or each time I deploy a dll, it throws this error:

Cannot open database "MySiteSitecore_Web" requested by the login. The login failed. Login failed for user 'sa'.

To fix this, I have to restart the SQL service. I check the databases if they have gone into recovery mode, but they are good. The DB is also installed in my local machine.

Sometimes, I have to also do an iisreset, then refresh the page a few times with a gap of a few seconds after each refresh.

Why does this happen every time and how can I resolve this.

Stack Trace:

[SqlException (0x80131904): Cannot open database "MySiteSitecore_Web" requested by the login. The login failed. Login failed for user 'sa'.] System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +353
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +118
System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +268
System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) +315
System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) +128
System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) +265 System.Data.SqlClient.SqlConnection.Open() +133
Sitecore.Data.DataProviders.Sql.DataProviderCommand..ctor(IDbCommand command, DataProviderTransaction transaction, Boolean openConnection) +113 Sitecore.Data.DataProviders.Sql.<>c__DisplayClass4.<CreateCommand>b__3() +48 Sitecore.Data.DataProviders.NullRetryer.Execute(Func1 action, Action recover) +293
Sitecore.Data.DataProviders.Sql.<>c__DisplayClass12.b__10() +30 Sitecore.Data.DataProviders.NullRetryer.Execute(Func1 action, Action recover) +293
Sitecore.Data.DataProviders.Sql.SqlDataApi.CreateReader(String sql, Object[] parameters) +281
Sitecore.Data.DataProviders.Sql.SqlDataProvider.GetContentLanguages() +189 Sitecore.Data.DataProviders.Sql.SqlDataProvider.LoadLanguages() +133
Sitecore.Data.DataProviders.Sql.SqlDataProvider.GetLanguages() +49
Sitecore.Data.SqlServer.SqlServerDataProvider.ExecuteLoadItemDefinitionsSql(String sql, Object[] parameters, SafeDictionary2 prefetchData) +90
Sitecore.Data.DataProviders.Sql.SqlDataProvider.EnsureInitialPrefetch() +359 Sitecore.Data.DataProviders.Sql.SqlDataProvider.GetPrefetchData(ID itemId) +43
Sitecore.Data.DataProviders.Sql.SqlDataProvider.GetItemDefinition(ID itemId, CallContext context) +9
Sitecore.Data.DataProviders.DataProvider.GetItemDefinition(ID itemID, CallContext context, DataProviderCollection providers) +156
Sitecore.Data.DataSource.GetItemInformation(ID itemID) +88
Sitecore.Data.DataSource.GetItemData(ID itemID, Language language, Version version) +31
Sitecore.Nexus.Data.DataCommands.GetItemCommand.GetItem(ID , Language , Version , Database ) +113
Sitecore.Nexus.Data.DataCommands.GetItemCommand.Execute(ID , Language , Version , Database ) +496
Sitecore.Data.Engines.DataCommands.GetItemCommand.DoExecute() +142
Sitecore.Data.Engines.EngineCommand`2.Execute() +93
Sitecore.Data.Managers.ItemProvider.GetItem(ID itemId, Language language, Version version, Database database) +295
Sitecore.Data.Managers.ItemProvider.GetItem(ID itemId, Language language, Version version, Database database, SecurityCheck securityCheck) +141
Sitecore.ContentTesting.Pipelines.ItemProvider.GetItem.GetItemUnderTestProcessor.Process(GetItemArgs args) +185 (Object , Object[] ) +74
Sitecore.Pipelines.CorePipeline.Run(PipelineArgs args) +475
Sitecore.Data.Managers.DefaultItemManager.GetItem(ID itemId, Language language, Version version, Database database, SecurityCheck securityCheck) +297
Sitecore.Data.Managers.DefaultItemManager.GetItem(ID itemId, Language language, Version version, Database database) +147
Sitecore.Data.Managers.ItemManager.GetItem(ID itemId, Language language, Version version, Database database) +154
Sitecore.Data.DeviceRecords.DoGetAll() +77
Sitecore.Data.DeviceRecords.GetAll() +44
Sitecore.Data.Items.DeviceItem.FindBestMatch(Database database, HttpContextBase httpContext) +65
Sitecore.Data.Items.DeviceItem.ResolveDevice(Database database, HttpContextBase httpContext) +131
Sitecore.Pipelines.HttpRequest.DeviceResolver.Process(HttpRequestArgs args) +303 (Object , Object[] ) +74
Sitecore.Pipelines.CorePipeline.Run(PipelineArgs args) +475
Sitecore.Pipelines.DefaultCorePipelineManager.Run(String pipelineName, PipelineArgs args, String pipelineDomain) +22
Sitecore.Nexus.Web.HttpModule.(Object , EventArgs ) +515
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +142 System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +75 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +93

Answer 1

Since this is happening every time you publish a dll, it does make me feel like publishing ìs overriding your writing permissions to database. Does your .pubxml file contains the following?

<IncludeSetACLProviderOnDestination>False</IncludeSetACLProviderOnDestination>

Answer 2

From the description above, there is a possibility that you might have set the Maximum number of concurrent connections for your SQL server. And as soon as that limit exceeds, you start getting this issue. And when you restart your SQL Server service or IIS(that shutdown the applications using the connection), the connections are available back in the pool and hence things start to work.

Please check the limit here:

I hope this helps.