Exclude login from automated Kill SQL job

Question

Post reply

Exclude login from automated Kill SQL job

OSU SQL

SSC Journeyman

Points: 87
More actions
May 20, 2015 at 11:39 am

#304125

I have this job that runs and Kills blocking sessions that are longer than 10 mins. (Which I understand is a bad idea) I need to add a list of login's that are exclulded from this Job. I cannot seem to get it to exclude specific users in this script. Below is the script.
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>ROW ID</td>' +
'<td align=center bgcolor=#E6E6FA>SPID</td>' +
'<td align=center bgcolor=#E6E6FA>Event Info</td>' +
'<td align=center bgcolor=#E6E6FA>Login</td>'+
'<td align=center bgcolor=#E6E6FA>DBName</td>'+
'<td align=center bgcolor=#E6E6FA>Command</td>'+
'<td align=center bgcolor=#E6E6FA>CPUTime</td>'+
'<td align=center bgcolor=#E6E6FA>DiskIO</td>'+
'<td align=center bgcolor=#E6E6FA>LastBatch</td>'+
'<td align=center bgcolor=#E6E6FA>EventTime</td></tr>';
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
)
-- INSERT
-- 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='EMAIL', -- change mail address accordingly
@subject = 'Blocking Session Detected',
@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 >= 10
for XML path('')
)
EXEC(@QKILLsp)
END

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323755 More actions · Answer 1

isn't it just checkign the [Login] column in your table variable?

SET @QKILLsp= (SELECT DISTINCT ' KILL '+ CONVERT(VARCHAR,SPID)

FROM @BusyProcess I

WHERE EventInfo NOT LIKE ' ---BusyProcess Detection%'

AND [Login] NOT IN('sa','lowell','mydomain\lowell')

AND EventTime >= 10

for XML path('')

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

OSU SQL SSC Journeyman Points: 87 More actions · Answer 2

Is the EXEC(@QKILLsp) killing the identified processes?

Eirikur Eiriksson SSC Guru Points: 182951 More actions · Answer 3

AND cmd NOT LIKE 'BACKUP%'

Guess you should add DBCC

😎

OSU SQL SSC Journeyman Points: 87 More actions · Answer 4

OSU SQL

SSC Journeyman

Points: 87

May 20, 2015 at 12:35 pm

#1799434

Good call on adding dbcc