SQL Server Performance
  1. Jackson New Member

    Hi,
    When I use EXEC SP_LOCKS. How can I know that in which database or the table the lock is on. Please, let me know.
    Thanks,
    Jackson
  2. preethi Member

    You mean sp_lock? You can see a column dbid there. It refers to the ID of the database. You can use DB_Name() function to get the name of the database. The column ObjID refers to the table the lock is on, provided that the type column says "TAB". If you take the objID and use Object_name() function (run this against the database specified with the dbid) you can get the object name
    Hope this helps!
  3. Jackson New Member

    Hi Preethi,
    Thanks for the reply but I couldn't understand what you are trying to say. I tried using
    SELECT object_ID(0)
    It didn't work. Can you please, let me know that if there is any other way or I can still use this one to make it work. I will really appreciate it.
    Thanks,
    Jackson
  4. Adriaan New Member

    I'm pretty sure SQL 2005 introduced some system views/functions that will provide more easily readable results than sp_lock - but I'm not a SQL 2005 person.
    Here's the old-school way of doing it manually.
    Run sp_lock. The results are shown in a table (at least they are in SQL 2000). The second column is dbid, the third column is ObjId.
    Find the row(s) relevant to the lock.
    Run this query ...
    SELECT DB_NAME(x)
    ... where you replace the x with the dbid value from the row.
    Now run this query ...
    USE y
    SELECT OBJECT_NAME(z)
    ... where you replace the y with the database name that you found with the previous query, and the z with the ObjId from the same row.
    Repeat for every relevant row from the results.
  5. Elisabeth Redei New Member

    Hi Jackson,
    Look up sys.dm_tran_locks in BoL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/f0d3b95a-8a00-471b-9da4-14cb8f5b045f.htm). The secion also contains some script you might find useful.
    HTH!
    /Elisabeth

Share This Page