Script to display SQL Server locks

 

IF OBJECT_ID('up_bn_locks') IS NOT NULL
BEGIN
    DROP PROCEDURE up_bn_locks
    IF OBJECT_ID('up_bn_locks') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE up_bn_locks >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE up_bn_locks >>>'
END
go
CREATE PROCEDURE up_bn_locks
(@blocks char(1) = 'N')
    AS
    set nocount on
    set ARITHABORT off
    set ARITHIGNORE on
    set ANSI_WARNINGS off

    DECLARE @DatabaseName varchar(50),
            @obj_id      int,
            @table_name  sysname,
            @sql varchar(100)

    create table #sql_locks
        (spid int NULL,
        loginname varchar(128) NULL,
        ntuser varchar(128) NULL,
        dbname sysname NULL,
        obj_id int NULL,
        index_id int NULL,
        table_name sysname NULL,
        lock_type varchar(15) NULL,
        lock_mode varchar(40) NULL,
        lock_status varchar(15) NULL,
        lock_ownertype varchar(15) NULL,
        user_program varchar(128) NULL,
        blocking_spid int NULL,
        wait_time int NULL,
        spid_status nchar(30) NULL,
        spid_cmd nchar(16) NULL,
        nt_domain varchar(128) NULL)

    insert into #sql_locks
        (spid,
        loginname,
        ntuser,
        dbname,
        obj_id,
        index_id,
        lock_type,
        lock_mode,
        lock_status,
        lock_ownertype,
        user_program,
        blocking_spid,
        wait_time,
        spid_status,
        spid_cmd,
        nt_domain)
    select
           b.spid,
           b.loginame,
           b.nt_username,
           db_name(a.rsc_dbid),
           rsc_objid,
           rsc_indid,
           lock_type = case rsc_type
                        when 1 then NULL
                        when 2 then 'DATABASE'
                        when 3 then 'FILE'
                        when 4 then 'INDEX'
                        when 5 then 'TABLE'
                        when 6 then 'PAGE'
                        when 7 then 'KEY'
                        when 8 then 'EXTENT'
                        when 9 then 'RID'
                        when 10 then 'APPLICATION'
                        else NULL
                       end,
           lock_mode = case req_mode
                        when 1 then 'SCHEMA STABILITY'
                        when 2 then 'SCHEMA MODIFICATION'
                        when 3 then 'SHARED'
                        when 4 then 'UPDATE'
                        when 5 then 'EXCLUSIVE'
                        when 6 then 'INTENT SHARED'
                        when 7 then 'INTENT UPDATE'
                        when 8 then 'INTENT EXCLUSIVE'
                        when 9 then 'SHARED INTENT UPDATE'
                        when 10 then 'SHARED INTENT EXCLUSIVE'
                        when 11 then 'UPDATE INTENT EXCLUSIVE'
                        when 12 then 'BULK OPERATION'
                        when 13 then 'SHARED KEY RANGE/SHARED RESOURCE'
                        when 14 then 'SHARED KEY RANGE/UPDATE RESOURCE'
                        when 15 then 'INSERT KEY RANGE/NULL RESOURCE'
                        when 16 then 'KEY RANGE CONVERSION'
                        when 17 then 'KEY RANGE CONVERSION'
                        when 18 then 'KEY RANGE CONVERSION'
                        when 19 then 'KEY RANGE CONVERSION'
                        when 20 then 'KEY RANGE CONVERSION'
                        when 21 then 'EXCLUSIVE KEY RANGE/EXCLUSIVE RESOURCE'
                        else NULL
                       end,
           lock_status = case req_status
                          when 1 then 'GRANTED'
                          when 2 then 'CONVERTED'
                          when 3 then 'WAITING'
                         end,
           lock_ownertype = case req_ownertype
                             when 1 then 'TRANSACTION'
                             when 2 then 'CURSOR'
                             when 3 then 'SESSION'
                             when 4 then 'EXSESSION'
                            end,
           b.program_name,
           b.blocked,
           b.waittime,
           b.status,
           b.cmd,
           b.nt_domain
    from  
        master..syslockinfo a,
        master..sysprocesses b
    where
        a.req_spid = b.spid and
        b.spid <> @@spid

    create table #objects
    (objid int NULL,
     tabname sysname NULL,
     db sysname NULL)

    declare lock_cursor cursor for
            select
                distinct dbname,obj_id
            from
                #sql_locks

See code depot for full script
       
    while @@fetch_status = 0
    begin
       insert into #objects
       exec ('USE [' + @DatabaseName + ']
       select
        a.id,
        a.name,
        db_name()
       from
        sysobjects a where a.id = ' + @obj_id) 
       
       fetch lock_cursor
       into
        @DatabaseName,
        @obj_id
    end
    deallocate lock_cursor

    if @blocks = 'N'      
      select   
                spid,
                loginname,
                ntuser,
                dbname,
                b.tabname, 
                index_id,
                lock_type,
                lock_mode,
                lock_status,
                lock_ownertype,
                user_program,
                blocking_spid,
                wait_time,
                spid_status,
                spid_cmd,
                nt_domain
      from
                #sql_locks a,
                #objects b
      where
                a.obj_id *= b.objid and
                a.dbname *= b.db and
                b.tabname not like '#sql_locks%'
    else
      select   
                spid,
                loginname,
                ntuser,
                dbname,
                obj_id,
                index_id,
                b.tabname,
                lock_type,
                lock_mode,
                lock_status,
                lock_ownertype,
                user_program,
                blocking_spid,
                wait_time,
                spid_status,
                spid_cmd,
                nt_domain
      from
                #sql_locks a,
                #objects b
      where
                a.obj_id *= b.objid and
                a.dbname *= b.db and
 

See code depot for full script

go
IF OBJECT_ID('up_bn_locks') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE up_bn_locks >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE up_bn_locks >>>'
go
GRANT EXECUTE ON up_bn_locks TO public
go