Post reply

Find locked tables

  • December 27, 2007 at 11:27 am

    #180091

    Hi,

    I was wondering, if there is a way to find out the tables that are currently locked by any other query/transaction.

    My motivation behind this doubt is because sometimes the same query executes in less time and sometimes takes inordinately long time. I wonder if some other process is blocking my select statement.

    Thanks.

    Suhas.

  • December 27, 2007 at 11:33 am

    #763048

    run the stored procedure sp_lock, and pay particular attention to two columns "Type" and "Mode"

    exec sp_lock

    a value of X for Mode means it is exclusively locked, and it might be a type of row, Page or Table lock. a table lock for an extended period is most likely what you are trying to track down, as that prevents others from accessing the data while the lock is in place.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 27, 2007 at 11:43 am

    #763053

    here's an example SQL: note that the function object_name takes a second paramter, dbid, only in SQL 2005.

    Create Table #tmplocks (

    spid int,

    dbid int,

    objid int,

    indid int,

    type varchar(30),

    Resource varchar(30),

    Mode varchar(30),

    Status varchar(30) )

    insert into #tmplocks

    exec sp_lock

    select user_name(spid) As username,db_name(dbid) as dbname,object_name(objid,dbid) As TableName,Type,Mode,Status

    from #tmplocks where Mode <> 'S'

    drop table #tmplocks

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 27, 2007 at 11:50 am

    #763058

    Hey Lowell,

    Thanks for the reply. That is very nicely explained. I have to try it out now.

    Thanks again

  • December 27, 2007 at 12:28 pm

    #763069

    Lowell (12/27/2007)

    here's an example SQL: note that the function object_name takes a second paramter, dbid, only in SQL 2005.

    2005 SP2 and higher.

    If you are using SQL 2005, rather use the DMV sys.dm_tran_locks than the sp_lock proc. sp_lock is solely there for compatibility with SQL 2000 and is not guaranteed to be there in future.

    Plus, since the DMV can be directly queried, there's no need for temp tables.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply