I had blogged about debugging deadlocked schedulers earlier for SQL Server 2008 R2 and below releases. Since there have been some fundamental changes in SQL Server 2012, I thought it would be a good idea to post about debugging the same scenario on SQL Server 2012 instances.
In the recent past, I had to work on a SQL Server 2000 instance which became unresponsive after a short period of time the service was restarted. Since this was SQL Server 2000, I didn’t have the opportunity to use a Dedicated Administrator Connection (DAC) to log into the SQL Server instance to see if a DAC connection succeeded. And if it did, could I figure out what was happening on the SQL Server engine that it was not accepting a new connection.
Post the SQL Server service restart, the ERRORLOG very happily indicated no issues and if you weren’t already ready to tear your hair out due to the lack of error messages, the connection failure reported the most generic of errors messages:
Server: Msg 11, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation.
I did the basic due diligence to check if the network protocols were enabled and if the port on which the SQL Server instance was supposed to listen on was actually open. I did happen to check the netstat output to check the activity on the port and found a large number of connections on the SQL Server port. I did a quick check of the count of the number of connections showing up to determine if this was a TCP port exhaustion issue. But that was not the case either! The Errorlog didn’t even report a Deadlocked Schedulers condition for me to know that there was an issue.
I just concluded my presentation on “Debugging the Deadlock for the Scheduler” at SQL Saturday 76 over a Lync meeting. The WiFi and Lync gods decided to be benevolent today and let me present without an untoward incident! As always it was fun to demonstrate the use of a debugger…The attempt of the presentation was to demonstrate what can be done proactively and reactively when dealing with a deadlocked schedulers condition.
A special thank you is due to all the sponsors without whom the event today wouldn’t have been possible: Microsoft New Zealand, RedGate, SQL Pass, New Zealand MS Communities and SQL Services!
The slide deck used for presentation today is available on Slide Share and is embedded below:
The demo files used are available at SkyDrive in the file SQLSat76_Demo.zip The walkthrough for the debugging that I explained along with the use of the Powershell script to automatically collect DMV outputs based on messages in the Windows Application event log are available using the posts available under the walkthroughs link below as well as the zip file mentioned above.