I thought maybe someone would be able to throw some light
on a problem I have seen on our SQL DB.
We are running SQL2000 with the latest service packs and
have experienced occasions where the Masterdb has been
locked for about 5 mins. The total DB is about 30gb with
the main db about 16gb, the rest being temp etc, plus a
couple of other small DB's.
I would have thought this is impossible but it happened.
Was wondering if anyone had ever heard of such a thing
before and knew what we can do.
When a lot of processes were happening on the DB, at some point nothing was working ... whatever I was trying to access from the enterprise management console, was giving me lock timeout, such as viewing current activity which gets data from masterdb afaik ...
I have seen similar symptoms when tempdb was unavailable. The next time it happens, try select * from master..sysprocesses vs sp_who2. sp_who2 depends on temp tables. The direct select does not. In sysprocesses pay attention to the waittype, lastwaittype, and waitresource columns. They may tell you something. Just not sure what, at the moment.
if tempdb is heavily used due to high activity generated by work against temporary objects, - sp_who will timeout. i don't think it was your master database. when this occurs again try dbcc opentran ('your_database_name') and then either dbcc inputbuffer(offending_spid) of ::fn_get_sql(sql_handle_from_sysprocesses).
Now I remember where I saw this behavior before. There was a second application installed (as a service) on the SQL box that had a memory leak. Only it was not a nice slow steady detectable one. This application would hit some condition, then eat the box in the course of a day. Our only solution (since we could not uninstall the offensive application) was to restart that application once per week. You may want to set up perfmon to look at SQL Memory counters. If you see SQL giving up lots of memory right before a hang/crash, you may have the same condition.