sqlserver 查看死锁和运行进程
|
USE [master]
GO /****** Object: ?StoredProcedure [dbo].[sp_who_run] ? ?Script Date: 02/28/2017 14:34:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_who_run] AS BEGIN ? ? SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ? ? SELECT [Spid] = session_Id ? ?,ecid ? ?,[Database] = DB_NAME(sp.dbid) ? ?,[User] = nt_username ? ?,[Status] = er.status ? ?,[Wait] = wait_type ? ?,[Individual Query] = SUBSTRING (qt.text,? ? ? ? ? ? ? ?er.statement_start_offset/2, ? ? (CASE WHEN er.statement_end_offset = -1 ? ? ? ? ? ?THEN LEN(CONVERT(NVARCHAR(MAX),qt.text)) * 2 ? ? ? ? ELSE er.statement_end_offset END -? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? er.statement_start_offset)/2) ? ?,[Parent Query] = qt.text ? ?,Program = program_name ? ?,Hostname ? ?,nt_domain ? ?,start_time ? ?,logical_reads ? ? FROM sys.dm_exec_requests er ? ? INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid ? ? CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt ? ? WHERE session_Id > 50 ? ? ? ? ? ? ?-- Ignore system spids. ? ? AND session_Id NOT IN (@@SPID) ? ? -- Ignore this current statement. ? ? ORDER BY start_time END ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- USE [master] GO /****** Object: ?StoredProcedure [dbo].[sp_who_lock] ? ?Script Date: 02/28/2017 14:19:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ? ALTER procedure [dbo].[sp_who_lock] as begin declare @spid int,@bl int,?@intTransactionCountOnEntry ?int,? ? ? ? @intRowcount ? ?int,? ? ? ? @intCountProperties ? int,? ? ? ? @intCounter ? ?int ?create table #tmp_lock_who ( ?id int identity(1,1),?spid smallint,?bl smallint) ? ?IF @@ERROR<>0 RETURN @@ERROR ? ?insert into #tmp_lock_who(spid,bl) select ?0,blocked ? ?from (select * from sysprocesses where ?blocked>0 ) a? ? ?where not exists(select * from (select * from sysprocesses where ?blocked>0 ) b? ? ?where a.blocked=spid) ? ?union select spid,blocked from sysprocesses where ?blocked>0 ?IF @@ERROR<>0 RETURN @@ERROR? ?? -- 找到临时表的记录数 ?select ?@intCountProperties = Count(*),@intCounter = 1 ?from #tmp_lock_who ? ?IF @@ERROR<>0 RETURN @@ERROR? ? ?if @intCountProperties=0 ? select '现在没有阻塞和死锁信息' as message -- 循环开始 while @intCounter <= @intCountProperties begin -- 取第一条记录 ? select ?@spid = spid,@bl = bl ? from #tmp_lock_who where Id = @intCounter? ?begin ? if @spid =0? ? ? ? ? ? ? select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' ?else ? ? ? ? ? ? select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' ?DBCC INPUTBUFFER (@bl ) ?end? -- 循环指针下移 ?set @intCounter = @intCounter + 1 end drop table #tmp_lock_who return 0 end （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |