Hi
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.
I am baffled :-)
I have looked on the MS site to no avail yet.
Any assistance or hints would be appreciated.
Thanks
OrtezAre you talking about the "Master" database or is it a database created by you ??
And do you mean some particular table has been locked ??|||I mean the MS MasterDB, the system one where the stored procs, etc are kept, not our own generated DB.|||Were you able to get to the tables which were locked ??|||Were you able to get to the tables which were locked ??|||Hi Enigma
well the whole system was locked at that point so could not get any info|||6.5 corruption ok, 2k? Never heard of it...or very rarely...
And how do you know "Master" database is locked if you can't see anything...
Can you look at the error logs?
And how about profiler did you start a trace?
Can you ping the box?|||Hi Bret
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.|||also this is not happening at the moment, it is something that happened last week.
As I said I am baffled as this is strange
Thanks|||thanks I shall note these down as well|||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).|||well as we have no real idea what caused it we have been trying to analyse things further.
I will ofcourse try your suggestion as the more info we have the better.
due to the fact we could not access anything at that time we assumed it was the MasterDB causing this, but we have no hard evidence
Thats for the advice and we shall monitor and report back if we get further information
Cheers
Ortez|||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.|||Hi
Thanks for that tip, I will speak to the DBA and have him look at that possibilty. Anything that can help we will investigate :P
Cheers
Ortez
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment