Warning: Undefined array key "HTTP_REFERER" in
/www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line
58
引起死锁的进程查看:它没有被阻塞,但别的进程被它阻塞着
select * from sysprocesses a where a.blocked=0
AND EXISTS( select * from sysprocesses b where b.blocked>0 and b.blocked = a.spid )
*--处理死锁
查看当前进程,或死锁进程,并能自动杀掉死进程
因为是针对死的,所以如果有死锁进程,只能查看死锁进程
当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程
--*/
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
ALTER PROC [dbo].[p_lockinfo]
@kill_lock_spid BIT = 1 , --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock BIT = 1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
AS
DECLARE @count INT ,
@s VARCHAR(MAX) ,
@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,
sql_handle,
stmt_start,
stmt_end
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,
sql_handle,
stmt_start,
stmt_end
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,
sql_handle,
stmt_start,
stmt_end
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 #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,
sql_handle,
stmt_start,
stmt_end
FROM master..sysprocesses
SET @count = @@rowcount
END
IF @count > 0
BEGIN
CREATE TABLE #t1
(
id INT IDENTITY(1, 1) ,
a NVARCHAR(30) ,
b INT ,
EventInfo NVARCHAR(255)
)
IF @kill_lock_spid = 1
BEGIN
DECLARE @spid VARCHAR(10) ,
@标志 VARCHAR(10)
WHILE @i <= @count
BEGIN
SELECT @spid = 进程ID ,
@标志 = 标志
FROM #t
WHERE id = @i
INSERT #t1
EXEC ( 'dbcc inputbuffer(' + @spid + ')'
)
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
)
SET @i = @i + 1
END
SELECT 标志 ,
数据库名 ,
进程的SQL语句 = b.EventInfo,
sqlText=substring(qt.text,a.stmt_start/2,
(case when a.stmt_end = -1
then len(convert(nvarchar(max), qt.text)) * 2
else a.stmt_end end -a.stmt_start)/2
) ,
进程状态,
用户名,
累计CPU时间,
登陆时间 ,
工作站名 ,
应用程序名,
进程ID ,
线程ID
FROM #t a
JOIN #t1 b ON a.id = b.id
OUTER apply sys.dm_exec_sql_text(a.sql_handle) as qt
END
GO