Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server freezes (because of application) need logging Expand / Collapse
Author
Message
Posted Monday, July 1, 2013 2:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 8, 2013 7:16 AM
Points: 5, Visits: 67
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
Post #1469221
Posted Tuesday, July 2, 2013 1:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:20 PM
Points: 2,302, Visits: 2,773
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/


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1469331
Posted Tuesday, July 2, 2013 7:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 8, 2013 7:16 AM
Points: 5, Visits: 67
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
Post #1469475
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse