一个生成死锁日志的存储过程
1、sysdatabases
Microsoft® SQL Server™ 上的每个数据库在表中占一行。最初安装 SQL Server 时，sysdatabases 包含 master、model、msdb、mssqlweb 和 tempdb 数据库的项。该表只存储在 master 数据库中。
2、sysprocesses
sysprocesses 表中保存关于运行在 Microsoft® SQL Server™ 上的进程的信息。这些进程可以是客户端进程或系统进程。sysprocesses 只存储在 master 数据库中。
3、syslockinfo
包含有关所有已授权、正在转换和正在等待的锁请求的信息。此表是锁管理器内部数据结构的非规范化表格格式视图，只存储在 master 数据库中。
4、sysindexes
数据库中的每个索引和表在表中各占一行。该表存储在每个数据库中。
sysdatabases包含所数据库的信息。
sysprocesses包含当前所有活动的进程。
syslockinfo包含所有阻塞或被阻塞的进程信息。
sysindexes包含所有表名的索引信息。
sysprocesses表的spid字段与syslockinfo表的req_spid对应，
syslockinfo表的rsc_dbid字段与sysdatabases表的dbid对应
syslockinfo表的rsc_objid字段与sysindexes(该表在当前被锁的库中)的字段id对应。
请将以下表与存储过程新建在master数据库中。
表：
CREATE TABLE [custom_ProcessesLog] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[blockedCount] [int] NULL ,
[isblocked] [bit] NULL ,
[spidList] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[blockedList] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[tableList] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[buildDate] [datetime] NOT NULL CONSTRAINT [DF_ServerRunInfo_InputTime] DEFAULT (getdate())
) ON [PRIMARY]
GO
存储过程：
CREATE Procedure custom_BuildProcesses AS
Declare
@i int,
@forCount int,
@spid smallint,
@blocked smallint,
@dbid smallint,
@objid int,
@dbname varchar(128),
@tablename varchar(128),
@blockedCount int,
@isblocked bit,
@spidList varchar(255),
@blockedList varchar(255),
@tableList varchar(255),
@blockedDate datetime,
@query nvarchar(800);
Create Table #Stack(spid smallint, blocked smallint, isblocked bit)
INSERT INTO #Stack Select * From (
select spid, blocked, case when spid in (
select blocked from master.dbo.sysprocesses) then '1' else '0' end As isblocked from master.dbo.sysprocesses) As T1 where T1.isblocked='1'
Select @forCount=Count(*) From #Stack
select @i = 0, @blockedCount=@forCount, @isblocked=case when @forCount>0 then 1 else 0 end, @spidList='',
@blockedList='', @tableList='', @blockedDate=getDate()
while @i<@forCount
begin
select top 1 @spid=spid, @blocked=blocked from #Stack
delete from #Stack where spid=@spid
select @dbid=rsc_dbid, @objid=rsc_objid from master.dbo.syslockinfo where (req_spid = @spid) AND (rsc_type = 5) --得到数据库ID和表的引ID
select @dbname=name from master.dbo.sysdatabases where dbid=@dbid -- 跟据数据库ID得到数据库名称
select @query=('select @tablename=name from '+@dbname+'.dbo.sysindexes where id='+str(@objid))
exec sp_executesql @query, N'@tablename varchar(128) out',
@tablename out
select @spidList = @spidList+RTrim(Ltrim(Str(@spid)))+',',
@blockedList = @blockedList+RTrim(Ltrim(Str(@blocked )))+',',
@tableList = @tableList+RTrim(LTrim(@tablename))+','
select @i = @i+1
end
insert into custom_ProcessesLog (blockedCount, isblocked, spidList, blockedList, tableList)
values(@blockedCount, @isblocked, @spidList, @blockedList, @tableList)
GO
版权所有 2004 cjsh 保留所有权利。欢迎转载，请注明出处：http://www.cnblogs.com/cjsh
