/*******************************************
* 查询数据库锁表信息
*******************************************/
CREATE PROC [dbo].[sp_lockinfo]
@kill_lock_spid BIT = 0,--是否杀掉死锁的进程 1 杀掉 0 仅显示
@show_spid_if_nolock BIT = 0 --如果没有死锁的进程,是否显示正常的 1 显示 0 不显示
AS
SET NOCOUNT ON
DECLARE @count INT,
@s NVARCHAR(3000),
@i INT
SELECT id= IDENTITY(INT,1,1),标志,
进程ID=spid,线程ID=kpid,阻塞进程ID=blocked,数据库ID=dbid,
数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,
登陆时间=login_time,打开事物数=open_tran,进程状态=status,
工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,
域名=nt_domain,网卡地址=net_address
INTO #t FROM (
SELECT 标志='死锁的进程',
spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,STATUS,hostname,program_name,hostprocess,nt_domain,net_address,
s1=a.spid,s2=0
FROM MASTER..sysprocesses a JOIN(
SELECT blocked FROM MASTER..sysprocesses GROUP BY blocked
) b ON a.spid = b.blocked WHERE a.blocked = 0
UNION ALL
SELECT '|_被锁进程_>',
spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,STATUS,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
FROM [master]..sysprocesses a WHERE blocked <> 0
) a ORDER BY s1,s2
SELECT @count=@@ROWCOUNT,@i = 1
IF @count = 0 AND @show_spid_if_nolock = 1
BEGIN
INSERT INTO #t
SELECT 标志='正常的进程',
spid,kpid,blocked,dbid,DB_NAME(dbid),uid,loginame,cpu,login_time,open_tran,STATUS,hostname,program_name,hostprocess,nt_domain,net_address
FROM master..sysprocesses
SET @count = @@ROWCOUNT
END
--IF @count > 1
CREATE TABLE #t1(id INT IDENTITY(1,1),a NVARCHAR(30),b INT ,eventinfo NVARCHAR(4000))
IF @kill_lock_spid = 1
BEGIN
DECLARE @spid VARCHAR(50),@标志 varchar(50)
WHILE @i <= @count
BEGIN
SELECT @spid = 进程ID,@标志 = 标志 from #t where id = @i
INSERT #t1 EXEC ('dbcc inputbuffer('+@spid+')')
IF @@ROWCOUNT = 0 INSERT #t1(a) VALUES(NULL)
IF @标志='死锁的进程' exec('kill '+@spid)
SET @i= @i + 1
END
END
ELSE
WHILE @i <= @count
BEGIN
SELECT @s = 'dbcc inputbuffer('+CAST(进程ID as VARCHAR)+')' from #t where id = @i
INSERT #t1 EXEC (@s)
IF @@ROWCOUNT = 0 INSERT #t1(a) VALUES(NULL)
SET @i = @i + 1
END
SELECT a.*,c.client_net_address,进程的SQL语句=b.eventinfo
FROM #t a JOIN #t1 b ON a.id = b.id join sys.dm_exec_connections c on a.进程ID = c.session_id
ORDER BY 进程id