Here is a query you can use to get a more detailed view on what is happening on the SQL Server, and perhaps help you map the SPID in "Online Users" against SPID on the SQL Server.
-- forums.learnsqlserver.com/SqlServerTopic40.aspx
USE master
GO
IF ( SELECT OBJECTPROPERTY(OBJECT_ID('sp_lock3'), 'IsProcedure')) = 1
DROP PROC dbo.sp_lock3
GO
CREATE PROC dbo.sp_lock3 (
@spid1 INT = NULL /* Check only this spid; if this is NULL then all spids will be checked */
, @spid2 INT = NULL /* and this spid; if this is not null, @spid1 must be not null as well */
)
AS
CREATE TABLE #locktable (
spid SMALLINT
, loginname NVARCHAR(128)
, hostname NVARCHAR(128)
, dbid INT
, dbname NVARCHAR(128)
, objId INT
, ObjName NVARCHAR(128)
, IndId INT
, Type NVARCHAR(4)
, Resource NVARCHAR(16)
, Mode NVARCHAR(8)
, Status NVARCHAR(5)
)
SET NOCOUNT ON
IF @spid2 IS NOT NULL AND @spid1 IS NULL
SET @spid1 = @spid2
DECLARE @object_id INT,
@dbid INT,
@DynamicSql NVARCHAR(255)
/***** @spid1 is provided so show only the locks for @spid1 and @spid2 *****/
IF @spid1 IS NOT NULL
INSERT #locktable ( spid, loginname, hostname, dbid, dbname, objId, ObjName, IndId, Type, Resource, Mode, Status )
SELECT CONVERT (SMALLINT, l.req_spid)
, COALESCE(SUBSTRING (s.loginame, 1, 128), '')
, COALESCE(SUBSTRING (s.hostname, 1, 128), '')
, l.rsc_dbid
, SUBSTRING (DB_NAME(l.rsc_dbid), 1, 128)
, l.rsc_objid
, ''
, l.rsc_indid
, SUBSTRING (v.name, 1, 4)
, SUBSTRING (l.rsc_text, 1, 16)
, SUBSTRING (u.name, 1, 8)
, SUBSTRING (x.name, 1, 5)
FROM master.dbo.syslockinfo l JOIN master.dbo.spt_values v
ON l.rsc_type = v.number
JOIN master.dbo.spt_values x
ON l.req_status = x.number
JOIN master.dbo.spt_values u
ON l.req_mode + 1 = u.number
JOIN master.dbo.sysprocesses s
ON l.req_spid = s.spid
WHERE v.type = 'LR' AND x.type = 'LS' AND u.type = 'L' AND l.req_spid in (@spid1, @spid2) and l.rsc_dbid not in (32767)
ELSE /***** @spid1 is not provided so show all the locks *****/
INSERT #locktable ( spid, loginname, hostname, dbid, dbname, objId, ObjName, IndId, Type, Resource, Mode, Status )
SELECT CONVERT (SMALLINT, l.req_spid)
, COALESCE(SUBSTRING (s.loginame, 1, 128), '')
, COALESCE(SUBSTRING (s.hostname, 1, 128), '')
, l.rsc_dbid
, SUBSTRING (DB_NAME(l.rsc_dbid), 1, 128)
, l.rsc_objid
, ''
, l.rsc_indid
, SUBSTRING (v.name, 1, 4)
, SUBSTRING (l.rsc_text, 1, 16)
, SUBSTRING (u.name, 1, 8)
, SUBSTRING (x.name, 1, 5)
FROM master.dbo.syslockinfo l JOIN master.dbo.spt_values v
ON l.rsc_type = v.number
JOIN master.dbo.spt_values x
ON l.req_status = x.number
JOIN master.dbo.spt_values u
ON l.req_mode + 1 = u.number
JOIN master.dbo.sysprocesses s
ON l.req_spid = s.spid
WHERE v.type = 'LR' AND x.type = 'LS' AND u.type = 'L' and l.rsc_dbid not in (32767)
/**********************************************************************************************
Because the locks exist in any database, you must USE <database name> before running OBJECT_NAME
We use a dynamic SQL loop to loop through each row from #locktable
A temp table is required here since SQL Server 2000 cannot access a table variable when issuing dynamic sql
**********************************************************************************************/
-- Initialize the loop
SELECT TOP 1 @dbid = dbid, @object_id = ObjId FROM #locktable WHERE Type ='TAB' AND ObjName = ''
WHILE @dbid IS NOT NULL
BEGIN
SELECT @DynamicSql =
'USE ' + DB_NAME(@dbid) + char(13)
+ 'UPDATE #locktable SET ObjName = OBJECT_NAME('
+ CONVERT(VARCHAR, @object_id) + ') WHERE dbid = ' + CONVERT(VARCHAR, @dbId)
+ ' AND objid = ' + CONVERT(VARCHAR, @object_id)
EXEC sp_executesql @DynamicSql
SET @dbid = NULL -- TSQL preserves the "old" value unless you initialize it to NULL
SELECT @dbid = dbid, @object_id = ObjId FROM #locktable WHERE Type ='TAB' AND ObjName = ''
END
SELECT * FROM #locktable
WHERE objname NOT LIKE '#locktable_____%' -- don't return this temp table
AND objid > 100 -- do not return system table locks
AND objname <> 'spt_values'
GO
You run the query above to install the sp_lock3 procedure on the instance, and after that it is available globally on that SQL Server instance. You then simply open a new query window and run "sp_lock3".
