--MQLCK_profile 为数据库邮件配置名称
---- [dbo].[MQPrd_DBLockLog] 20,0,'Wan'
Create procedure [dbo].[MQPrd_DBLockLog] @nSec as int ,@isKill as bit,@cDBName char(12) WITH ENCRYPTION
as
begin
declare @spid int,@nLCKSPID int,@Atspid int,@isLock bit,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int
create table #tmp_lock_sql (
EventType varchar(200),
Parameters varchar(200),
EventInfo varchar(5000))
create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
set @isLock = 0
IF @@ERROR<>0
Select @@ERROR
insert into #tmp_lock_who(spid,bl)
Select distinct spid,bl
From (
select 0 as spid,blocked as bl from (select * from master.dbo.sysprocesses where blocked>0 ) a
where not exists
(select * from (select * from master.dbo.sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from master.dbo.sysprocesses where blocked>0
) AAA
IF @@ERROR<>0
Select @@ERROR
-- 找到临时表的记录数
select @intCountProperties = Count(*),@intCounter = 1 From #tmp_lock_who
IF @@ERROR<>0
Select @@ERROR
--if @intCountProperties=0
-- return
--Select * From #tmp_lock_who
-- select '现在没有阻塞和死锁信息' as message
Set @Atspid = -1
-- 循环开始
while @intCounter <= @intCountProperties
begin
set @isLock = 1
-- 取第一条记录
Select * From [Master].[dbo].[SYSPROCESSES] Where spid = @nLCKSPID
select @spid = spid,@nLCKSPID = bl
from #tmp_lock_who where Id = @intCounter
Begin
if @Atspid = @spid
begin
set @intCounter = @intCounter + 1
CONTINUE
end
if @spid =0
begin
set @Atspid = @nLCKSPID
select '引起数据库死锁的是: '+ CAST(@nLCKSPID AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
end
else
begin
set @Atspid = @spid
select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被'
+ '进程号SPID:'+ CAST(@nLCKSPID AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
end
--DBCC INPUTBUFFER (@nLCKSPID )
truncate table #tmp_lock_sql
Insert into #tmp_lock_sql(EventType,Parameters,EventInfo)
Exec('DBCC INPUTBUFFER ('+@Atspid+')')
--Select * From #tmp_lock_sql
Insert into MQ_DBLockLog([nLOCKID]
,[nSPID]
,[EventType]
,[Parameters]
,[EventInfo]
,[cpu]
,[physical_IO]
,[loginame]
,[login_time]
,[last_batch]
,[hostname]
,[program_name]
,[cmd]
,[Lastwaittype]
) Select distinct _tbLK.[nLOCKID]
,_tbLK.[nSPID]
,_tbLK.[EventType]
,_tbLK.[Parameters]
,_tbLK.[EventInfo]
,Case When SPE.[cpu] is null then 0 else SPE.[cpu] end [cpu]
,Case When SPE.[physical_IO] is null then 0 else SPE.[physical_IO] end [physical_IO]
,SPE.[loginame]
,SPE.[login_time]
,SPE.[last_batch]
,SPE.[hostname]
,SPE.[program_name]
,SPE.[cmd]
,SPE.[Lastwaittype]
From
( Select
@nLCKSPID as [nLOCKID]
,@Atspid as [nSPID]
,[EventType]
,[Parameters]
,[EventInfo]
From #tmp_lock_sql
) _tbLK
left join [Master].[dbo].[SYSPROCESSES] SPE on SPE.spid = _tbLK.[nSPID]
Where getdate() <'2018-09-29'
End
-- 循环指针下移
set @intCounter = @intCounter + 1
end
if (@isLock=1 And @isKill=1)
begin
Exec(' kill '+@nLCKSPID+' ')
end
if @isLock = 1
begin
--Declare @nSec as int,@nLCKSPID as int
--Set @nSec = 20
--set @nLCKSPID =324
Select dateadd(ss,-@nSec,GETDATE()),GETDATE()
Declare @nCount as int
Declare @EventType as varchar(200)
Declare @EventInfo as varchar(5000)
Declare @Cpu as int,@nLockID as int
,@physical_IO as int
,@loginame as nchar(128)
,@last_batch as datetime
,@hostname as nchar(128)
,@program_name as nchar(128)
Set @nLockID = 0
Select top 1
@nLockID = [nLOCKID]
From MQ_DBLockLog
Where isEMail = 0
And nLockID = @nLCKSPID
And Lastwaittype Like 'LCK%'
--And dCrtDate < dateadd(ss,-@nSec,GETDATE())
if @nLockID > 0
begin
Declare @dCrtDate as datetime
Declare @nLKSec as varchar(25)
Declare @nSPIDAT as int
Declare @EventInAT as varchar(5000)
Set @nSPIDAT = 0
Select Top 1
@EventInfo = [EventInfo]
,@Cpu=[cpu]
,@physical_IO=[physical_IO]
,@loginame=[loginame]
,@last_batch = [last_batch]
,@hostname = [hostname]
,@program_name = [program_name]
,@dCrtDate = dCrtDate
From MQ_DBLockLog
Where isEMail = 0 And nLockID = @nLockID
And nLOCKID = nSPID And [Lastwaittype] <> 'CXPACKET'
Order by dCrtDate
Select Top 1 @nSPIDAT = nSPID,@EventInAT = [EventInfo]
From MQ_DBLockLog
Where isEMail = 0 And nLockID = @nLockID
And nLOCKID <> nSPID And [Lastwaittype] <> 'CXPACKET'
Order by dCrtDate
Set @nLKSec = DATEDIFF(ss,@dCrtDate,getdate())
Declare @cEmail as varchar(8000)
if @nLKSec>@nSec * 3 And @nSPIDAT > 0
begin
Set @cEmail =
' 注意: ['+RTRIM( @cDBName)+']数据库出现互锁'+case when @isKill=1 then '且系统已偿试Kill互锁进程[自动Kill功能可关闭]' else '' end
+'<br />! 互锁进程主ID:'+Ltrim(Rtrim(CAST(@nLockID as varCHAR(18)))) +' 其中被锁进程ID:' + Ltrim(Rtrim(CAST(@nSPIDAT as varCHAR(18))))
+' <br />已消耗CPU指数:'+Ltrim(Rtrim(CAST(@Cpu as varCHAR(18))))
+' <br />已消耗硬盘读取指数:'+Ltrim(Rtrim(CAST(@physical_IO as varCHAR(18))))
+' <br />登录账号:' +Rtrim(@loginame)
+' <br />登录终端:' +Rtrim(@hostname)
+' <br />执行程序端:' + Rtrim(@program_name)
+' <br />已互锁秒数:' + cast(@nLKSec as varchar(25))
+' <br />============================= '
+' <br />主锁语句: '+ RTRIM(@EventInfo)
+' <br />----------------------------- '
+' <br />被锁语句: '+ RTRIM(@EventInAT)
--Select @cEmail
Declare @cSubject as varchar(200)
set @cSubject = ' 注意: [' + RTRIM( @cDBName) + ']数据库出现互锁'
Exec msdb..sp_send_dbmail
@profile_name = 'MQLCK_profile'
,@recipients = 'yidk@haday.cn;huangsl@haday.cn' -- 收件人邮箱
,@subject = @cSubject -- 邮件标题
,@body = @cEmail
,@body_format = 'html' -- 邮件格式
Update MQ_DBLockLog set [isEmail] = 1 ,[dEmail] = GETDATE() Where [isEmail] = 0
End
End
Update MQ_DBLockLog set [isEmail] = 1 Where [isEmail] = 0 And dCrtDate < dateadd(ss,-(@nSec*2),GETDATE())
End
--REturn
Declare @tDate as datetime
Set @tDate = GETDATE()
Select @nCount= COUNT(*) From (
Select 1 as nRec,nSPID From MQ_DBLockLog
Where dCrtDate > dateadd(ss,-(@nSec*2),@tDate) And isEmailXP = 0 And [Lastwaittype] <> 'CXPACKET'
Group by nSPID ) A
Declare @nMax as int
Declare @nHour as int
Set @nHour = DATEPART(hh,GETDATE())
if @nHour > 17 OR @nHour <8
begin
Set @nMax = @nSec * 5
end
Else
Begin
Set @nMax = @nSec
End
if @nCount>@nMax
begin
--Select CONVERT(varchar(20),getdate(),120)
set @cSubject = ' 注意: [' +RTRIM( @cDBName)+ ']数据库阻塞严重,需要立即处理!'
set @cEmail = ' 注意: [' +RTRIM( @cDBName)+ ']数据库阻塞严重,需要立即处理!' +
' <br />在'+cast(@nSec*2 as varchar(18))+'秒('+
CONVERT(varchar(20),dateadd(ss,-(@nSec*2),GETDATE()),120) +'->'+ CONVERT(varchar(20),GETDATE(),120) +
')被拥堵的语句数已达'+cast(@nCount as varchar(12))+'条,会导致系统异常缓慢,请即时处理! <br />'+ ' 详情见日志表:MQ_DBLockLog '+
' <br /><br /><br />时间:'+CONVERT(varchar(20),getdate(),121)+' <br />发送人:SQL系统监控(MQ)'
Exec msdb..sp_send_dbmail
@profile_name = 'MQLCK_profile'
,@recipients = 'yidk@haday.cn;huangsl@haday.cn' -- ;chenl@haday.cn;chenrq@haday.cn;huangsl@haday.cn' -- 收件人邮箱 chenl@haday.cn;chenrq@haday.cn
,@subject = @cSubject -- 邮件标题
,@body = @cEmail
,@body_format = 'HTML' -- 邮件格式
Declare @dDate as smalldatetime
Set @dDate = GETDATE()
update MQ_DBLockLog set dEmailXP = @dDate,nEmailXPRec = @nCount where dCrtDate > dateadd(ss,-(@nSec*2),@tDate) And isEmailXP = 0
update MQ_DBLockLog set isEmailXP = 1 where isEmailXP = 0
end
/*
SELECT spid as id
,lastwaittype as 等待类型
,login_time as 登入时间
,last_batch as 最后处理
,status as 状态
,cmd as 命令类型
,loginame as 登录用户
,hostname as 登录终端
,program_name as 执行程序
,*
FROM
[Master].[dbo].[SYSPROCESSES] WHERE loginame <> 'sa'
Order by 状态,SYSPROCESSES.CPU desc
*/
drop table #tmp_lock_who
return 0
end
---===表结构
CREATE TABLE [dbo].[MQ_DBLockLog](
[nID] [int] IDENTITY(1,1) NOT NULL,
[nLOCKID] [int] NOT NULL,
[nSPID] [int] NOT NULL,
[EventType] [varchar](200) NOT NULL,
[EventInfo] [varchar](5000) NOT NULL,
[hostname] [nchar](128) NOT NULL,
[dCrtDate] [datetime] NOT NULL,
[cpu] [int] NOT NULL,
[physical_IO] [int] NOT NULL,
[loginame] [nchar](128) NOT NULL,
[login_time] [datetime] NOT NULL,
[last_batch] [datetime] NOT NULL,
[program_name] [nchar](128) NOT NULL,
[cmd] [nchar](16) NOT NULL,
[Lastwaittype] [nchar](32) NOT NULL,
[Parameters] [varchar](200) NOT NULL,
[isEmailXP] [bit] NOT NULL,
[dEmailXP] [smalldatetime] NOT NULL,
[nEmailXPRec] [int] NOT NULL,
[isEmail] [bit] NOT NULL,
[dEmail] [datetime] NOT NULL,
CONSTRAINT [PK_MQ_DBLockLog] PRIMARY KEY CLUSTERED
(
[nID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Index [IX_MQ_DBLockLog] Script Date: 2020/3/3 16:13:32 ******/
CREATE NONCLUSTERED INDEX [IX_MQ_DBLockLog] ON [dbo].[MQ_DBLockLog]
(
[dCrtDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_MQ_DBLockLog_1] Script Date: 2020/3/3 16:13:32 ******/
CREATE NONCLUSTERED INDEX [IX_MQ_DBLockLog_1] ON [dbo].[MQ_DBLockLog]
(
[isEmail] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_MQ_DBLockLog_2] Script Date: 2020/3/3 16:13:32 ******/
CREATE NONCLUSTERED INDEX [IX_MQ_DBLockLog_2] ON [dbo].[MQ_DBLockLog]
(
[dEmail] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_MQ_DBLockLog_3] Script Date: 2020/3/3 16:13:32 ******/
CREATE NONCLUSTERED INDEX [IX_MQ_DBLockLog_3] ON [dbo].[MQ_DBLockLog]
(
[dEmailXP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_MQ_DBLockLog_4] Script Date: 2020/3/3 16:13:32 ******/
CREATE NONCLUSTERED INDEX [IX_MQ_DBLockLog_4] ON [dbo].[MQ_DBLockLog]
(
[isEmailXP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_MQ_DBLockLog_5] Script Date: 2020/3/3 16:13:32 ******/
CREATE NONCLUSTERED INDEX [IX_MQ_DBLockLog_5] ON [dbo].[MQ_DBLockLog]
(
[nEmailXPRec] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_MQ_DBLockLog_6] Script Date: 2020/3/3 16:13:32 ******/
CREATE NONCLUSTERED INDEX [IX_MQ_DBLockLog_6] ON [dbo].[MQ_DBLockLog]
(
[last_batch] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MQ_DBLockLog] ADD CONSTRAINT [DF_MQ_DBLockLog_dCrtDate] DEFAULT (getdate()) FOR [dCrtDate]
GO
ALTER TABLE [dbo].[MQ_DBLockLog] ADD CONSTRAINT [DF_MQ_DBLockLog_isEmailXP] DEFAULT ((0)) FOR [isEmailXP]
GO
ALTER TABLE [dbo].[MQ_DBLockLog] ADD CONSTRAINT [DF_MQ_DBLockLog_dEmailXP] DEFAULT ((0)) FOR [dEmailXP]
GO
ALTER TABLE [dbo].[MQ_DBLockLog] ADD CONSTRAINT [DF_MQ_DBLockLog_nEmailXPRec] DEFAULT ((0)) FOR [nEmailXPRec]
GO
ALTER TABLE [dbo].[MQ_DBLockLog] ADD CONSTRAINT [DF_MQ_DBLockLog_isEmail] DEFAULT ((0)) FOR [isEmail]
GO
ALTER TABLE [dbo].[MQ_DBLockLog] ADD CONSTRAINT [DF_MQ_DBLockLog_dEmail] DEFAULT ((0)) FOR [dEmail]
GO