1

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]
Improve this question
8
  • What are the specs on the server and SQL version?
    – LowlyDBA - John M
    Dec 4, 2014 at 14:41
  • A bit more info I just received, he pauses the service first (which is why I see those errors). Is there a benefit to that? I have always just stopped the services directly.
    – Chris Woods
    Dec 4, 2014 at 14:46
  • @JohnM Its a Intel(R) Xeon(R) CPU E5-4650 0 @ 2.70GHz with 1tb of memory. Microsoft SQL Server 2012 (SP1) - 11.0.3412.0 (X64) Mar 2 2014 01:25:09 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) Let me know if there is anything else specifically you are looking for.
    – Chris Woods
    Dec 4, 2014 at 14:48
  • Oh, and it has 32 cores.
    – Chris Woods
    Dec 4, 2014 at 14:52
  • I don't think pausing before a shutdown is really necessary, do you know what portion of that 60 minutes is allocated to the pausing versus the actual shutdown?
    – LowlyDBA - John M
    Dec 4, 2014 at 14:53

2 Answers 2

Reset to default
3

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.

Improve this answer
1
  • I would imagine when you Stop a service which is Paused, it has to Continue it before it can further Stop it.
    – Jerry Dodge
    Aug 23, 2017 at 21:57
2

The answer to how much time it will take to stop a sql server instance will depend on :

Pausing a SQL Server is different as

When you pause an instance of Microsoft SQL Server, users that are connected to the server can finish tasks, but new connections are not allowed. For example, you can pause an instance of SQL Server for a few minutes and send a shutdown message to connected users before shutting it down. You can also resume a SQL Server service.

So to answer your question,

60 minutes to shut down sql server service?

Ideally, it should not take 60 mins , but again it depends on what you are doing. If you are pausing and then stopping sql server then it probably might.

If your entire goal is to stop sql server, then I would suggest you to (in maintenance window) :

  • Shutdown your web application. This will kill most of the connections to sql server.

  • Then shutdown sql server using :

    • Stop sql server using Services console .
    • Shutting down your sql server using configuration manager.
    • running SHUTDOWN command in SSMS (do not use SHUTDOWN with NOWAIT !)
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.