3

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.

Improve this question

3 Answers 3

Reset to default
8

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...

Improve this answer
1
  • 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
    Commented Jul 1, 2013 at 23:13
2

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.

  1. 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 :

Improve this answer
4
  • 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
    Commented Jul 1, 2013 at 21:33
  • @RemusRusanu Thanks for mentioning. Really helpful to know.
    – Kin Shah
    Commented Jul 1, 2013 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
    Commented Jul 1, 2013 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
    Commented Jul 1, 2013 at 23:10
1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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