none
Service Broker Manager issued a SQL shutdown

    Question

  • I have SQL 2008 R2 Base (10.50.1600) installed on a server running Windows 2008 R2 (no service pack).

    SQL log shows the final DB backup completed at 12:24AM.  There is nothing in the log until 3:18AM when you see SQL getting shut down.  It didn't come back up until I restarted it later in the morning.

    2013-08-20 00:24:53.72 Backup      Database backed up. Database: FlowCastArchive, creation date(time): 2012/12/21(07:03:21), pages dumped: 474, first LSN: 118:13731:48, last LSN: 118:13750:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'G:\Backups\User\FlowCastArchive\FlowCastArchive_backup_2013_08_20_002304_0936847.bak'}). This is an informational message only. No user action is required.

    2013-08-20 03:18:18.19 spid52      Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.

    2013-08-20 03:18:18.19 spid52      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.

    2013-08-20 03:18:20.00 spid14s     Service Broker manager has shut down.

    2013-08-20 03:18:22.73 spid7s      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.

    2013-08-20 03:18:22.76 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

    2013-08-20 03:18:22.86 Server      The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/PMC-PIA-DB17.intranet.osfnet.org ] for the SQL Server service.

    2013-08-20 03:18:22.86 Server      The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/PMC-PIA-DB17.intranet.osfnet.org:1433 ] for the SQL Server service.

    SQL Agent error log shows

    2013-08-20 03:18:16 - ? [131] SQLSERVERAGENT service stopping due to a stop request from a user, process, or the OS...

    2013-08-20 03:18:18 - ? [098] SQLServerAgent terminated (normally)

    Windows error logs show me nothing more than a SQL Server shutdown taking place at 3:18AM.  In the event log shutdown entry, the userID that performed the shutdown was the service account we run SQL under. 

    I don't have a dump file. The Windows guys tell me there were no MS updates being applied. The SQL Services all have the Recovery tab configured to auto-restart SQL (twice) if it goes down unexpectedly.  From what I can tell, it was a graceful shutdown.  It never tried to restart.

    I checked the default trace file and saw an application admin connected to a DB on the server at 3:13AM.  He claims he never got connected to the DB but the command shows as being successful in the trace file.  He does have admin privs on the machine. 

    I need to provide a root cause for this downtime.  I have been at this for hours.  How can I determine if the OS shut SQL down or if something else did?  I think I have read every post out there.  Does anyone have any suggestions as to other things I should look at? 

    Wednesday, August 21, 2013 12:47 PM

Answers

All replies

  • 2013-08-20 03:18:20.00 spid14s     Service Broker manager has shut down.

    <span2013-08-20 03:18:22.73="" spid7s      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.</span2013-08-20>

    Hello,

    That's a misunderstanding, not the Service Broker issues a shut down of SQL Server. Service Broker just shut down first, because it depends on the database engine.

    The issue is, the a stop request was send to the service, therefore the SQL Server shutdown.

    You should check Windows EventLog to see what happens around the time.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Kevin Urquhart Wednesday, August 21, 2013 1:32 PM
    Wednesday, August 21, 2013 1:21 PM
  • The Windows system log entries are as such

    2:54AM  The Application Experience service entered the stopped state.

    3:01AM  The WinHTTP Web Proxy Auto-Discovery Service service entered the running state.

    3:18 AM  The SQL Server Agent (MSSQLSERVER) service entered the stopped state.

    The Windows application log entries show the last backup at 12:24AM and the SQL shutdown at 3:18AM.

    I am looking for some clue as to why it shut down and who initiated it.  I can't find anything that even gives me a hint as to why.

    Wednesday, August 21, 2013 2:06 PM
  • The issue I have it that this SQL instance has about 20 application DBs on it.  The shut down affected loads of people.  I am just at a loss as to figuring out why it happened.  The normal places I would look just aren't providing any clues.  I appreciate any clues you can throw my way.  Thank you.

    Wednesday, August 21, 2013 2:23 PM
  • I have SQL 2008 R2 Base (10.50.1600) installed on a server running Windows 2008 R2 (no service pack).

    Hello Jarvis,

     Adding to what Olaf said it seems your SQL server 2008 R2 is not patched to latest SP i.e SP2 .SP1 has a fix which fixes unexpected restart of SQL server 2008 R2 ,so this might be cause I am not sure more details can be found out from eventviewer and running below command and other windows logs.

    I also recommend you to apply SP for windows server,its really dangerous you are running both OS and app without latest SP

    Sp_readerrorlog 1 

    SQL server 2008 R2 SP2 download

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Wednesday, August 21, 2013 2:45 PM typo
    Wednesday, August 21, 2013 2:43 PM
  • I am afraid, but it will be difficult to check who may stopped the service; see thread To find the user who shut down the SQL Server

    You may have a look at the Windows EventLog => Security to see, who logged on during that time.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, August 21, 2013 2:55 PM
  • Thank you both.  I will mark this as answered. 
    • Marked as answer by OSFRKJarvis Wednesday, August 21, 2013 3:03 PM
    Wednesday, August 21, 2013 3:02 PM
  • I am sorry that we can't provide a better help at this moment.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, August 21, 2013 3:06 PM