We have an application running ontop of our SQL Server 2005 instance and a couple of times a week this application (unanncounced) 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 an SPID. 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 8gigs. Server has 12gigs but the max is set to 8192 for SQL Server.