none
The MSSQLSERVER service terminated unexpectedly

    Pergunta

  • Hi All,
    Today morning my production Server restarted unexpectedly 2 times in 1 hr. 
    Production Server Configuration
    Windows Server 2003 R2 64-Bit enterprise edition SP2
    SQL Server :- RTM 10.50.1617.0 Standard Edition (64-bit)
    16 GB RAM, 2.66 GHz(8 CPU's)
    max server memory (MB) 16 2147483647 14500 14500
    max degree of parallelism 0 1024 0 0

    Before same server was working fine and the only change i did is, we installed mysql connectivity driver to create mysql linked Server. After configuring the odbc DSN for mysql i started configure mysql linked Server but MSDASQL provider isn't there.
    After doing some google i came to know that there's a bug by microsoft in Windows Server 2003 64-Bit for MSDASQL. So i installed hotfix for the same from below mentioned link around 11 Am. And linked Server was created successfully created and patch also applied successfully.
    http://www.microsoft.com/en-us/download/details.aspx?id=20065

    And around 12:45 Pm users reported me that a database that they were doing highly delete operation is in recovery mode. This again happenned around 1:47 Pm. 
    Below mentioned is the error log i got at the time of issue . Please tell me why this happenned? what was the reason for sql server services restarted. As this is production issue m much more worried for next failure.
    or any more configurations needed.


    Error Log at 12 Pm:-
    12:46:16,Server,Unknown,Registry startup parameters: <nl/> -d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf<nl/> -e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG<nl/> -l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf,
    12:46:16,,Warning,[139] AutoRestart: Attempting to restart the MSSQLSERVER service (attempt #2)...,
    12:46:16,,Error,[359] The local host server is not running,
    12:46:15,,Error,[359] The local host server is not running,
    12:46:11,,Error,[368] AutoRestart: Unable to restart the MSSQLSERVER service (reason: An instance of the service is already running),
    12:46:11,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01],
    12:46:11,,Error,[359] The local host server is not running,
    12:46:11,,Error,[359] The local host server is not running,
    12:46:11,,Error,[359] The local host server is not running,
    12:46:10,,Error,[298] SQLServer Error: 109<c/> Shared Memory Provider: The pipe has been ended. [SQLSTATE 08S01],
    12:46:10,,Error,[298] SQLServer Error: 109<c/> Communication link failure [SQLSTATE 08S01],
    12:46:10,,Error,[298] SQLServer Error: 16389<c/> Communication link failure [SQLSTATE 08S01],
    12:46:10,,Error,[359] The local host server is not running,
    12:46:10,,Error,[359] The local host server is not running,
    12:46:10,,Error,[359] The local host server is not running,
    12:46:10,,Error,[012] The MSSQLSERVER service terminated unexpectedly,
    12:46:10,,Warning,[139] AutoRestart: Attempting to restart the MSSQLSERVER service (attempt #1)...,
    12:46:07,spid107,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    12:46:07,spid107,Unknown,The client was unable to reuse a session with SPID 107<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    12:46:07,spid64,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    12:46:07,spid64,Unknown,The client was unable to reuse a session with SPID 64<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    12:46:04,spid116,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    12:46:04,spid116,Unknown,The client was unable to reuse a session with SPID 116<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    12:46:03,spid65,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    12:46:03,spid65,Unknown,The client was unable to reuse a session with SPID 65<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    12:46:03,spid62,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    12:46:03,spid62,Unknown,The client was unable to reuse a session with SPID 62<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    12:46:03,spid51,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    12:46:03,spid51,Unknown,The client was unable to reuse a session with SPID 51<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    12:46:03,spid72,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    12:46:03,spid72,Unknown,The client was unable to reuse a session with SPID 72<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    12:46:01,spid80,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    12:46:01,spid80,Unknown,The client was unable to reuse a session with SPID 80<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    12:46:00,spid77,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    12:46:00,spid77,Unknown,The client was unable to reuse a session with SPID 77<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    12:46:00,spid81,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    12:46:00,spid81,Unknown,The client was unable to reuse a session with SPID 81<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,



    Error Log at 1:47 Pm:-
    13:47:16,spid7s,Unknown,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'MSSQLSERVER'.,
    13:47:16,spid7s,Unknown,0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.,
    13:47:16,spid7s,Unknown,Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.,
    13:47:15,spid7s,Unknown,6 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.,
    13:47:15,spid7s,Unknown,Starting up database 'master'.,
    13:47:15,Server,Unknown,Node configuration: node 0: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x00000000000000ff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.,
    13:47:15,Server,Unknown,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.,
    13:47:15,Server,Unknown,SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.,
    13:47:15,Server,Unknown,Detected 8 CPUs. This is an informational message; no user action is required.,
    13:47:15,Server,Unknown,This instance of SQL Server last reported using a process ID of 2400 at 6/9/2012 1:47:02 PM (local) 6/9/2012 8:17:02 AM (UTC). This is an informational message only; no user action is required.,
    13:47:15,Server,Unknown,Registry startup parameters: <nl/> -d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf<nl/> -e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG<nl/> -l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf,
    13:47:15,Server,Unknown,Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) <nl/> Apr 22 2011 19:23:43 <nl/> Copyright (c) Microsoft Corporation<nl/> Standard Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2),
    13:47:15,Server,Unknown,(c) Microsoft Corporation.,
    13:47:15,Server,Unknown,All rights reserved.,
    13:47:15,Server,Unknown,Server process ID is 2540.,
    13:47:15,Server,Unknown,Authentication mode is MIXED.,
    13:47:15,Server,Unknown,Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'.,
    13:47:12,,Error,[012] The MSSQLSERVER service terminated unexpectedly,
    13:47:12,,Warning,[139] AutoRestart: Attempting to restart the MSSQLSERVER service (attempt #1)...,
    13:47:08,,Error,[298] SQLServer Error: 26<c/> Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server<c/> server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server. [SQLSTATE 08001],
    13:47:08,,Error,[298] SQLServer Error: 26<c/> Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server<c/> server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server. [SQLSTATE 08001],
    13:47:08,,Error,[298] SQLServer Error: 109<c/> Shared Memory Provider: The pipe has been ended. [SQLSTATE 08001],
    13:47:08,,Error,[298] SQLServer Error: 109<c/> Shared Memory Provider: The pipe has been ended. [SQLSTATE 08001],
    13:47:08,,Error,[298] SQLServer Error: 26<c/> Client unable to establish connection [SQLSTATE 08001],
    13:47:08,,Error,[298] SQLServer Error: 26<c/> Client unable to establish connection [SQLSTATE 08001],
    13:47:08,,Error,[298] SQLServer Error: 109<c/> Client unable to establish connection due to prelogin failure [SQLSTATE 08001],
    13:47:08,,Error,[382] Logon to server '(local)' failed (ConnUpdateStartExecutionDate),
    13:47:08,,Error,[298] SQLServer Error: 109<c/> Client unable to establish connection due to prelogin failure [SQLSTATE 08001],
    13:47:08,,Error,[382] Logon to server '(local)' failed (SaveAllSchedules),
    13:47:02,spid68,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    13:47:02,spid68,Unknown,The client was unable to reuse a session with SPID 68<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    13:47:02,spid82,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    13:47:02,spid82,Unknown,The client was unable to reuse a session with SPID 82<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    13:47:02,spid73,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    13:47:02,spid73,Unknown,The client was unable to reuse a session with SPID 73<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    13:47:02,spid55,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    13:47:02,spid55,Unknown,The client was unable to reuse a session with SPID 55<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    13:47:02,spid93,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    13:47:02,spid93,Unknown,The client was unable to reuse a session with SPID 93<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    13:47:02,spid70,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    13:47:02,spid70,Unknown,The client was unable to reuse a session with SPID 70<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    13:47:02,spid54,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    13:47:02,spid54,Unknown,The client was unable to reuse a session with SPID 54<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    13:47:02,spid89,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    13:47:02,spid89,Unknown,The client was unable to reuse a session with SPID 89<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    13:47:02,spid56,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    13:47:02,spid56,Unknown,The client was unable to reuse a session with SPID 56<c/> which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.,
    13:47:02,spid78,Unknown,Error: 18056<c/> Severity: 20<c/> State: 29.,
    domingo, 10 de junho de 2012 16:53

Respostas

  • You are running the RTM version of SQL 2008 R2.  I would suggest you update your database engine to the current Service Pack and Cumulative Update and see if the problem reoccurs.

    Please see: http://support.microsoft.com/kb/2527041

    segunda-feira, 11 de junho de 2012 15:02
  • ProcDump tool can be used to determine who/what is shutting down a process unexpectedly since SQL itself is not getting a minidump from the unexpected shutdown. We are guessing it is MySql's driver.

    • Download http://technet.microsoft.com/en-us/sysinternals/dd996900.aspx
    • Unzip to a folder
    • Start Cmd as administrator
    • cd into the unzipped folder
    • procdump.exe -t -e sqlservr.exe c:\temp\sqlshutdown.mdmp
    • Note that the -t is to measure unexpected process shutdown in case mySQL is calling ProcessExit() when it has trouble.
    • Note that the size of the output mdmp file can be very large (as big as the amount of RAM consumed by SQLServr.exe process in task manager), so make sure your c:\temp folder is big enough, or use another big folder.

    Once you get the shutdown dump you can open in Windbg to see what dll caused the shutdown.

    • WinDbg download is part of debugging tools for windows http://msdn.microsoft.com/en-us/windows/hardware/gg463009.aspx download here
    • Install Windbg, Open Windbg, then Open the dump in Windbg, File > Open Dump...
    • Then set the symbol path by pasting this command in the command 0:00> box and press enter 
    • .sympath SRV*C:\localsymbols*http://msdl.microsoft.com/download/symbols
    • Then switch to any exception record if there was a crash
    • .ecxr
    • Then in the command window do the kL command to see the stack of the shutdown thread. If you have multiple thread you may do ~*kL  to see them all.

    Thanks, Jason


    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance


    terça-feira, 12 de junho de 2012 08:08

Todas as Respostas

  • Moving this to the Database Engine forum.

    Did you have a minidump file in the SQL errorlog folder *.mdmp here:

    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\

    If you do, you can open a ticket with the Microsoft support team to review the dump file and look for the probable cause.

    My guess is that the MySql ODBC driver is crashing or calling Exit Process itself, shutting down the SQL Server inadvertantly. Since the linked server work talks to MySQL from within the Sqlservr.exe process, if the ODBC drivers crashes, it takes SQL down with it.

    Thanks, Jason


    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

    segunda-feira, 11 de junho de 2012 04:11
  • How do you start the service ? NET START??


    Best Regards, Uri Dimant SQL Server MVP MS SQL Development and Optimization Large scale of database and optimization http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    segunda-feira, 11 de junho de 2012 05:37
  • Thanks Jason and Dimant for ur reply.

    Dear Jason,

    I have checked the log folder and searched *.mdmp, No such file exists. And in error log as posted above at first  failure no information for MySql ODBC driver crashing is printed but in 2nd error MySql ODBC driver crashing information is printed.

    So why Sql Server services resarted at first time.

    And whats the connection pooling error mentioned both time at the time of restrart. Please help me!

    segunda-feira, 11 de junho de 2012 06:50
  • Any entries in Event Viewer?

    Best Regards,Uri Dimant SQL Server MVP http://www.dfarber.com/microsoft-solutions/mssql-server.aspx http://www.dfarber.com/computer-consulting-blog.aspx http://sqlblog.com/blogs/uri_dimant/

    segunda-feira, 11 de junho de 2012 06:52
  • Dear Dimant,

    Most of the time i used to restart the services by services.msc and sometimes NET START if urgently uptime is required.

    But this time on Saturday Services got automatically restarted 2 times in 1 hr. I want to know whether this issue is due to connection pooling or memory consumption or by My SQL driver.

    segunda-feira, 11 de junho de 2012 06:54
  • Entries in Event Viewer are same as in error log of sql server what i have posted above.
    segunda-feira, 11 de junho de 2012 07:55
  • I am pretty sure it is by My SQL driver not a memory..... Is it only MS SQL Service get restarted of the OS as well?

    Best Regards,Uri Dimant SQL Server MVP http://www.dfarber.com/microsoft-solutions/mssql-server.aspx http://www.dfarber.com/computer-consulting-blog.aspx http://sqlblog.com/blogs/uri_dimant/

    segunda-feira, 11 de junho de 2012 08:04
  • I again checked the event viewer logs and for the 1st time only MS SQL Services got restarted but for 2nd time before sql services McAfee services got restarted with the following error.

    A thread in process C:\Program Files (x86)\McAfee\VirusScan Enterprise\x64\mcshield.exe took longer than 90000 ms to complete a request.
     The process will be terminated. Thread id : 3756 (0xeac)
     Thread address : 0x0000000077EF052A
     Thread message :
     Object being scanned = \Device\HarddiskVolume2\Documents and Settings\All Users\Application Data\McAfee\DesktopProtection\OnAccessScanLog.txt
     by C:\Program Files (x86)\McAfee\VirusScan Enterprise\x64\mfeann.exe
     7005(187)(0)
     7004(187)(0)
     5006(94)(0)
     5004(94)(0)
     5003(94)(0)
     5002(47)(1)
     15002(47)(0)
     5000(31)(0)

    The McShield service terminated unexpectedly.
     Please review event 5019 or 5051 for details. The McShield service will be restarted in 5 seconds;

    After this error Sql Server Services got restarted. In all These 2 servies got restarted. So my main concern is should i uninstall My SQL driver or wait for the next restart.

    segunda-feira, 11 de junho de 2012 08:22
  • What this OnAccessScanLog.txt file contains?

    Best Regards,Uri Dimant SQL Server MVP http://www.dfarber.com/microsoft-solutions/mssql-server.aspx http://www.dfarber.com/computer-consulting-blog.aspx http://sqlblog.com/blogs/uri_dimant/

    segunda-feira, 11 de junho de 2012 08:24
  • Nothing much except scan time out error posted below.

    6/9/2012    12:35:17 PM    Not scanned  (scan timed out)     domain\Administrator    C:\WINDOWS\Explorer.EXE    F:\win64_11gR2_client.zip    
    6/9/2012    12:35:24 PM    Not scanned  (scan timed out)     domain\Administrator    C:\WINDOWS\Explorer.EXE    F:\win64_11gR2_client.zip    
    6/9/2012    12:35:28 PM    Not scanned  (scan timed out)     domain\Administrator    C:\WINDOWS\Explorer.EXE    F:\win64_11gR2_client.zip    
    6/9/2012    1:44:15 PM    Not scanned  (scan timed out)     NT AUTHORITY\NETWORK SERVICE    C:\WINDOWS\system32\wbem\wmiprvse.exe    C:\Program Files\HPWBEM\Logs\HPWBEMServer.log   

    segunda-feira, 11 de junho de 2012 09:11
  • Well, try uninstall the driver and let us know how was it going.

    Best Regards,Uri Dimant SQL Server MVP http://www.dfarber.com/microsoft-solutions/mssql-server.aspx http://www.dfarber.com/computer-consulting-blog.aspx http://sqlblog.com/blogs/uri_dimant/

    segunda-feira, 11 de junho de 2012 09:25
  • You are running the RTM version of SQL 2008 R2.  I would suggest you update your database engine to the current Service Pack and Cumulative Update and see if the problem reoccurs.

    Please see: http://support.microsoft.com/kb/2527041

    segunda-feira, 11 de junho de 2012 15:02
  • ProcDump tool can be used to determine who/what is shutting down a process unexpectedly since SQL itself is not getting a minidump from the unexpected shutdown. We are guessing it is MySql's driver.

    • Download http://technet.microsoft.com/en-us/sysinternals/dd996900.aspx
    • Unzip to a folder
    • Start Cmd as administrator
    • cd into the unzipped folder
    • procdump.exe -t -e sqlservr.exe c:\temp\sqlshutdown.mdmp
    • Note that the -t is to measure unexpected process shutdown in case mySQL is calling ProcessExit() when it has trouble.
    • Note that the size of the output mdmp file can be very large (as big as the amount of RAM consumed by SQLServr.exe process in task manager), so make sure your c:\temp folder is big enough, or use another big folder.

    Once you get the shutdown dump you can open in Windbg to see what dll caused the shutdown.

    • WinDbg download is part of debugging tools for windows http://msdn.microsoft.com/en-us/windows/hardware/gg463009.aspx download here
    • Install Windbg, Open Windbg, then Open the dump in Windbg, File > Open Dump...
    • Then set the symbol path by pasting this command in the command 0:00> box and press enter 
    • .sympath SRV*C:\localsymbols*http://msdl.microsoft.com/download/symbols
    • Then switch to any exception record if there was a crash
    • .ecxr
    • Then in the command window do the kL command to see the stack of the shutdown thread. If you have multiple thread you may do ~*kL  to see them all.

    Thanks, Jason


    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance


    terça-feira, 12 de junho de 2012 08:08
  • Thanks to all for ur comment,

    Now from past three days m continuously monitoring that server and its performance without uninstalling My SQL driver, Till now it is working fine and from sat it never got restarted. Next time if it will get restart first thing i'll do is uninstall the My SQL driver as suggested by Uri Dimant.

    By the time i'll find out why it got restarted as mentioned by Jason.

    Jason, As recommended by u procdump.exe -t -e sqlservr.exe c:\temp\sqlshutdown.mdmp it seems that it will capture info from sqlservr.exe , my main concern is that whether it will effect my sql db service in any way if done on production time or done on free time.

    Second is from link posted by u http://msdn.microsoft.com/en-us/windows/hardware/gg463009.aspx there is many others links for different platform and different purpose so in that what should i download suitable for only debugging purpose and as per my hardware requirements.

    quarta-feira, 13 de junho de 2012 07:25