SP_lock2 Returns Additional Locking Details in MS-SQL

The sp_lock2 procedure is an enhanced version of the sp_lock SQL Server system stored procedure (see SQL Server 7.0 Books Online for more documentation). In addition to the output of sp_lock, sp_lock2 returns the names for users, databases, and tables involved in the current locks, and therefore it can help you analyze a locking scenario.

The sp_lock2 procedure returns the following additional information to sp_lock:
1.User names for the listed system process IDs (SPIDs).
2.Database names for the listed databases.
3.Table names for the listed database objects.

You can use the following script to create the sp_lock2 procedure for SQL Server 7.0:


USE MASTER
GO
create procedure sp_lock2
@spid1 int = NULL, /* server process id to check for locks */
@spid2 int = NULL /* other process id to check for locks */
as

set nocount on
/*
** Show the locks for both parameters.
*/
declare @objid int,
@dbid int,
@string Nvarchar(255)

CREATE TABLE #locktable
(
spid smallint
,loginname nvarchar(20)
,hostname nvarchar(30)
,dbid int
,dbname nvarchar(20)
,objId int
,ObjName nvarchar(128)
,IndId int
,Type nvarchar(4)
,Resource nvarchar(16)
,Mode nvarchar(8)
,Status nvarchar(5)
)

if @spid1 is not NULL
begin
INSERT #locktable
(
spid
,loginname
,hostname
,dbid
,dbname
,objId
,ObjName
,IndId
,Type
,Resource
,Mode
,Status
)
select convert (smallint, l.req_spid)
--,coalesce(substring (user_name(req_spid), 1, 20),'')
,coalesce(substring (s.loginame, 1, 20),'')
,coalesce(substring (s.hostname, 1, 30),'')
,l.rsc_dbid
,substring (db_name(l.rsc_dbid), 1, 20)
,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,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u,
master.dbo.sysprocesses s
where l.rsc_type = v.number
and v.type = 'LR'
and l.req_status = x.number
and x.type = 'LS'
and l.req_mode + 1 = u.number
and u.type = 'L'
and req_spid in (@spid1, @spid2)
and req_spid = s.spid
end
/*
** No parameters, so show all the locks.
*/
else
begin
INSERT #locktable
(
spid
,loginname
,hostname
,dbid
,dbname
,objId
,ObjName
,IndId
,Type
,Resource
,Mode
,Status
)
select convert (smallint, l.req_spid)
--,coalesce(substring (user_name(req_spid), 1, 20),'')
,coalesce(substring (s.loginame, 1, 20),'')
,coalesce(substring (s.hostname, 1, 30),'')
,l.rsc_dbid
,substring (db_name(l.rsc_dbid), 1, 20)
,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,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u,
master.dbo.sysprocesses s
where l.rsc_type = v.number
and v.type = 'LR'
and l.req_status = x.number
and x.type = 'LS'
and l.req_mode + 1 = u.number
and u.type = 'L'
and req_spid = s.spid
order by spID
END
DECLARE lock_cursor CURSOR
FOR SELECT dbid, ObjId FROM #locktable WHERE Type ='TAB'

OPEN lock_cursor
FETCH NEXT FROM lock_cursor INTO @dbid, @ObjId
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @string =
'USE ' + db_name(@dbid) + char(13)
+ 'UPDATE #locktable SET ObjName = object_name('
+ convert(varchar(32),@objId) + ') WHERE dbid = ' + convert(varchar(32),@dbId)
+ ' AND objid = ' + convert(varchar(32),@objId)

EXECUTE (@string)
FETCH NEXT FROM lock_cursor INTO @dbid, @ObjId
END
CLOSE lock_cursor
DEALLOCATE lock_cursor


SELECT * FROM #locktable
return (0)
-- END sp_lock2
GO


Referance : http://support.microsoft.com/kb/q255596

Hiç yorum yok:

Visual Studio 2017'de Devexpress 17.2.5 Toolbox görünmüyor

Visual Studio 2017 Toolbox'ı üzerinde Developer Express componentlerini göremiyorsanız aşağıdaki komutu çalıştırmak işini görecektir. (...