/*--------------------------------------------------------------------- $Header: /abasql/aba_lockinfo_sql7.sp 6 06-03-21 21:09 Sommar $ This SP lists locking information for all active processes, that is processes that have a lock or are not AWAITING COMMAND. Information about all locked objects are included, as well the last command sent from the client. Note that this command is tacked out afterwards with DBCC INPUTBUFFER, and may be out of sync with the rest of the data. The original source for the SP was taken from the undocumented system procedure sp_lockinfo. This version is for SQL 7. There are separate versions for SQL6.5 and SQL 2000. $History: aba_lockinfo_sql7.sp $ * * ***************** Version 6 ***************** * User: Sommar Date: 06-03-21 Time: 21:09 * Updated in $/abasql * Bug fix: I did not quote database names with quotename(), so you would * get a syntax error with databases with special characters. * * ***************** Version 5 ***************** * User: Sommar Date: 04-02-22 Time: 23:24 * Updated in $/abasql * Fixed header comments. * * ***************** Version 4 ***************** * User: Sommar Date: 04-02-22 Time: 23:19 * Updated in $/abasql * Thorough rewrite for better performance. * * ***************** Version 3 ***************** * User: Sommar Date: 03-05-25 Time: 11:22 * Updated in $/abasql * stmt_start is an offset, so we should add 1 to it. * * ***************** Version 2 ***************** * User: Sommar Date: 02-12-21 Time: 23:25 * Updated in $/abasql * stmt_end = -1 means that current statement extents until end of text. * * ***************** Version 1 ***************** * User: Sommar Date: 02-12-21 Time: 23:08 * Created in $/abasql * * ***************** Version 10 ***************** * User: Sommar Date: 02-05-04 Time: 18:35 * Updated in $/abasql * Current time was saved in a format that later would cause conversion * error with some dateformat settings. * * ***************** Version 9 ***************** * User: Sommar Date: 02-03-24 Time: 0:39 * Updated in $/abasql * The setting of @minspid had disappeared. * * ***************** Version 8 ***************** * User: Sommar Date: 02-03-22 Time: 16:02 * Updated in $/abasql * Performance enhancements. No longer need for separate database, uses * temp tables. Lots of KEEPFIXED PLAN to avoid recompilations. * Unnecessary use of dynamic SQL removed. Support for SQL7 removed. * * ***************** Version 7 ***************** * User: Sommar Date: 01-11-26 Time: 15:38 * Updated in $/abasql * Now that's news! There might be more than one ecid in sysprocesses per * spid. Let's handle that! * * ***************** Version 6 ***************** * User: Sommar Date: 01-07-16 Time: 22:27 * Updated in $/abasql * Extensive rewrite. Default is now to group locks to reduce the amount * data when there are many locks. Also handling the case that a process * may not exist in sysprocesses. Handle also application locks. * * ***************** Version 5 ***************** * User: Sommar Date: 01-03-17 Time: 22:07 * Updated in $/abasql * Added SET QUOTED_IDENTIFIER OFF for the benefit of people outside * Abaris who might have this on. * * ***************** Version 4 ***************** * User: Sommar Date: 00-11-07 Time: 10:59 * Updated in $/projects/dbverktyg/abasql * Adaptions for SQL2000. Define processes that only hold a lock on a * databaes as passive. Translate object names per database, not per * object. Handle that last_since may overflow. * * ***************** Version 2 ***************** * User: Sommar Date: 00-02-09 Time: 13:19 * Updated in $/projects/dbverktyg/abasql * Stupid bug: last_since was 10 times too big. * * ***************** Version 1 ***************** * User: Sommar Date: 00-01-06 Time: 17:46 * Created in $/projects/dbverktyg/abasql * * ***************** Version 2 ***************** * User: Sommar Date: 99-12-21 Time: 19:39 * Updated in $/projects/dbverktyg/abasql * Hide system processes. * * ***************** Version 1 ***************** * User: Sommar Date: 99-12-21 Time: 19:33 * Created in $/projects/dbverktyg/abasql ---------------------------------------------------------------------*/ CREATE PROCEDURE aba_lockinfo @processes tinyint = 0, @details bit = 0, @fancy bit = 0 AS DECLARE @minspid int, @dbid smallint, @dbname sysname, @qdbname nvarchar(256), @stmt varchar(8000), @spid smallint, @spidstr varchar(10), @str varchar(255), @blklvl tinyint, @now datetime, @spidlen varchar(5), @ecidlen varchar(5), @cntlen varchar(5), @loginlen varchar(5), @statuslen varchar(5), @dbnamelen varchar(5), @hostlen varchar(5), @cmdlen varchar(5), @appllen varchar(5), @waitlen varchar(5), @waitreslen varchar(5), @locktlen varchar(5), @restlen varchar(5), @lkstatlen varchar(5), @lkobjlen varchar(5), @ownertlen varchar(5), @cpulen varchar(5), @physiolen varchar(5), @memlen varchar(5), @delaylen varchar(5), @inputlen varchar(5), @waittypestr varchar(255) CREATE TABLE #result ( ident int IDENTITY, spid smallint NOT NULL, ecid smallint NOT NULL, cnt int NULL, login sysname NOT NULL, prcstatus nvarchar(30) NOT NULL, command nvarchar(16) NOT NULL, dbname sysname NOT NULL, host nvarchar(128) NOT NULL, appl nvarchar(128) NOT NULL, opntrn varchar(5) NOT NULL, lvl char(3) NOT NULL, blkby varchar(5) NOT NULL, locktype nvarchar(70) NOT NULL, ownertype nvarchar(70) NOT NULL, object nvarchar(170) NULL, rsctype nvarchar(70) NOT NULL, lstatus nvarchar(70) NOT NULL, waittime varchar(10) NOT NULL, waittype binary(2) NULL, cpu varchar(10) NOT NULL, physio varchar(10) NOT NULL, memusg varchar(10) NOT NULL, now char(12) NOT NULL, login_time char(16) NOT NULL, last_batch char(16) NOT NULL, last_since varchar(11) NOT NULL, delay varchar(10) NOT NULL, inputbuffer varchar(255) NOT NULL, last bit NOT NULL DEFAULT 0, PRIMARY KEY CLUSTERED (spid, ident)) CREATE TABLE #inputbuffer (eventtype nvarchar(30) NULL, params int NULL, eventinfo nvarchar(255) NULL) CREATE TABLE #procs ( spid smallint NOT NULL, ecid smallint NOT NULL, active bit NOT NULL DEFAULT 1, login sysname NULL, status nvarchar(30) NULL, dbname sysname NULL, host nvarchar(128) NULL, command nvarchar(16) NULL, appl nvarchar(128) NULL, opntrn smallint NULL, blking smallint NOT NULL, blkby smallint NULL, blklvl smallint NOT NULL, waittime int NULL, waittype binary(2) NULL, cpu int NULL, physio int NULL, memusage int NULL, now datetime NOT NULL, login_time char(16) NULL, last_batch char(16) NULL, last_since numeric(10,3) NULL, delay int NOT NULL DEFAULT 0, inputbuffer nvarchar(255) NOT NULL DEFAULT ' ', PRIMARY KEY (spid, ecid)) CREATE TABLE #locks ( ident int IDENTITY, spid smallint NOT NULL, ecid smallint NOT NULL, cnt int NULL, req_mode tinyint NOT NULL, rsc_type tinyint NOT NULL, req_status tinyint NOT NULL, req_ownertype smallint NOT NULL, dbid smallint NOT NULL, objid int NOT NULL, indid tinyint NOT NULL, rsc_text nchar(32) NULL, refcnt smallint NULL, activelock tinyint NOT NULL, UNIQUE NONCLUSTERED (spid, ecid, activelock, ident), UNIQUE NONCLUSTERED (dbid, objid, indid, ident)) CREATE TABLE #objects (dbid smallint NOT NULL, objid int NOT NULL, indid tinyint NOT NULL, objname nvarchar(170) NULL, UNIQUE (dbid, objid, indid)) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET NOCOUNT ON SELECT @minspid = 6, @now = getdate() -- First caputure all locks. These can be aggregate, or we can get all of them. IF @details = 0 BEGIN INSERT #locks (spid, ecid, req_mode, rsc_type, req_status, req_ownertype, dbid, objid, indid, rsc_text, activelock, cnt) SELECT req_spid, req_ecid, req_mode, rsc_type, req_status, req_ownertype, rsc_dbid, rsc_objid, rsc_indid, CASE rsc_type WHEN 10 THEN rsc_text END, CASE WHEN rsc_type = 2 AND req_status = 1 THEN 0 ELSE 1 END, COUNT(*) FROM master.dbo.syslockinfo GROUP BY req_spid, req_ecid, req_mode, rsc_type, req_status, req_ownertype, rsc_dbid, rsc_objid, rsc_indid, CASE rsc_type WHEN 10 THEN rsc_text END, CASE WHEN rsc_type = 2 AND req_status = 1 THEN 0 ELSE 1 END OPTION (KEEP PLAN) END ELSE BEGIN INSERT #locks (spid, ecid, req_mode, rsc_type, req_status, req_ownertype, dbid, objid, indid, rsc_text, refcnt, activelock) SELECT req_spid, req_ecid, req_mode, rsc_type, req_status, req_ownertype, rsc_dbid, rsc_objid, rsc_indid, rsc_text, req_refcnt, CASE WHEN rsc_type = 2 AND req_status = 1 THEN 0 ELSE 1 END FROM master.dbo.syslockinfo OPTION (KEEP PLAN) END -- Then get the processes. We filter here for active processes once for all INSERT #procs(spid, ecid, login, status, dbname, host, command, appl, opntrn, blking, blkby, blklvl, waittime, waittype, cpu, physio, memusage, now, login_time, last_batch, last_since) SELECT p.spid, p.ecid, suser_sname(p.sid), rtrim(p.status), CASE WHEN p.dbid > 0 THEN db_name(p.dbid) ELSE '' END, rtrim(p.hostname), rtrim(p.cmd), rtrim(p.program_name), p.open_tran, 0, p.blocked, 0, p.waittime, p.waittype, p.cpu, p.physical_io, p.memusage, @now, convert(char(7), p.login_time, 12) + convert(char(8), p.login_time, 8), convert(char(7), p.last_batch, 12) + convert(char(8), p.last_batch, 8), CASE WHEN datediff(DAY, p.last_batch, @now) > 20 THEN NULL ELSE datediff(MS, p.last_batch, @now) / 1000.000 END FROM master.dbo.sysprocesses p WHERE @processes > 0 OR (upper(p.cmd) <> 'AWAITING COMMAND' AND p.spid >= @minspid AND p.spid <> @@spid) OR (p.open_tran > 0 AND p.spid <> @@spid) OR p.blocked > 0 OR (EXISTS (SELECT * FROM #locks l WHERE l.spid = p.spid AND l.activelock = 1) AND spid <> @@spid) OPTION (KEEP PLAN) -- Mark inactive processes; this is only interesting if @processes = 1, -- because with @processes = 0 we only have active now. IF @processes = 1 BEGIN UPDATE #procs SET active = 0 FROM #procs p WHERE NOT EXISTS (SELECT * FROM #locks l WHERE p.spid = l.spid AND p.ecid = l.ecid AND l.activelock = 1 AND p.spid <> @@spid AND p.spid >= @minspid) AND (p.command = 'AWAITING COMMAND' OR p.spid < @minspid OR p.spid = @@spid) AND p.blkby = 0 OPTION (KEEP PLAN) END -- Get input buffers. Note that only the main thread, ecid = 0 is of interest. DECLARE C1 CURSOR LOCAL FOR SELECT str(spid), spid FROM #procs WHERE (@processes = 2 OR active = 1) AND ecid = 0 AND login IS NOT NULL OPTION (KEEP PLAN) OPEN C1 WHILE 1 = 1 BEGIN FETCH C1 INTO @spidstr, @spid IF @@fetch_status <> 0 BREAK INSERT #inputbuffer EXEC ('DBCC INPUTBUFFER (' + @spidstr + ') WITH NO_INFOMSGS') SELECT @str = ' ' SELECT @str = rtrim(eventinfo) FROM #inputbuffer -- Replace line breaks with spaces. SET @str = replace(@str, char(10) + char(13), ' ') SET @str = replace(@str, char(10), ' ') SET @str = replace(@str, char(13), ' ') UPDATE #procs SET inputbuffer = isnull(@str, ' '), delay = datediff(ms, now, @now) WHERE spid = @spid OPTION (KEEP PLAN) DELETE #inputbuffer OPTION (KEEP PLAN) END DEALLOCATE C1 -- Delete inactive processes from #locks. IF @processes = 0 BEGIN DELETE #locks FROM #locks l WHERE NOT EXISTS (SELECT * FROM #procs p WHERE p.spid = l.spid AND p.active = 1) OPTION (KEEP PLAN) END -- Get name of objects. INSERT #objects (dbid, objid, indid) SELECT DISTINCT dbid, objid, indid FROM #locks WHERE dbid > 0 AND objid > 0 OPTION (KEEP PLAN) DECLARE C2 CURSOR LOCAL FOR SELECT DISTINCT dbid, db_name(dbid), quotename(db_name(dbid)) FROM #objects OPEN C2 WHILE 1 = 1 BEGIN FETCH C2 INTO @dbid, @dbname, @qdbname IF @@fetch_status <> 0 BREAK -- Set database.owner.name(.index) of all objects in #objects. SELECT @stmt = ' UPDATE #objects SET objname = ''' + @dbname + '.'' + u.name + ''.'' + o.name + CASE coalesce(t.indid, 0) WHEN 0 THEN '''' ELSE ''.'' + i.name END FROM #objects t JOIN ' + @qdbname + '.dbo.sysobjects o ON t.objid = o.id JOIN ' + @qdbname + '.dbo.sysusers u ON u.uid = o.uid LEFT JOIN ' + @qdbname + '.dbo.sysindexes i ON t.indid = i.indid AND t.objid = i.id WHERE t.dbid = ' + str(@dbid) + ' AND t.objid > 0 ' EXEC (@stmt) END DEALLOCATE C2 -- Flag blocking and blocked processes UPDATE #procs SET blking = 1 FROM #procs p WHERE EXISTS (SELECT * FROM #procs p2 WHERE p.spid = p2.blkby) OPTION (KEEP PLAN) UPDATE #procs SET blklvl = 1 WHERE blking = 1 AND blkby = 0 OPTION (KEEP PLAN) SELECT @blklvl = 1 -- Find out place in the queue for blocked processes. WHILE EXISTS (SELECT * FROM #procs WHERE blkby > 0 AND blklvl = 0) AND @blklvl < 20 BEGIN UPDATE p1 SET blklvl = @blklvl + 1 FROM #procs p1 JOIN #procs p2 ON p1.blkby = p2.spid WHERE p1.blkby > 0 AND p1.blklvl = 0 AND p2.blklvl = @blklvl OPTION (KEEP PLAN) SELECT @blklvl = @blklvl + 1 END -- For Plain results we are ready to return now. IF @fancy = 0 BEGIN SELECT spid = coalesce(p.spid, l.spid), ecid = coalesce(p.ecid, l.ecid), cnt = CASE @details WHEN 0 THEN coalesce(l.cnt, 0) WHEN 1 THEN coalesce(l.refcnt, 0) END, login = coalesce(p.login, ''), prcstatus = coalesce(p.status, ''), command = coalesce(p.command, ''), dbname = coalesce(p.dbname, ''), host = coalesce(p.host, ''), appl = coalesce(p.appl, ''), opntrn = coalesce(convert(varchar(5), p.opntrn), ''), lvl = CASE coalesce(p.blklvl, 0) WHEN 0 THEN '' WHEN 1 THEN '!!' ELSE convert(varchar(3), p.blklvl - 1) END, blkby = CASE coalesce(p.blkby, 0) WHEN 0 THEN '' ELSE convert(varchar(5), p.blkby) END, locktype = coalesce(v1.name, ''), ownertype = coalesce(v2.name, ''), object = CASE WHEN l.rsc_type = 10 THEN rtrim(l.rsc_text) WHEN l.rsc_type = 2 THEN rtrim(db_name(l.dbid)) WHEN l.rsc_type IS NOT NULL THEN coalesce(o1.objname, db_name(l.dbid) + '.MISSING?') ELSE '' END, rsctype = coalesce(v3.name, ''), lstatus = coalesce(v4.name, ''), waittime = CASE coalesce(p.waittime, 0) WHEN 0 THEN '' ELSE convert(varchar(10), p.waittime) END, p.waittype, cpu = coalesce(convert(varchar(10), p.cpu), ''), physio = coalesce(convert(varchar(10), p.physio), ''), memusg = coalesce(convert(varchar(10), p.memusage), ''), now = convert(char(12), p.now, 114), login_time = coalesce(p.login_time, ''), last_batch = coalesce(p.last_batch, ''), last_since = coalesce(str(p.last_since, 11, 3), ''), delay = coalesce(convert(varchar(10), p.delay), ''), inputbuffer = coalesce(p.inputbuffer, '') FROM #procs p FULL JOIN (#locks l LEFT JOIN master.dbo.spt_values v1 ON v1.number = l.req_mode + 1 AND v1.type = 'L' LEFT JOIN master.dbo.spt_values v2 ON v2.number = l.req_ownertype AND v2.type = 'LO' LEFT JOIN master.dbo.spt_values v3 ON v3.number = l.rsc_type AND v3.type = 'LR' LEFT JOIN master.dbo.spt_values v4 ON v4.number = l.req_status AND v4.type = 'LS' LEFT JOIN #objects o1 ON l.dbid = o1.dbid AND l.objid = o1.objid AND l.indid = o1.indid) ON p.spid = l.spid AND p.ecid = l.ecid ORDER BY spid, ecid, lstatus DESC, object OPTION (KEEP PLAN) END ELSE BEGIN INSERT #result (spid, ecid, cnt, login, prcstatus, command, dbname, host, appl, opntrn, lvl, blkby, locktype, ownertype, object, rsctype, lstatus, waittime, p.waittype, cpu, physio, memusg, now, login_time, last_batch, last_since, delay, inputbuffer) SELECT spid = coalesce(p.spid, l.spid), ecid = coalesce(p.ecid, l.ecid), cnt = CASE @details WHEN 0 THEN coalesce(l.cnt, 0) WHEN 1 THEN coalesce(l.refcnt, 0) END, login = coalesce(p.login, ''), prcstatus = coalesce(p.status, ''), command = coalesce(p.command, ''), dbname = coalesce(p.dbname, ''), host = coalesce(p.host, ''), appl = coalesce(p.appl, ''), opntrn = coalesce(convert(varchar(5), p.opntrn), ''), lvl = CASE coalesce(p.blklvl, 0) WHEN 0 THEN '' WHEN 1 THEN '!!' ELSE convert(varchar(3), p.blklvl - 1) END, blkby = CASE coalesce(p.blkby, 0) WHEN 0 THEN '' ELSE convert(varchar(5), p.blkby) END, locktype = coalesce(v1.name, ''), ownertype = coalesce(v2.name, ''), object = CASE WHEN l.rsc_type = 10 THEN rtrim(l.rsc_text) WHEN l.rsc_type = 2 THEN rtrim(db_name(l.dbid)) WHEN l.rsc_type IS NOT NULL THEN coalesce(o1.objname, db_name(l.dbid) + '.MISSING?') ELSE '' END, rsctype = coalesce(v3.name, ''), lstatus = coalesce(v4.name, ''), waittime = CASE coalesce(p.waittime, 0) WHEN 0 THEN '' ELSE convert(varchar(10), p.waittime) END, p.waittype, cpu = coalesce(convert(varchar(10), p.cpu), ''), physio = coalesce(convert(varchar(10), p.physio), ''), memusg = coalesce(convert(varchar(10), p.memusage), ''), now = convert(char(12), p.now, 114), login_time = coalesce(p.login_time, ''), last_batch = coalesce(p.last_batch, ''), last_since = coalesce(str(p.last_since, 11, 3), ''), delay = coalesce(convert(varchar(10), p.delay), ''), inputbuffer = coalesce(p.inputbuffer, '') FROM #procs p FULL JOIN (#locks l LEFT JOIN master.dbo.spt_values v1 ON v1.number = l.req_mode + 1 AND v1.type = 'L' LEFT JOIN master.dbo.spt_values v2 ON v2.number = l.req_ownertype AND v2.type = 'LO' LEFT JOIN master.dbo.spt_values v3 ON v3.number = l.rsc_type AND v3.type = 'LR' LEFT JOIN master.dbo.spt_values v4 ON v4.number = l.req_status AND v4.type = 'LS' LEFT JOIN #objects o1 ON l.dbid = o1.dbid AND l.objid = o1.objid AND l.indid = o1.indid) ON p.spid = l.spid AND p.ecid = l.ecid ORDER BY spid, ecid, lstatus DESC, object OPTION (KEEP PLAN) -- Mark last row. UPDATE r1 SET last = 1 FROM #result r1 JOIN (SELECT spid, ident = MAX(ident) FROM #result GROUP BY spid) AS r2 ON r2.ident = r1.ident OPTION (KEEP PLAN) SELECT @spidlen = convert(varchar(5), coalesce(max(len(ltrim(str(spid)))), 1)), @ecidlen = convert(varchar(5), coalesce(max(len(ltrim(str(ecid)))), 1)), @cntlen = convert(varchar(5), coalesce(max(len(ltrim(str(cnt)))), 1)), @loginlen = convert(varchar(5), coalesce(nullif(max(len(login)), 0), 1)), @cntlen = convert(varchar(5), coalesce(nullif(max(len(ltrim(str(cnt)))), 0), 1)), @statuslen = convert(varchar(5), coalesce(nullif(max(len(prcstatus)), 0), 1)), @dbnamelen = convert(varchar(5), coalesce(nullif(max(len(dbname)), 0), 1)), @hostlen = convert(varchar(5), coalesce(nullif(max(len(host)), 0), 1)), @cmdlen = convert(varchar(5), coalesce(nullif(max(len(command)), 0), 1)), @appllen = convert(varchar(5), coalesce(nullif(max(len(appl)), 0), 1)), @waitlen = convert(varchar(5), coalesce(nullif(max(len(waittime)), 0), 1)), @locktlen = convert(varchar(5), coalesce(nullif(max(len(locktype)), 0), 1)), @lkobjlen = convert(varchar(5), coalesce(nullif(max(len(object)), 0), 1)), @ownertlen = convert(varchar(5), coalesce(nullif(max(len(ownertype)), 0), 1)), @restlen = convert(varchar(5), coalesce(nullif(max(len(rsctype)), 0), 1)), @lkstatlen = convert(varchar(5), coalesce(nullif(max(len(lstatus)), 0), 1)), @cpulen = convert(varchar(5), coalesce(nullif(max(len(cpu)), 0), 1)), @physiolen = convert(varchar(5), coalesce(nullif(max(len(physio)), 0), 1)), @memlen = convert(varchar(5), coalesce(nullif(max(len(memusg)), 0), 1)), @delaylen = convert(varchar(5), coalesce(nullif(max(len(delay)), 0), 1)), @inputlen = convert(varchar(5), coalesce(nullif(max(len(inputbuffer)), 0), 1)) FROM #result OPTION (KEEP PLAN) -- ====== return the #results table ===== EXEC ('SELECT spid = str(spid, ' + @spidlen + '), ecid = str(ecid, ' + @ecidlen + '), cnt = convert(varchar( ' + @cntlen + '), cnt), login = convert(varchar( ' + @loginlen + '), login), prcstatus = convert(varchar( ' + @statuslen + '), prcstatus), command = convert(varchar( ' + @cmdlen + '), command), dbname = convert(varchar( ' + @dbnamelen + '), dbname), host = convert(varchar( ' + @hostlen + '), host), appl = convert(varchar( ' + @appllen + '), appl), opntrn, lvl, blkby, locktype = convert(varchar( ' + @locktlen + '), locktype), ownertype = convert(varchar( ' + @ownertlen + '), ownertype), object = convert(varchar( ' + @lkobjlen + '), object), rsctype = convert(varchar( ' + @restlen + '), rsctype), lstatus = convert(varchar( ' + @lkstatlen + '), lstatus), waittime = convert(varchar( ' + @waitlen + '), waittime), waittype, cpu = convert(varchar( ' + @cpulen + '), cpu), io = convert(varchar( ' + @physiolen + '), physio), memusg = convert(varchar( ' + @memlen + '), memusg), now, login_time, last_batch, last_since, delay = convert(varchar( ' + @delaylen + '), delay), intputbuffer = convert(varchar( ' + @inputlen + '), inputbuffer), CASE last WHEN 1 THEN char(10) ELSE '' '' END FROM #result ORDER BY ident') END