[SQL]sp_lock2
昨天到客戶端查看SQL2005 DB被Lock的問題，本來想利用「活動監視器」來看到底那些Process Lock呢?
結果居然開不了，只好用sp_lock + DBCC INPUTBUFFER (spid) + OBJECT_NAME(objid) 來一個一個看!相當不方便!
所以上網找了sp_lock2，結果在定序Chinese_Taiwan_Stroke_BIN下，有大小寫的錯誤，如下，
Msg 911, Level 16, State 1, Line 1
Database 'MASTER' does not exist. Make sure that the name is entered correctly.
Msg 137, Level 15, State 2, Procedure sp_lock2, Line 125
Must declare the scalar variable "@ObjId".
Msg 137, Level 15, State 2, Procedure sp_lock2, Line 131
Must declare the scalar variable "@objId".
Msg 137, Level 15, State 2, Procedure sp_lock2, Line 135
Must declare the scalar variable "@ObjId".
而且沒有顯示執行的SQL，所以就將它改一下，之後在查lock就會很方便了。
--http://support.microsoft.com/kb/255596/en-us?ln=en-us&sd=gn&fr=0
--1.修改變數大寫小不同的錯誤
--2.加入顯示執行的SQL
USE master
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[sp_lock2 ]')
AND type IN ( N'P', N'PC' ) )
DROP PROCEDURE [dbo].[sp_lock2]
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),
@Type NVARCHAR(4),
@spid SMALLINT
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),
EventInfo Nvarchar(4000)
)
CREATE TABLE #inputbuffer (
EventType varchar(30),
Parameters smallint,
EventInfo Nvarchar(4000)
)
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,
Type,
spid
FROM #locktable
--WHERE Type = 'TAB'
OPEN lock_cursor
FETCH NEXT FROM lock_cursor INTO @dbid, @objid, @Type, @spid
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Type = 'TAB'
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)
END
--assign DBCC INPUTBUFFER
SELECT @string = 'DBCC INPUTBUFFER(' + STR( @spid ) + ')'
DELETE #inputbuffer
INSERT INTO #inputbuffer
EXEC (@string)
SELECT @string = 'UPDATE #locktable SET EventInfo = (SELECT EventInfo FROM #inputbuffer) WHERE spid = ' + STR(@spid)
EXECUTE (@string)
FETCH NEXT FROM lock_cursor INTO @dbid, @objid, @Type, @spid
END
CLOSE lock_cursor
DEALLOCATE lock_cursor
SELECT *
FROM #locktable
RETURN (0)
-- END sp_lock2
GO
--EXEC sp_lock2
--DBCC INPUTBUFFER(51)
執行的畫面如下，
Source Code:
Hi,
亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」
請大家繼續支持 ^_^
