Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2003
    Posts
    8

    Question Unanswered: MasterDB locking

    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
    Ortez

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Are you talking about the "Master" database or is it a database created by you ??

    And do you mean some particular table has been locked ??
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Dec 2003
    Posts
    8
    I mean the MS MasterDB, the system one where the stored procs, etc are kept, not our own generated DB.

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Were you able to get to the tables which were locked ??
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Were you able to get to the tables which were locked ??
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Dec 2003
    Posts
    8
    Hi Enigma

    well the whole system was locked at that point so could not get any info

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Dec 2003
    Posts
    8
    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 ...

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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.

  10. #10
    Join Date
    Dec 2003
    Posts
    8
    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

  11. #11
    Join Date
    Dec 2003
    Posts
    8
    thanks I shall note these down as well

  12. #12
    Join Date
    Sep 2003
    Posts
    522
    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).

  13. #13
    Join Date
    Dec 2003
    Posts
    8
    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

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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.

  15. #15
    Join Date
    Dec 2003
    Posts
    8
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •