SQL Server – 60 Minutes to Shut Down SQL Server Service

sql server

We recently merged with a new company and they have a server that has 265 DBs on it (6.6tb of mdfs). Shortly after we merged the other DBA said it takes 60 minutes to shut down the services which I thought was incredibly high. Last night they stopped and started the services and sure enough it took 60 minutes. I looked at the log this morning and found the following error messages in the sql log file while it was in the process of trying to stop the service.

I know SQL takes a longer time to start back up if you have a lot of VLFs, but it only took 1 minute from the time it started up until the Recovery Complete message appeared in the log. Any idea if 60 mins is normal? Or if not, what can I do to investigate speeding this up. Thanks!

 Error: 17142, Severity: 14, State: 0.
 SQL Server service has been paused. No new connections will be allowed. To resume the     
 service, use SQL Computer Manager or the Services application in Control Panel.

 Error: 18456, Severity: 14, State: 13.
 Login failed for user ''. Reason: SQL Server service is paused. 
 No new connections can be accepted at this time. [CLIENT: xx.xxx.xx.xx]

Best Answer

It shouldn't take 60 minutes to shutdown the server. As you mentioned, the DBA is performing a pause before shutting down - this is probably the major contributor to the slow shutdown.

Unless he's planning to resume the database, there isn't normally reason to pause. Removing this step from the procedure should cut down a significant portion of the 60 minutes.

Here is an example of a log that shows a pause followed by a shutdown.

The pause begins at:

SQL Server IS NOT allowing NEW connections because the Service Control Manager
requested a pause. TO resume the service<c/> USE SQL Computer Manager OR the Services
application IN Control Panel.

And the shutdown sequence begins with:

SQL Server IS terminating IN response TO a 'stop' request FROM Service Control
Manager. This IS an informational message ONLY. No USER action IS required.

Check with the DBA first though, to make sure there isn't a specialized case that necessitates the pause.

Related Question