Posté le 24 décembre 2014 - 15:41 |
USE MASTER GO
SET NOCOUNT ON
DECLARE @Inputbuffer TABLE ( EventType NVARCHAR(30) NULL, Parameters INT NULL, EventInfo NVARCHAR(4000) NULL )
DECLARE @BusyProcess TABLE ( SPID INT, Status VARCHAR(100), Login VARCHAR(100), HostName VARCHAR(100), DBName VARCHAR(100), Command VARCHAR(200), CPUTime INT, DiskIO INT, LastBatch DATETIME, ProgramName VARCHAR(200), EventInfo NVARCHAR(4000), EventTime INT )
INSERT @BusyProcess ( SPID, Status, Login, HostName, DBName, Command, CPUTime, DiskIO, LastBatch, ProgramName )
SELECT spid,status,loginame,hostname,DB_NAME(dbid),cmd,cpu,physical_io,last_batch,program_name FROM SYS.SYSPROCESSES WHERE 1 = CASE WHEN Status IN ( 'RUNNABLE', 'SUSPENDED' ) THEN 1
WHEN Status = 'SLEEPING' AND open_tran > 0 THEN 1 ELSE 0 END AND cmd NOT LIKE 'BACKUP%'
DECLARE cur_BusyProcess Cursor FOR SELECT SPID FROM @BusyProcess
OPEN cur_BusyProcess DECLARE @SPID INT
FETCH NEXT FROM cur_BusyProcess INTO @SPID While ( @@FETCH_STATUS <> -1 ) BEGIN
INSERT @Inputbuffer EXEC ( 'DBCC INPUTBUFFER(' + @SPID + ')' )
UPDATE @BusyProcess SET EventInfo = I.EventInfo, EventTime = DATEDIFF(MI,LastBatch,GETDATE()) FROM @BusyProcess b CROSS JOIN @Inputbuffer i WHERE B.SPID = @SPID
DELETE FROM @Inputbuffer
FETCH NEXT FROM cur_BusyProcess INTO @SPID END CLOSE cur_BusyProcess DEALLOCATE cur_BusyProcess
IF EXISTS(SELECT 1 FROM @BusyProcess I WHERE EventInfo NOT LIKE '---BusyProcess Detection%' AND EventTime >= 3 ) BEGIN Declare @Body VARCHAR(MAX), @TableHead VARCHAR(1000), @TableTail VARCHAR(1000) SET NoCount ON; SET @TableTail = '</table></body></html>'; SET @TableHead = '<html><head>' + '<style>' + 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' + '</style>' + '</head>' + '<body><table cellpadding=0 cellspacing=0 border=0>' + '<tr><td align=center bgcolor=#E6E6FA><b>ROW ID</b></td>' + '<td align=center bgcolor=#E6E6FA><b>SPID</b></td>' + '<td align=center bgcolor=#E6E6FA><b>Event Info</b></td>' + '<td align=center bgcolor=#E6E6FA><b>Login</b></td>'+ '<td align=center bgcolor=#E6E6FA><b>DBName</b></td>'+ '<td align=center bgcolor=#E6E6FA><b>Command</b></td>'+ '<td align=center bgcolor=#E6E6FA><b>CPUTime</b></td>'+ '<td align=center bgcolor=#E6E6FA><b>DiskIO</b></td>'+ '<td align=center bgcolor=#E6E6FA><b>LastBatch</b></td>'+ '<td align=center bgcolor=#E6E6FA><b>EventTime</b></td></tr>';
INSERT INTO NG0004_KillProcess( DataKill, DataLastBatch, SPID, Usuario, Evento, EventTime, DBName, Command, CPU, HDIO ) (SELECT getdate(), I.LastBatch, I.SPID, MAX(I.Login), I.EventInfo, I.EventTime, I.DBName, I.Command, SUM(I.CpuTime), SUM(I.DiskIO) FROM @BusyProcess I WHERE EventInfo NOT LIKE '---BusyProcess Detection%' GROUP BY SPID, EventInfo, DBName, Command, LastBatch, EventTime)
SELECT @Body = (SELECT td= row_number()over(ORDER BY I.SPID ),'', td= I.SPID,'', td= I.EventInfo,'', td= MAX(I.Login),'', td= I.DBName,'', td= I.Command,'', td= SUM(I.CpuTime),'', td= SUM(I.DiskIO),'', td= I.LastBatch,'', td= I.EventTime,'' FROM @BusyProcess I WHERE EventInfo NOT LIKE '---BusyProcess Detection%'
GROUP BY SPID, EventInfo, DBName, Command, LastBatch, EventTime
FOR XML raw('tr'), Elements)
SET @Body = REPLACE(@Body, '_x0020_', SPACE(1)) SET @Body = REPLACE(@Body, '_x003D_', '=') SET @Body = REPLACE(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>') SET @Body = REPLACE(@Body, '<TRRow>0</TRRow>', '')
SELECT @Body = @TableHead + @Body + @TableTail
EXEC msdb.dbo.sp_send_dbmail @recipients='adrianoboller@gmail.com,celiogirotron@gmail.com', @subject = 'RT - Blocking Session Detected', @profile_name = 'DBA Team', @body = @Body, @body_format = 'HTML' ;
DECLARE @QKILLsp VARCHAR(1000)
SET @QKILLsp= (SELECT DISTINCT ' KILL '+ CONVERT(VARCHAR,SPID) FROM @BusyProcess I WHERE EventInfo NOT LIKE '---BusyProcess Detection%' AND EventTime >= 3 FOR XML PATH('') )
EXEC(@QKILLsp)
END
|
| |
| |
|