PC SOFT

FORUMS PROFESSIONNELS
WINDEVWEBDEV et WINDEV Mobile

Accueil → WEBDEV 2024 → Auto Kill Process in MS SQL Server
Auto Kill Process in MS SQL Server
Débuté par adrianoboller, 24 déc. 2014 15:41 - 9 réponses
Membre enregistré
3 651 messages
Popularité : +175 (223 votes)
Posté le 24 décembre 2014 - 15:41
/******************************************
Script By: Adriano Boller
Purpose: To detect long running sessions,
send complete information through mail about such sessions
and killing session, which are acceding given limit of execution time.
******************************************/
USE MASTER
GO

---BusyProcess Detection
SET NOCOUNT ON

-- Table variable to hold InputBuffer data
DECLARE @Inputbuffer TABLE
(
EventType NVARCHAR(30) NULL,
Parameters INT NULL,
EventInfo NVARCHAR(4000) NULL
)
-- Table variable to hold running processes information
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), -- extra column to hold actual stored procedure or batch call text
EventTime INT -- time in minutes, a process is running
)

-- Insert all running processes information to table variable
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
--Transactions that are open not yet committed or rolledback
WHEN Status = 'SLEEPING' AND open_tran > 0 THEN 1
ELSE 0 END
AND cmd NOT LIKE 'BACKUP%'

-- Cursor to add actuall Procedure or Batch statement for each process
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

-- Create html mail
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%'
--AND EventTime >= 3
GROUP BY SPID, EventInfo, DBName, Command, LastBatch, EventTime
--HAVING MAX(Login) = 'CureMD'
FOR XML raw('tr'), Elements)

-- Replace the entity codes and row numbers
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

-- Send mail to DBA Team
EXEC msdb.dbo.sp_send_dbmail @recipients='adrianoboller@gmail.com,celiogirotron@gmail.com', -- change mail address accordingly
@subject = 'RT - Blocking Session Detected',
@profile_name = 'DBA Team', -- Change profile name accordingly
@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 -- Transactions Running for 3 minutes or more
FOR XML PATH('')
)

EXEC(@QKILLsp)

END
Membre enregistré
3 651 messages
Popularité : +175 (223 votes)
Posté le 24 décembre 2014 - 16:16


Membre enregistré
3 651 messages
Popularité : +175 (223 votes)
Posté le 24 décembre 2014 - 16:16


Membre enregistré
3 651 messages
Popularité : +175 (223 votes)
Posté le 24 décembre 2014 - 16:16


Membre enregistré
3 651 messages
Popularité : +175 (223 votes)
Posté le 24 décembre 2014 - 16:20
Outro exemplo:

/******************************************
Script By: Adriano Boller
Purpose: To detect long running sessions,
send complete information through mail about such sessions
and killing session, which are acceding given limit of execution time.
******************************************/
USE Master
GO

SET NOCOUNT ON

-- 1 - Variable Declaration
DECLARE @DBID int
DECLARE @CMD1 VARCHAR(8000)
DECLARE @spidNumber int
DECLARE @SpidListLoop int
DECLARE @SpidListTable TABLE
(UIDSpidList int IDENTITY (1,1),
SpidNumber int)

-- 2 - Populate @SpidListTable with the spid information
-- Master, Tempdb, Model, MSDB
INSERT INTO @SpidListTable (SpidNumber)
SELECT spid
FROM Master.dbo.sysprocesses
WHERE DBID NOT IN (1,2,3,4) AND spid > 50 AND spid <>@@spid
ORDER BY spid DESC

-- 3b - Determine the highest UIDSpidList to loop through the records
SELECT @SpidListLoop = MAX(UIDSpidList) FROM @SpidListTable

-- 3c - While condition for looping through the spid records
WHILE @SpidListLoop > 0
BEGIN

-- 3d - Capture spids location
SELECT @spidNumber = spidnumber
FROM @spidListTable
WHERE UIDspidList = @SpidListLoop

-- 3e - String together the KILL statement
SELECT @CMD1 = 'KILL ' + CAST(@spidNumber AS VARCHAR(5))

-- 3f - Execute the final string to KILL the spids
-- SELECT @CMD1
EXEC (@CMD1)

-- 3g - Descend through the spid list
SELECT @SpidListLoop = @SpidListLoop - 1
END

SET NOCOUNT OFF
GO
Membre enregistré
3 651 messages
Popularité : +175 (223 votes)
Posté le 24 décembre 2014 - 16:24
:merci:
Membre enregistré
3 651 messages
Popularité : +175 (223 votes)
Posté le 24 décembre 2014 - 16:39
Antes:



Membre enregistré
3 651 messages
Popularité : +175 (223 votes)
Posté le 24 décembre 2014 - 16:40
Depois de executar o job KillProcess:



Membre enregistré
3 651 messages
Popularité : +175 (223 votes)
Membre enregistré
3 651 messages
Popularité : +175 (223 votes)
Posté le 24 décembre 2014 - 16:52
Activity Monitor MS SQL