We have an application running ontop of our SQL Server 2005 instance and a couple of times a week this application (unannounced) will cause the SQL Server to freeze. I can't even restart the SQL Server service; I have to restart the entire machine.
Needless to say, I can't open a query window to run sp_who2 to find the cause. It could be days before the issue shows up again. Is there any type of logging I can put in place to better track down what is causing the SQL Server to freeze up?
exec xp_readerrorlog only shows me what has happened after the restart so it isn't much help.
At the time it freezes, CPU is pegged at 90-97% and memory is maxed at 8 GB. Server has 12 GB but the max is set to 8192 for SQL Server.
Best Answer
SQL Server has a Dedicated Administrative Connection, see How to: Use the Dedicated Administrator Connection with SQL Server Management Studio. The DAC uses distinct, pre-reserved resources (memory, CPU, IO ports etc) exactly so it can be used even when the server is 'frozen'. From the DAC you can run queries that can identify the cause of the spike.
When the server is restarted the ERRORLOG is not deleted, it gets recycled. The errorlog from before the shutdown is usually
ERRORLOG.1, thenERRORLOG.2etc, and is located in the same folder as the current one. Just open it and inspect the tail of the file. Your previous restart logs are probably there still, have a look and see what you can find. Also see if there are any.mdmpfiles in the same location...