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.