6

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.

Thanks

BamBamBeano
  • 464
  • 4
  • 13
  • 24

1 Answers1

1

I guess you can use SQL Server Profiler traces to see which queries are the cause:

Save Trace Results to a File (SQL Server Profiler)

henginy
  • 2,041
  • 1
  • 16
  • 27
  • My reservation about this is that doesn't running SQL Profiler for extended periods of time make an already slow system even slower? It could be 3 to 4 days before this issue happens again. – BamBamBeano Jul 01 '13 at 21:02
  • Yeah, I had that in mind but thought finding the cause would be more important than a temporary performance degradation. If this is not the case for you, on the other hand, you are right. – henginy Jul 01 '13 at 21:04
  • I've set up a trace and roll over logs, am going to monitor performance to see what type of hit it takes. If Profiler is the only way then it has to be done. Question: now that I have a trace running and saving the logs somewhere, I have to keep profiler open for the next few days? – BamBamBeano Jul 01 '13 at 21:11