SQL Server 2005 stops responding

Question

Post reply

SQL Server 2005 stops responding

Colin McMillan

SSC Journeyman

Points: 77
More actions
August 7, 2007 at 7:42 am

#118047

Hi
We run two SQL Server 2005 instances (on two separate servers) as development machines in our Web Design office. Both get very light use as there are only 6 developers in the office at a time, plus some client access from outside the building.
One of our servers has been intermittently locking up since the end of last year. Basically web pages we are working on timeout, and I am unable to even open Activity Monitor in Management Studio to check out processes and locks. The only was out of this is to restart SQL Server, although sometimes it will lock up very quickly after this anyway.
Our other, newer, server has recently started doing this too, although less frequently (which maybe because it isn't used as heavily).
Can't see anything in Event Manager that gives me any clues, and we've been especially careful about closing connections and releasing recordset resources in our web pages (still using traditional ASP).
Is there anything I can do to investigate this problem?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Colin,

did you ever try to access the "Hanging" machine through a DAC connection ? Look for blocked processes.

Markus

[font="Verdana"]Markus Bohse[/font]

Colin McMillan SSC Journeyman Points: 77 More actions · Answer 2

Thanks for the tip Mark! I didn't know about SQLCMD, but have checked it out and managed to log into the server using it.

Not sure how to check for locked processes though. Any tips there are appreciated.

Next time it locks up I'll try and do some digging.

Cheers

Colin

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

Hi Colin,

I would start out with sp_who2 and sp_lock. Also the dynamic management views should deliver some information. See here for more information:

http://msdn2.microsoft.com/en-us/library/ms176083.aspx

http://www.sqlteam.com/article/dynamic-management-views

By the way you can also open a DAC in a query window in SSMS. Only Object explorer won't be available.

Markus

[font="Verdana"]Markus Bohse[/font]

Colin McMillan SSC Journeyman Points: 77 More actions · Answer 4

Thanks for all that Mark. I'll get into this next time the DB hangs (probably tomorrow!) and get back to you on what happens.

C

Colin McMillan SSC Journeyman Points: 77 More actions · Answer 5

OK, I've managed to run the above two SPs to generate some info, although I'm not sure how to interpret it.

While the DB was taking ages to respond, and I had an ASP page timing out I got the following. It's a bit too much to post so I've made a webpage to preserve formatting:

http://tmp.dogstaging.com/SQL_Output.html

To me it doesn't look like anything bad is happening but I might be wrong. I checked out the server while this was happening and it didn't report any load (SQL server using only 9% CPU and nothing much else going on) so I don't think it's resources.

Any pointers greatly appreciated!

Colin

Minaz Amin SSChampion Points: 11052 More actions · Answer 6

What is the SQL Server version?

What is logged in the error log/ system and application event log ?

It will be helpfull if you try to post it here.

Minaz

"More Green More Oxygen !! Plant a tree today"

Colin McMillan SSC Journeyman Points: 77 More actions · Answer 7

SQL Server is version 2005 (9.0.1399)

Getting a lot of DCOM errors in the System Event log:

===============

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID

{61738644-F196-11D0-9953-00C04FD919C1}

to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.

===============

I've checked in DCOM manager but this CLSID no longer exists so not sure what it was.

Nothing in SQL error log since 9.27 this morning, and only a bunhc of Office Server Search info messages in the application log:

===============

Component: 2612b515-0e76-4078-b340-3903d823039b

Catalog: AnchorProject. A master merge was started due to an external request.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

===============

I'm not convinced these are relevant to my problem...

Colin McMillan SSC Journeyman Points: 77 More actions · Answer 8

For what it's worth, Sharepoint has been installed on this server, but there's only two people using it, and I don't think they're doing much with it.

Colin

steve smith-401573 SSChampion Points: 10889 More actions · Answer 9

Is there anyway of researching the following (quoted from you) any further?

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID

{61738644-F196-11D0-9953-00C04FD919C1}

to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.

I suspect that the CLSID may be a key to the problem of the hanging (just a gut level hunch) and may imply an embedded ID somewhere that's getting in the way. you are using mixed mode login or only Windows?

Colin McMillan SSC Journeyman Points: 77 More actions · Answer 10

I tracked this error down to a Sharepoint issue. With help from this how-to I gave permissions to the Network Service component and the error seems to have gone.

http://geekswithblogs.net/mhamilton/archive/2006/12/19/101568.aspx

Not sure whether it's had any impact on the MSSQL problem yet but I'll keep an eye on things and report back.