Post reply

Profiler hangs and Clustered SQL Server restarts !

  • December 14, 2017 at 4:02 pm

    #402752

    Hello All. We just had this happen on one of our production SQL Servers. One of our DBA's was running profiler with the default standard template. After this, SQL I see that SQL Server was restarted by the Cluster manager, and I see these in the event logs:

    • Windows error reporting saying profiler has crashed(Complete messsage is long).
    • [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
    • [sqsrvres] printODBCError: sqlstate = HYT00; native error = 0; message = [Microsoft][SQL Server Native Client 10.0]Query timeout expired
    • The program profiler.exe version 2009.100.1600.1 stopped interacting with Windows and was closed. To see if more information about the problem is available, check the problem history in the Action Center control panel. Process ID: 27dc Start Time: 01d37526b53afa8f Termination Time: 3 Application Path: c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\profiler.exe Report Id: 93952cbb-e11a-11e7-bbbb-5cf3fcfc963a
    • [sqsrvres] SvcTerminateProcess: Terminated the sqlserver process (processID = 100c) explicitly.

    Any idea how / what caused Cluster Manager to restart SQL Server ?

  • December 15, 2017 at 12:40 am

    #1972244

    sqlGDBA - Thursday, December 14, 2017 4:02 PM

    Hello All. We just had this happen on one of our production SQL Servers. One of our DBA's was running profiler with the default standard template. After this, SQL I see that SQL Server was restarted by the Cluster manager

    Not overly surprising.

    Any idea how / what caused Cluster Manager to restart SQL Server ?

    Running profiler, or more specifically what profiler does to busy servers.
    Never, never, never use the profiler GUI on a production server. Use server-side trace or, if you're on SQL 2012 or later, Extended Events

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 18, 2017 at 10:17 am

    #1972548

    Microsoft themselves have strongly advised against running SQL Profiler on a production database server. Server Side tracing or Extended Events as put by Gail are best options.

  • December 19, 2017 at 2:25 pm

    #1972809

    Thanks Gail / Arsh. Yes, I realize that profiler should not be run on PRD servers and most of the team is aware of this. This was a rookie DBA who had a ticket to troubleshoot some Application issues, and she went ahead and ran it from the production server itself. I did open a case with Microsoft for the RCOA, since we did have a brief outage. Yes - this is a legacy SQL 2008 R2 Server,  so we cannot run extended events. 

    We sent them everything we had - Cluster logs, Event Viewer, SQL Logs, profiler crash dump, etc. from both nodes on the cluster. We also ran the sdp package against both the servers in the cluster on Saturday and sent results to Microsoft for analysis. 

    So far, the only possibility, seems to be that profiler hung, possibly consuming all resources that were not allocated to SQL Server, and sqlsrvres.dll timed out while querying SQL server to run the health check and thus this could have led to IsAlive health check failure and thus SQL was terminated by cluster and restarted.

  • December 20, 2017 at 12:50 am

    #1972844

    You're far from the first to have crashed SQL by running Profiler (and running it from a different server to production is actually far worse)

    DO NOT use the Profiler GUI against a production server, no matter where you run the client. Use server-side traces.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply