Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers 3

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, then ERRORLOG.2 etc, 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 .mdmp files in the same location...

share|improve this answer
1  
+1 DAC + sp_whoisactive might give enough for a follow up question that gets closer to the root cause, with minimal effort. –  Mark Storey-Smith Jul 1 '13 at 23:13
add comment

I would suggest you to look into 2 routes that will be most helpful to you in your situation:

  1. Monitor PERFMON counter - especially % Processor Time along with SQLDiag

    On a SQL Server 2005 server, the SQLDiag utility is present under the following folder as SQLDiag.exe file – [systemroot]\Program Files\Microsoft SQL Server\90\Tools\Binn. On a SQL Server 2008 box, SQLDiag is present under [systemroot]\Program Files\Microsoft SQL Server\100\Tools\Binn. SQLDiag uses an XML configuration file which can be used to specify the data that you want to collect.

  2. When in doubt, run process monitor. Also ProcDump is useful for getting dump of particular process. Very useful tools from sysinternals.

ProcDump has a parameter -c for CPU threshold at which to create a dump of the process.

Excellent References :

share|improve this answer
2  
don't use ProcDump with SQL Server. It has it's own dumper, sqldumper.exe which does some SQL Server specific tricks. Eg. it can generate a 'filtered' dump which does not contain actual data (buffer pool) for a much, much, much smaller dump and a dump which is safe(er) to share with outsiders. –  Remus Rusanu Jul 1 '13 at 21:33
    
@RemusRusanu Thanks for mentioning. Really helpful to know. –  Kin Jul 1 '13 at 21:36
    
With ProcDump (or any other non-SQL Server aware tool for the matter) on a 12GB RAM system you risk dumping an image file of +12GB. Not the end of the world, until you have a 256GB RAM server to deal with... –  Remus Rusanu Jul 1 '13 at 21:38
3  
I don't think I'd put any form of memory dump and analysis tools in the hands of someone asking this sort of question, frankly. –  Mark Storey-Smith Jul 1 '13 at 23:10
add comment

You could use PerfMon to track some SQL Server counters:

http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

I was going to suggest you check the plan cache for the most resource-intensive queries, but that will only show queries since your last reboot. One of them might point you in the right direction, though.

http://www.brentozar.com/responder/get-top-resource-consuming-queries/

You could also read the error logs.

http://msdn.microsoft.com/en-us/library/ms187109%28v=sql.90%29.aspx

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.