July 1, 2013 at 2:53 pm
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
July 2, 2013 at 1:59 am
When it happens again, you could try to connect to the SQL instance using a "Dedicated Admin Conection (DAC)". If you even can't connect to the Windows server when the problem occurs, you'll need to enable the "remote admin connection" setting with sp_configure. This setting will allow to you to open a DAC connection from another machine.
You can only open one single DAC connection at a time, so when you're using SSMS for this you don't use the Object Explorer for this. Open the DAC with a new Query window.
Kendra Little has made a blog about the DAC connection: http://www.brentozar.com/archive/2011/08/dedicated-admin-connection-why-want-when-need-how-tell-whos-using/
July 2, 2013 at 7:12 am
I've set up a sql profiler trace and am saving the rollover logs to our NAS. I thought they would be larger, that's why I was shying away from them. Its only about 300MB a day (so far).
I looked into the DAC and have turn on the remote account. I will definitely jump on that the next time it happens. Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply