How to troubleshoot intermittent lockups

Hi,

I'm an accidental DBA and I was wondering how to troubleshoot what causes Sql Server to lock up some times. When I say “lock up”, I don’t mean completely. I’m talking about a situation everything’s working fine and all of a sudden we start getting complaints that users can’t get into or use SuchAndSuch application which uses Sql Server. So it's not to the point where I have to use the dedicated administratrive connection...but it is simply not responding to requests!

I check the replication monitor and all of our (push subscription) replication processes are backed up and all of them say “Synchronizing” and they’re not making any progress.

I check the activity monitor and if I trace the “blocked by” column I can usually see that there are several processes blocking other things. But it’s hard to determine the root cause, because once one thing blocks another thing, that causes a chain reaction (especially once the replication processes start kicking in).

For instance, this happened earlier today and there were several processes that said “blocked by” process 13. Yet there was no process 13 in the activity monitor (yes I had it ordered by ProcessID and I checked it twice).

I know how to start a profiler trace, but I’m never sure how to interpret the plethora of results to make any type of intelligent determination from them.

Usually what happens is I sit there browsing through the task manager, replication monitor and activity monitor until it *magically* starts responding on its own just as suddenly as it stopped! Of course, the users ask “how did you fix it”? –To which I have no good answer.

If your database is not responding what is the easiest way to determine the root cause? Trial and error takes too long with an intermittent problem and this solution never satisfies upper management!

Thanks,
Nate

Hi Nate,

You should invest a few hours of your time in this webcasts (the return of investment is great IMHO):

"TechNet Support WebCast:Performance troubleshooting and analysis in Microsoft SQL Server 2000" http://support.microsoft.com/kb/838622/en-us

"TechNet Webcast: Troubleshooting Performance Problems in Microsoft SQL Server 2005" http://msevents.microsoft.com/cui/eventdetail.aspx?eventID=1032275646&Culture=en-US

"TechNet Webcast: SQL Server 2005 Troubleshooting: Supportability Features for SQL 2005"
http://msevents.microsoft.com/cui/eventdetail.aspx?eventID=1032275593&Culture=en-US

"TechNet Webcast: Performance Diagnosis in SQL Server 2005"
http://msevents.microsoft.com/cui/eventdetail.aspx?eventID=1032275586&Culture=en-US

For SQL Server 2005 you should take a look at this whitepaper: "Troubleshooting Performance Problems in SQL Server 2005" http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

|||Thanks Cristian. I will definitely check these out!sql

how to tell if accessed

I just got assigned to be our companies sql server dba, there are about 80 databases on various servers. I am suspecting many are not even used. Is there a way for me to tell the last time they were accessed and by whom? And, more importantly, the last time any changes have been made in the database? Thanks in advance.Let me guess. The old DBA quit suddenly without leaving any documentation?|||Actually, there has never really been a sql server dba here. I'm new, and like all the other dbas,we are life long oracle dbas. But, someone has to get these under some control. Until now, all microsoft has been left in the hands of the developers to admin. Fox guarding the hen-house so to speak. Nobody wanted to touch it until I came along. (oh, and the dozens of developers involved have come and gone over the years, not much help)|||Inheriting 80 databases on multiple servers in lord knows what state of maintenance, you folks might consider hiring a SQL Server DBA...
Having gone from SQL Server to Oracle I can tell you that there will be some learning curve, and what you are describing sounds like a full-time position.

As far as telling whether a database is being used, that is difficult after the fact. You can set up tracing or auditing on it to track future access.|||That's a lot of licensing..

Do you have an inventory of all the boxes?

I would start there. Are they physically located near you? Or is it remote?

AND I would change the sa password and see what comes out of the wood work.

AND I would revoke any sa level priviliges.

Do you have a traditional server landscape?

Development, Production, Contingency?|||What's that awful grinding sound I hear? A room full of developers gnashing their teeth?

Heh heh heh...|||I'd check when they were last backed up, then build a custom trace to track logins and db's that they access. Very often DB info is not available in the trace if you use one of the predefined templates, so carefully select the events to ensure that db info is captured.|||Thanks for all the info. Everything is fine, backups have been taken care of and are scheduled now, and licensing was already addressed. SA passwords were changed and I'm working on the multiple file shares where data files reside :mad: with full access for Everyone who maps to it. Internal database privileges are next.

I've tried setting up a trace, but can only figure out how to track the login which, as stated, does not really show which database they are connecting to. These are all version 2000(8.0) and if anyone can help me with an event that will show me what database is being connected to, I'd appreciate it.