none
SQL Server 2012 Shutting Down Randomly

    Question

  • Hello,

    I have two servers with Windows Server 2008 R2 and SQL Server 2012 Standard that were just setup about a week ago. Both SQL Server instances will randomly stop the service and become disabled. I have been reviewing the SQL Server Logs in C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log and I am not seeing any issues that would cause it.

    Edit: I am figuring that it is not a coincidence that is happening to two different servers setup at the same time. One with Reporting Services and the other with Integration Services.

    Here is the log file:

    2013-05-16 15:15:07.55 Server      Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
    Oct 19 2012 13:38:57
    Copyright (c) Microsoft Corporation
     Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    2013-05-16 15:15:07.55 Server      (c) Microsoft Corporation.
    2013-05-16 15:15:07.55 Server      All rights reserved.
    2013-05-16 15:15:07.55 Server      Server process ID is 3380.
    2013-05-16 15:15:07.55 Server      System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.
    2013-05-16 15:15:07.55 Server      Authentication mode is WINDOWS-ONLY.
    2013-05-16 15:15:07.55 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
    2013-05-16 15:15:07.55 Server      The service account is '<Service Account>'. This is an informational message; no user action is required.
    2013-05-16 15:15:07.55 Server      Registry startup parameters:
      -d C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf
      -e C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG
      -l C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
    2013-05-16 15:15:07.57 Server      Command Line Startup Parameters:
      -s "MSSQLSERVER"
    2013-05-16 15:15:07.76 Server      SQL Server detected 2 sockets with 2 cores per socket and 2 logical processors per socket, 4 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
    2013-05-16 15:15:07.76 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2013-05-16 15:15:07.76 Server      Detected 8191 MB of RAM. This is an informational message; no user action is required.
    2013-05-16 15:15:07.76 Server      Using conventional memory in the memory manager.
    2013-05-16 15:15:07.79 Server      Error: 8319, Severity: 16, State: 1.
    2013-05-16 15:15:07.79 Server      Windows kernel object 'Global\SQL_110_MEMOBJ_24_MSSQLSERVER_0' already exists. It's not owned by the SQL Server service account. SQL Server performance counters are disabled.
    2013-05-16 15:15:07.79 Server      Error: 3409, Severity: 16, State: 1.
    2013-05-16 15:15:07.79 Server      Performance counter shared memory setup failed with error -1. Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions.
    2013-05-16 15:15:07.82 Server      Perfmon counters for resource governor pools and groups failed to initialize and are disabled.
    2013-05-16 15:15:07.83 Server      This instance of SQL Server last reported using a process ID of 1740 at 5/16/2013 3:15:02 PM (local) 5/16/2013 8:15:02 PM (UTC). This is an informational message only; no user action is required.
    2013-05-16 15:15:07.83 Server      Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    2013-05-16 15:15:07.84 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
    2013-05-16 15:15:07.85 Server      Software Usage Metrics is disabled.
    2013-05-16 15:15:07.85 spid5s      Starting up database 'master'.
    2013-05-16 15:15:07.94 Server      CLR version v4.0.30319 loaded.
    2013-05-16 15:15:07.98 Server      Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
    2013-05-16 15:15:08.70 spid5s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
    2013-05-16 15:15:08.70 spid5s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
    2013-05-16 15:15:08.76 spid5s      SQL Trace ID 1 was started by login "sa".
    2013-05-16 15:15:08.79 spid5s      Server name is '<Server Name>'. This is an informational message only. No user action is required.
    2013-05-16 15:15:08.81 spid5s      Failed to verify Authenticode signature on DLL 'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\ftimport.dll'.
    2013-05-16 15:15:08.81 spid13s     Starting up database 'msdb'.
    2013-05-16 15:15:08.81 spid16s     Starting up database 'red'.
    2013-05-16 15:15:08.81 spid9s      Starting up database 'mssqlsystemresource'.
    2013-05-16 15:15:08.82 spid17s     Starting up database 'SSISDB'.
    2013-05-16 15:15:08.83 spid9s      The resource database build version is 11.00.3000. This is an informational message only. No user action is required.
    2013-05-16 15:15:09.02 spid12s     A self-generated certificate was successfully loaded for encryption.
    2013-05-16 15:15:09.02 spid12s     Server is listening on [ 'any' <ipv6> 1433].
    2013-05-16 15:15:09.03 spid12s     Server is listening on [ 'any' <ipv4> 1433].
    2013-05-16 15:15:09.03 spid12s     Server local connection provider is ready to accept connection on [  ].
    2013-05-16 15:15:09.03 spid12s     Server local connection provider is ready to accept connection on [ ].
    2013-05-16 15:15:09.03 Server      Server is listening on [ ::1 <ipv6> 1434].
    2013-05-16 15:15:09.03 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
    2013-05-16 15:15:09.03 Server      Dedicated admin connection support was established for listening locally on port 1434.
    2013-05-16 15:15:09.04 Server      SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
    2013-05-16 15:15:09.04 spid12s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2013-05-16 15:15:09.07 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc<FQDN>/ ] for the SQL Server service. Windows return code: 0x2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
    2013-05-16 15:15:09.07 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc<FQDN>/:1433 ] for the SQL Server service. Windows return code: 0x2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
    2013-05-16 15:15:09.58 spid9s      Starting up database 'model'.
    2013-05-16 15:15:09.74 spid9s      Clearing tempdb database.
    2013-05-16 15:15:10.00 spid9s      Starting up database 'tempdb'.
    2013-05-16 15:15:10.12 spid18s     The Service Broker endpoint is in disabled or stopped state.
    2013-05-16 15:15:10.12 spid18s     The Database Mirroring endpoint is in disabled or stopped state.
    2013-05-16 15:15:10.13 spid18s     Service Broker manager has started.
    2013-05-16 15:15:10.27 spid16s     CHECKDB for database 'red' finished without errors on 2013-05-16 10:03:37.250 (local time). This is an informational message only; no user action is required.
    2013-05-16 15:15:10.28 spid5s      Recovery is complete. This is an informational message only. No user action is required.
    2013-05-16 15:15:10.32 spid5s      Launched startup procedure 'sp_ssis_startup'.
    2013-05-16 15:15:11.08 spid16s     AppDomain 2 (SSISDB.dbo[runtime].1) created.
    2013-05-16 15:15:11.25 spid16s     Unsafe assembly 'microsoft.sqlserver.integrationservices.server, version=11.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil' loaded into appdomain 2 (SSISDB.dbo[runtime].1).
    2013-05-16 15:15:11.29 spid16s     Unsafe assembly 'microsoft.sqlserver.integrationservices.server, version=11.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil' loaded into appdomain 2 (SSISDB.dbo[runtime].1).
    2013-05-16 15:15:48.03 spid52      Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
    2013-05-16 15:15:48.03 spid52      Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
    2013-05-16 15:15:48.04 spid52      Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
    2013-05-16 15:15:48.51 spid52      Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
    2013-05-16 15:15:48.52 spid52      Using 'xpsqlbot.dll' version '2011.110.2100' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
    2013-05-16 15:15:48.66 spid52      Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
    2013-05-16 15:15:48.68 spid52      Using 'xpstar.dll' version '2011.110.3000' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
    2013-05-16 15:15:48.80 spid52      Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
    2013-05-16 15:15:48.81 spid52      Using 'xplog70.dll' version '2011.110.2100' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
    2013-05-16 15:17:21.23 Logon       Error: 18456, Severity: 14, State: 38.
    2013-05-16 15:17:21.23 Logon       Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database 'model'. [CLIENT: <IP Address>]
    2013-05-16 15:17:21.23 Logon       Error: 18456, Severity: 14, State: 38.
    2013-05-16 15:17:21.23 Logon       Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database 'model'. [CLIENT: <IP Address>]
    2013-05-16 15:17:21.54 Logon       Error: 18456, Severity: 14, State: 38.
    2013-05-16 15:17:21.54 Logon       Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database 'red'. [CLIENT: <IP Address>]
    2013-05-16 15:17:21.54 Logon       Error: 18456, Severity: 14, State: 38.
    2013-05-16 15:17:21.54 Logon       Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database 'red'. [CLIENT: <IP Address>]
    2013-05-16 15:17:21.62 Logon       Error: 18456, Severity: 14, State: 38.
    2013-05-16 15:17:21.62 Logon       Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database 'SSISDB'. [CLIENT: <IP Address>]
    2013-05-16 15:17:21.62 Logon       Error: 18456, Severity: 14, State: 38.
    2013-05-16 15:17:21.62 Logon       Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database 'SSISDB'. [CLIENT: <IP Address>]
    2013-05-16 23:47:21.20 Logon       Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database 'SSISDB'. [CLIENT: <IP Address>]
    2013-05-17 00:00:13.38 spid24s     This instance of SQL Server has been using a process ID of 3380 since 5/16/2013 3:15:10 PM (local) 5/16/2013 8:15:10 PM (UTC). This is an informational message only; no user action is required.
    2013-05-17 00:01:34.28 spid51      Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
    2013-05-17 00:01:34.37 spid51      Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
    2013-05-17 00:01:34.38 spid51      Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
    2013-05-17 00:01:35.94 spid18s     Service Broker manager has shut down.
    2013-05-17 00:01:36.09 spid5s      .NET Framework runtime has been stopped.
    2013-05-17 00:01:37.09 spid5s      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-05-17 00:01:37.09 spid5s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

    I have also reviewed the Event Viewer and I see three entries one for disabling MSSQLSERVER, stopping SQL Server Agent, and one for stopping the SQL Server service. I do not see that any specific user information associated with the event.

    System
      Provider
       [ Name]  Service Control Manager
       [ Guid]  {555908d1-a6d7-4695-8e1e-26931d2012f4}
       [ EventSourceName]  Service Control Manager
    EventID 7040
       [ Qualifiers]  16384
       Version 0
       Level 4
       Task 0
       Opcode 0
       Keywords 0x8080000000000000
    TimeCreated
       [ SystemTime]  2013-05-17T05:01:32.109975600Z
       EventRecordID 3733
       Correlation
    Execution
       [ ProcessID]  620
       [ ThreadID]  2600
       Channel System
       Computer <FQDN>
    Security
      [ UserID]  S-1-5-18
    EventData
      param1 SQL Server (MSSQLSERVER)
      param2 auto start
      param3 disabled
      param4 MSSQLSERVER

    Our servers do have System Center Operations Manager 2012 installed if that makes a difference. I am not sure what else I can check to see if I can find the cause of the problem. Any assistance would be appreciated.



    • Edited by CameronMc85 Friday, May 17, 2013 2:56 PM Added Comments
    Friday, May 17, 2013 2:52 PM

Answers

  • You can check the security log of the server and see who/what logs in and out around that time. You also have to check System Center Operations Manager 2012 and see if it is doing or trying to do around that time. Also check any local or remote jobs/services that is running some scripts and this is a collateral damage. Please check who all has system admin access and try to cut them to bare minimum. Disabling one sysadmin account at a time and see if that stops it from happening can help identifying who/what is doing this. If your server is on a virtual emvironment check the virtual host logs as well, may be that can bear some pointers.
    Friday, May 17, 2013 10:03 PM

All replies

  • Hi,

    Definitely looks like a manual shutdown after someone changed SQL Advanced options.

    2013-05-17 00:01:34.28 spid51      Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
     2013-05-17 00:01:34.37 spid51      Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
     2013-05-17 00:01:34.38 spid51      Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
     2013-05-17 00:01:35.94 spid18s     Service Broker manager has shut down.
     2013-05-17 00:01:36.09 spid5s      .NET Framework runtime has been stopped.
     2013-05-17 00:01:37.09 spid5s      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.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, May 17, 2013 3:02 PM
  • 2013-05-17 00:01:37.09 spid5s      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-05-17 00:01:37.09 spid5s      SQL Trace was stopped due to server shutdown.

    Seems someone used the Windows Service Manager to stop the service manually.


    Olaf Helper

    Blog Xing

    Friday, May 17, 2013 3:02 PM
  • Thank you for the quick response, when I look at the event viewer, he SCM event does not list a specific user and the security audit only shows SYSTEM logging in.

    Is there another way to determine who would have stopped the service?

    Friday, May 17, 2013 3:13 PM
  • I stopped my local Express edition; getting the same message in ErrorLog.

    But up to now I don't see any hint in Windows EventLog, who stopped the service and to be true, that's really a  ... pity.


    Olaf Helper

    Blog Xing

    Friday, May 17, 2013 3:27 PM
  • Thank you for the help, I will have to continue to dig and find out what account is stopping the service. I do see several failed login from the SYSTEM account and it normally logs in about 15 minutes before it stops so I am going to take that route.

    Friday, May 17, 2013 6:02 PM
  • You would need to turn on logging for Successful logons as well as failures to capture who did it.  There is no other way.

    However, why would someone be stopping just SQL Server?  My guess is it is someone who thinks they are freeing resources, like RAM.

    Friday, May 17, 2013 7:54 PM
  • You can check the security log of the server and see who/what logs in and out around that time. You also have to check System Center Operations Manager 2012 and see if it is doing or trying to do around that time. Also check any local or remote jobs/services that is running some scripts and this is a collateral damage. Please check who all has system admin access and try to cut them to bare minimum. Disabling one sysadmin account at a time and see if that stops it from happening can help identifying who/what is doing this. If your server is on a virtual emvironment check the virtual host logs as well, may be that can bear some pointers.
    Friday, May 17, 2013 10:03 PM
  • Have you ever had any luck figuring this out?  I am having the EXACT same issue on a Server 2008 R2 box with my SQLSERVERAGENT just randomly getting set to manual startup by the system user.  I have had zero success in figuring out WHY.  Further, it is only happening on my DEV server, and I have an EXACT copy of this server in QA and PROD that are NOT having this issue.

    Wednesday, July 24, 2013 7:48 PM