Databases have "Recovery Pending" status every few hours

0

I recently noticed that most of the databases on my SQL Server are getting the Recovery Pending status after running for a few hours (sometimes 6, sometimes more). I can't imagine this being the expected outcome, so I decided to post about it here.

I opened the Event Log viewer and decided to find some more info about what's going on. It seems that all my databases are "Starting up" every few minutes or so (as cam be seen here). Some of them get started multiple times in a row. Is that supposed to work like that?

After scrolling a bit more, I found this warning from one of my IIS websites:

Event code: 3005 
Event message: An unhandled exception has occurred. 
Event time: 4/11/2018 9:32:05 AM 
Event time (UTC): 4/11/2018 7:32:05 AM 
Event ID: 7911bb726cd34d36941e8232ad78b059 
Event sequence: 18 
Event occurrence: 3 
Event detail code: 0 

Application information: 
    Application domain: /LM/W3SVC/5/ROOT-1-131679051910980983 
    Trust level: Full 
    Application Virtual Path: / 
    Application Path: C:\inetpub\x\ 
    Machine name: WIN-IV6CMBTUQ4N 

Process information: 
    Process ID: 2400 
    Process name: w3wp.exe 
    Account name: IIS APPPOOL\x

Exception information: 
    Exception type: HttpException 
    Exception message: The remote host closed the connection. The error code is 0x80070057.
at System.Web.Hosting.IIS7WorkerRequest.RaiseCommunicationError(Int32 result, Boolean throwOnDisconnect)
at System.Web.Hosting.IIS7WorkerRequest.ExplicitFlush()
at System.Web.HttpResponse.Flush(Boolean finalFlush, Boolean async)
at System.Web.HttpWriter.WriteFromStream(Byte[] data, Int32 offset, Int32 size)
at Microsoft.Owin.Host.SystemWeb.CallStreams.OutputStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at Microsoft.AspNet.SignalR.Owin.ServerResponse.Write(ArraySegment`1 data)
at Microsoft.AspNet.SignalR.Hosting.ResponseExtensions.End(IResponse response, String data)
at Microsoft.AspNet.SignalR.TaskAsyncHelper.TaskRunners`2.<>c__DisplayClass2_0.<RunTask>b__0(Task t)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.Owin.Mapping.MapMiddleware.<Invoke>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.StageAsyncResult.End(IAsyncResult ar)
at System.Web.HttpApplication.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)



Request information: 
    Request URL: http://91.134.116.40:8003/signalr/start?clientProtocol=1.4&transport=webSockets&connectionData=[{"Name":"ChatHub"}]&connectionToken=/y39a+1XOv/u7X/ZFYw5ACLQu58FVNl0hbkMwPHCw1EQ9gdCbpFxPQzPuJVDxRywoP2AaI6akuXxQIxM2Wwa0OLO4cWd/e3csUBetjb9nROsisQnavw52ulTHWVOjK/j&user=User-14D 
    Request path: /signalr/start 
    User host address: 24.13.101.142 
    User:  
    Is authenticated: False 
    Authentication Type:  
    Thread account name: IIS APPPOOL\x

Thread information: 
    Thread ID: 14 
    Thread account name: IIS APPPOOL\x
    Is impersonating: False 
    Stack trace:    at System.Web.Hosting.IIS7WorkerRequest.RaiseCommunicationError(Int32 result, Boolean throwOnDisconnect)
at System.Web.Hosting.IIS7WorkerRequest.ExplicitFlush()
at System.Web.HttpResponse.Flush(Boolean finalFlush, Boolean async)
at System.Web.HttpWriter.WriteFromStream(Byte[] data, Int32 offset, Int32 size)
at Microsoft.Owin.Host.SystemWeb.CallStreams.OutputStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at Microsoft.AspNet.SignalR.Owin.ServerResponse.Write(ArraySegment`1 data)
at Microsoft.AspNet.SignalR.Hosting.ResponseExtensions.End(IResponse response, String data)
at Microsoft.AspNet.SignalR.TaskAsyncHelper.TaskRunners`2.<>c__DisplayClass2_0.<RunTask>b__0(Task t)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.Owin.Mapping.MapMiddleware.<Invoke>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.Owin.Host.SystemWeb.IntegratedPipeline.StageAsyncResult.End(IAsyncResult ar)
at System.Web.HttpApplication.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)


Custom event details: 

Seems like there's something wrong with SignalR, so it shouldn't have any direct relation with the database, but there aren't any other errors/warnings there.

Am I missing something here?

Thank you!

asp.net
sql-server
iis
sql-server-2016
asked on Stack Overflow Apr 11, 2018 by Reynevan

1 Answer

2

Do you have "auto close" enabled on the database?

Best practice is to have it OFF

ALTER DATABASE <mydb> SET AUTO_CLOSE OFF

I would also check AUTO_SHRINK while I was there

answered on Stack Overflow Apr 11, 2018 by gbn

User contributions licensed under CC BY-SA 3.0