Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server freezes (because of application) need logging


SQL Server freezes (because of application) need logging

Author
Message
bam.willis
bam.willis
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 89
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
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3048 Visits: 3625
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’! **
bam.willis
bam.willis
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 89
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search