---- Exec MQTrcTimeOutAutoKillSPID 5,0
Create procedure MQTrcTimeOutAutoKillSPID
(@nMi as int, --超时的最大分钟数
@isCpuMax as int --CPU满负荷的语句是否处理
) as
Begin
Declare @iStartTime as datetime
Declare @iminTime as datetime
Declare @icSQLSTring as varchar(50)
select 'HTSAC' as [DBName]
,spid
,'' as [TextData]
,0 as [Duration]
,last_batch as [StartTime]
,GETDATE() as EndTime
,physical_io as reads
,'' as RowCounts
,CPU
,cast(loginame as nvarChar(18)) as loginame
,Cast([HostName] as nvarchar(16)) as [HostName]
,DATEDIFF(ss,last_batch,GETDATE()) as nTime
,Year(last_batch)*100+MONTH(last_batch) as nYM
,'' as cTrcFile
,[SYSPROCESSES].cmd
into #tmp_ReadTrack
FROM
[Master].[dbo].[SYSPROCESSES] WHERE
status IN('runnable','suspended') And not loginame IN('sa')
And ((Datediff(mi,last_batch,GETDATE())>@nMi ) --最后处理时间大于最大分钟数
Or (CPU >=2147483000 AND @isCpuMax >0 And Datediff(SS,last_batch,GETDATE())>30)) --CPU满负荷的语句超过30秒的
And datepart(hh,last_batch)<=19 --只在工作时间段处理
And datepart(hh,last_batch)>=8
--显示满足条件的语句
Select *
FROM #tmp_ReadTrack
IF Exists(SELECT * FROM #tmp_ReadTrack)
Begin
/* 保存日志
insert into dbo.MQTRCOutTimeKill(
[DBName]
,spid
,[TextData]
,[Duration]
,[StartTime]
,EndTime
,reads
,RowCounts
,CPU
,[LoginName]
,[HostName]
,nTime
,nYM
,cTrcFile
,cMD
)
Select Max([DBName]) as [DBName]
,spid
,Max([TextData]) as [TextData]
,Max([Duration]) as [Duration]
,Max([StartTime]) as [StartTime]
,Max(EndTime) as EndTime
,Max(reads) asreads
,Max(RowCounts) as RowCounts
,Max(CPU) as CPU
,Max(loginame) as loginame
,Max([HostName]) as [HostName]
,Max(nTime) as nTime
,MAX(nYM) AS nYM
,Max(cTrcFile) as cTrcFile
,Max(cmd) as cmd
FROM #tmp_ReadTrack
Group by SPID
*/
-- 放入游标
DECLARE c_TrctimeOutX CURSOR FOR
SELECT SPID,MAX([TextData]) as [TextData],Min([StartTime]) as [StartTime],MAX(CPU) as CPU,Max(cMD) as cMD
FROM #tmp_ReadTrack
Where 1=1 Group BY SPID
DECLARE @SPID as int,@RUNSQL as varchar(max),@StTime as datetime,@NewRunSQL as varchar(Max),@CPU as int,@cMD as varchar(100)
create table #_tmp_MQGetSPidSQLExec(cEventType varchar(100),cParameters int,cSQLInfo varchar(max))
OPEN c_TrctimeOutX
FETCH NEXT FROM c_TrctimeOutX INTO @SPID,@RUNSQL,@StTime,@CPU,@cMD
WHILE (@@FETCH_STATUS = 0)
BEGIN
insert #_tmp_MQGetSPidSQLExec exec MQGetSPidSQLExec @SPID
Select Top 1 @NewRunSQL =cSQLInfo from #_tmp_MQGetSPidSQLExec
IF @NewRunSQL <>''
Begin
IF ((PATindex('%select%',@cMD) >0 ) And datediff(mi,@StTime,GETDATE())>@nMi
OR (PATindex('%exec%',@cMD) >0 ) And datediff(mi,@StTime,GETDATE())>@nMi*2
OR (PATindex('%update%',@cMD) >0 ) And datediff(mi,@StTime,GETDATE())>@nMi*3
OR (@CPU >=2147483000 AND @isCpuMax >0 And datediff(SS,@StTime,GETDATE())> @nMi*30/2 And PATindex('%select%',@cMD) >0 )
OR (@CPU >=2147483000 AND @isCpuMax >0 And datediff(SS,@StTime,GETDATE())>@nMi*30 And PATindex('%exec%',@cMD) >0 )
OR (@CPU >=2147483000 AND @isCpuMax >0 And datediff(MI,@StTime,GETDATE())>@nMi And PATindex('%exec%',@NewRunSQL) >0 )
) --OR PATindex('%exec%',@NewRunSQL) >0
Begin
/* 更新日志的KILL情况
Update MQTRCOutTimeKill Set isKill = 1 ,dKillDate = GETDATE(),[TextData] = @NewRunSQL
,cKill = cast(datediff(ss,@StTime,GETDATE()) as varchar(30))+'秒钟'+' 已执行KILL.' +'RUN: Kill SPID:' + cast(@SPID as varchar(12))
Where SPID = @SPID And [StartTime] = @StTime
*/
set @icSQLSTring = 'Kill ' + cast(@SPID as varchar(12))
Exec (@icSQLSTring)
End
/* 更新日志的KILL情况
Else
Begin
Update MQTRCOutTimeKill Set isKill = 0 ,dKillDate = GETDATE(),[TextData] = @NewRunSQL
,cKill = cast(datediff(ss,@StTime,GETDATE()) as varchar(30))+'秒钟'+' 未执行KILL!!!' + case when datediff(ss,@StTime,GETDATE())>20 then '非select语句('+@cMD+')' else '小于20秒' End
Where SPID = @SPID And [StartTime] = @StTime
Print '====================================='
End
*/
End
truncate table #_tmp_MQGetSPidSQLExec
FETCH NEXT FROM c_TrctimeOutX INTO @SPID,@RUNSQL,@StTime,@CPU,@cMD
End
CLOSE c_TrctimeOutX
DEALLOCATE c_TrctimeOutX
Drop table #_tmp_MQGetSPidSQLExec
End
Drop table #tmp_ReadTrack
Print '执先完成! MQ'
End
=======================
Create proc MQGetSPidSQLExec(@spid int)
WITH ENCRYPTION AS
BEGIN
dbcc inputbuffer (@spid)
End