none
SQL Server Instance Restarts after Shutdown

    Question

  • We were restarting a server running SQL Server. Prior to the restarting the server, we issued a shutdown via SQL Management Studio on the local SQL instance. This killed my SMS connection as expected but to my surprise the SQL service restarted. I connected to the instance again, issued the shutdown again and the same behavior. The third shutdown attempt and the instance finally shutdown without restarting. On the other servers we rebooted that night, some shutdown as expected and others restarted and took as many as 6 shutdown commands before the service finally stopped.

    We do have the "Auto restart SQL Server if it stops unexpectedly" option enabled for our SQL Server Agent. In all of scenarios where SQL restarted, event viewer shows an Event ID 19019 "The MSSQLSERVER service terminated unexpectedly"

    This surprised me as we issued a formal shutdown command.

    Any help understanding this or how we encountered this would be appreciated.

     

    Tuesday, June 29, 2010 9:28 PM

All replies

  • This is normal and expected behavior since you have the service set to restart.  If you do not want it to restart, use Services to set the service to "Manual" mode or uncheck the "restart if stops unexpectedly".

     

    Tuesday, June 29, 2010 9:41 PM
  • But the behavior is not consistent! Sometimes SQL shuts down cleanly and other times it generates the "The MSSQLSERVER service terminated unexpectedly" error.

    Tuesday, June 29, 2010 10:28 PM
  • Did you stop the service (suing any of the pethora of moethods and tools out there), or did you execute the TSQL SHOTDOWN command from a query window? If the later, did you use the NOWAIT option? IT wouldn't answer the question, but as a part of the troubleshooting process... Perhaps Agent considers a SHUTDOWN command a more "hard" termination compared to stopping the service?

    Also, did you check the SQL Server errorlog file, if there is any clue to why it thinks the termination was not normal (if that is the case)?


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, June 30, 2010 7:36 AM
  • We issued the SHUTDOWN via T-SQL without the NOWAIT option. I checked the SQL error log and in some cases where the service restarted there were connection attemps where SQL was refusing the connection to the database as it was shutting down (SQL Server cannot accept new connections, because it is shutting down. The connection has been closed.).

    Wednesday, June 30, 2010 7:00 PM
  • I see... I tried a few things on my test-server. SHUTDOWN didn't trigger re-start (whether or not used with NOWAIT). Killing the sqlservr.exe process did (as expected).

    Seems in your case that Agent considers SQL Server to unexpected shutdown when it shouldn't (since you use the SHUTODWN command). I guess the key here is what Agent uses to determine that. Possibly, timing related or such. But I don't know that, I'm afraid, and I couldn't find anything in the documentation. I've asked around, though, and I will post back if I get any replies...


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Thursday, July 01, 2010 7:27 AM
  • Hi,

    Do you set  the recovery actions for your SQL Server service by using the Services snap-in ? Please check the recovery actions setting for your SQL Server Service? If a SQL Server service fails, you could use the Services snap-in to specify what action to take on the first or second service failure, or on subsequent failures. To verify the recovery actions setting of SQL Server service, try the following steps:
    1. Click Start, in the Start Search box, type services.msc, and then press ENTER.
    2. In the details pane, right-click the service for which you want to set up recovery actions, and then click Properties.
    3. Go to the Recovery tab to check the recovery actions.

    If you have any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, July 02, 2010 7:12 AM
  • I got a reply from MS, we'll see if he jumps in to this thread as well. Seems like Agent and Engine has a shared memory segment over which they communicate these things. I would consider what you noticed a bug, and would file a connect item for it. Problematic part might be to repro, though...
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Sunday, July 04, 2010 9:01 AM
  • We do not have any actions for the services on the Recovery tab. One of my operations guys had thought the same thing.
    Tuesday, July 06, 2010 2:58 PM
  • Getting MS on the thread would be awesome TiborK. Thank you!

    I have had some luck reproducing it just not at a consistent rate. I'll give it a day to see if they respond otherwise I may go the bug route.

    Tuesday, July 06, 2010 3:00 PM
  • Any additional thoughts on this?
    Thursday, July 08, 2010 3:23 PM