script to kill long running ad hoc queries

The following script is meant to identify long running queries and then kill
them after sending a warning. The main problem I have with this is how to
uniquely identify a process so that I don't terminate the wrong one as I
would in the following scenario:
1. Long running ad hoc query with SPID 50 is identified and warning messages
is sent
2. User terminates query
3. Another user connects and is assigned SPID 50. The process goes on to
kill this SPID.
Here is the script. I was planning to run the proc as a job. The time
intervals are for testing only and will probably be increased:
USE AdminDB
GO
--Create the logging table
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'AdHocQueryLog' AND
type = 'U') DROP TABLE AdHocQueryLog
GO
CREATE TABLE AdHocQueryLog
(
AdHocQueryID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
totalruntime smallint NOT NULL,
spid smallint NOT NULL,
cpu int NOT NULL,
last_batch datetime NOT NULL,
physical_io int NOT NULL,
[memusage] int NOT NULL,
open_tran smallint NOT NULL,
loginame nvarchar(128) NOT NULL,
hostname nvarchar(128) NOT NULL,
login_time datetime NOT NULL,
dbid smallint NOT NULL,
program_name nvarchar(128) NOT NULL,
cmd nvarchar(16) NOT NULL,
warned bit NOT NULL DEFAULT 0,
killed bit NOT NULL DEFAULT 0,
stopped bit NOT NULL DEFAULT 0,
insertdate datetime NOT NULL DEFAULT getdate()
)
--Add Indexes later
--Create stored proc
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'uspKillAdHocQuery'
AND type = 'P')
DROP PROCEDURE uspKillAdHocQuery
GO
CREATE PROCEDURE uspKillAdHocQuery
AS
SET NOCOUNT ON
DECLARE @.iReturnCode int,
@.iNextRowId int,
@.iCurrentRowId int,
@.iLoopControl int,
@.spid smallint,
@.hostname nvarchar(128),
@.warned bit,
@.killed bit,
@.warning nvarchar(256),
@.cmd nvarchar(512)
INSERT AdHocQueryLog
(
totalruntime,
spid,
cpu,
last_batch,
physical_io,
[memusage],
open_tran,
loginame,
hostname,
login_time,
dbid,
program_name,
cmd
)
SELECT datediff(ss,last_batch,getdate()),
spid,
cpu,
last_batch,
physical_io,
[memusage],
open_tran,
loginame,
hostname,
login_time,
dbid,
program_name,
cmd
FROM master.dbo.sysprocesses WITH (NOLOCK)
WHERE status='runnable'
AND program_name not like 'SQL Agent%'
AND program_name not in ('%DiagnosticMan','SQL Profiler')
AND datediff(ss,last_batch,getdate())>60
--What if is already it was already there
--Loop through active processes and send warning / kill
--Initialize variables
SELECT @.iLoopControl = 1
SELECT @.iNextRowId = MIN(AdHocQueryID)
FROM AdminDB.dbo.AdHocQueryLog
WHERE killed=0
--Retrieve the first row
SELECT @.hostname=hostname,
@.spid=spid,
@.warned=warned,
@.killed=killed
FROM AdminDB.dbo.AdHocQueryLog
WHERE AdHocQueryID = @.iNextRowId
AND killed=0
--Main processing loop
WHILE @.iLoopControl = 1
BEGIN
IF @.warned=1
BEGIN
PRINT 'KILL: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
convert(char(3),@.warned) +','+ convert(char(1),@.killed)
--populate the cmd variable
--!!!What about if it is still rolling back
--!!!check whether record exists
SELECT @.cmd='KILL '+convert(varchar(3),@.spid)
--Kill the process if it is still active
EXEC sp_executesql @.stmt=@.cmd
--update the log - process has been killed
UPDATE AdminDB.dbo.AdHocQueryLog
SET killed=1
WHERE AdHocQueryID = @.iNextRowId
END
ELSE
BEGIN--send a warning and update log
PRINT 'WARN: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
convert(char(3),@.warned) +','+ convert(char(1),@.killed)
--populate variable for warning message and cmd
SELECT @.warning='Warning: An Ad Hoc query with SPID
'+convert(varchar(3),@.spid)+' has been running at this workstation for over
5
minutes and will be terminated in 5 minutes'
SELECT @.cmd='master.dbo.xp_cmdshell ''NET SEND '+@.hostname+'
'+@.warning+''''
--send warning
EXEC sp_executesql @.stmt=@.cmd
--update the log - warning has been sent
UPDATE AdminDB.dbo.AdHocQueryLog
SET warned=1
WHERE AdHocQueryID = @.iNextRowId
END
-- Reset looping variables
SELECT @.iNextRowId = NULL
-- get the next iRowId
SELECT @.iNextRowId = MIN(AdHocQueryID)
FROM AdminDB.dbo.AdHocQueryLog
WHERE AdHocQueryID < @.iCurrentRowId
AND killed=0
-- check if valid next row id?
IF ISNULL(@.iNextRowId,0) = 0
BEGIN
BREAK
END
-- get the next row.
SELECT @.hostname=hostname,
@.spid=spid,
@.warned=warned,
@.killed=killed
FROM AdminDB.dbo.AdHocQueryLog
WHERE AdHocQueryID = @.iNextRowId
AND killed=0
END
GOYou might want to take a look at the sysprocesses table and specifically the
sql_handle column. But I would concentrate on fixing why the long running
queries occur as a real cure<g>.
Andrew J. Kelly SQL MVP
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:C4620B3D-6EDC-440E-902D-6A5C923F0666@.microsoft.com...
> The following script is meant to identify long running queries and then
kill
> them after sending a warning. The main problem I have with this is how to
> uniquely identify a process so that I don't terminate the wrong one as I
> would in the following scenario:
> 1. Long running ad hoc query with SPID 50 is identified and warning
messages
> is sent
> 2. User terminates query
> 3. Another user connects and is assigned SPID 50. The process goes on to
> kill this SPID.
> Here is the script. I was planning to run the proc as a job. The time
> intervals are for testing only and will probably be increased:
> USE AdminDB
> GO
> --Create the logging table
> IF EXISTS(SELECT name FROM sysobjects WHERE name = N'AdHocQueryLog' AND
> type = 'U') DROP TABLE AdHocQueryLog
> GO
> CREATE TABLE AdHocQueryLog
> (
> AdHocQueryID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> totalruntime smallint NOT NULL,
> spid smallint NOT NULL,
> cpu int NOT NULL,
> last_batch datetime NOT NULL,
> physical_io int NOT NULL,
> [memusage] int NOT NULL,
> open_tran smallint NOT NULL,
> loginame nvarchar(128) NOT NULL,
> hostname nvarchar(128) NOT NULL,
> login_time datetime NOT NULL,
> dbid smallint NOT NULL,
> program_name nvarchar(128) NOT NULL,
> cmd nvarchar(16) NOT NULL,
> warned bit NOT NULL DEFAULT 0,
> killed bit NOT NULL DEFAULT 0,
> stopped bit NOT NULL DEFAULT 0,
> insertdate datetime NOT NULL DEFAULT getdate()
> )
> --Add Indexes later
>
> --Create stored proc
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'uspKillAdHocQuery'
> AND type = 'P')
> DROP PROCEDURE uspKillAdHocQuery
> GO
> CREATE PROCEDURE uspKillAdHocQuery
> AS
> SET NOCOUNT ON
> DECLARE @.iReturnCode int,
> @.iNextRowId int,
> @.iCurrentRowId int,
> @.iLoopControl int,
> @.spid smallint,
> @.hostname nvarchar(128),
> @.warned bit,
> @.killed bit,
> @.warning nvarchar(256),
> @.cmd nvarchar(512)
> INSERT AdHocQueryLog
> (
> totalruntime,
> spid,
> cpu,
> last_batch,
> physical_io,
> [memusage],
> open_tran,
> loginame,
> hostname,
> login_time,
> dbid,
> program_name,
> cmd
> )
> SELECT datediff(ss,last_batch,getdate()),
> spid,
> cpu,
> last_batch,
> physical_io,
> [memusage],
> open_tran,
> loginame,
> hostname,
> login_time,
> dbid,
> program_name,
> cmd
> FROM master.dbo.sysprocesses WITH (NOLOCK)
> WHERE status='runnable'
> AND program_name not like 'SQL Agent%'
> AND program_name not in ('%DiagnosticMan','SQL Profiler')
> AND datediff(ss,last_batch,getdate())>60
> --What if is already it was already there
>
> --Loop through active processes and send warning / kill
> --Initialize variables
> SELECT @.iLoopControl = 1
> SELECT @.iNextRowId = MIN(AdHocQueryID)
> FROM AdminDB.dbo.AdHocQueryLog
> WHERE killed=0
> --Retrieve the first row
> SELECT @.hostname=hostname,
> @.spid=spid,
> @.warned=warned,
> @.killed=killed
> FROM AdminDB.dbo.AdHocQueryLog
> WHERE AdHocQueryID = @.iNextRowId
> AND killed=0
> --Main processing loop
> WHILE @.iLoopControl = 1
> BEGIN
> IF @.warned=1
> BEGIN
> PRINT 'KILL: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
> convert(char(3),@.warned) +','+ convert(char(1),@.killed)
> --populate the cmd variable
> --!!!What about if it is still rolling back
> --!!!check whether record exists
> SELECT @.cmd='KILL '+convert(varchar(3),@.spid)
> --Kill the process if it is still active
> EXEC sp_executesql @.stmt=@.cmd
> --update the log - process has been killed
> UPDATE AdminDB.dbo.AdHocQueryLog
> SET killed=1
> WHERE AdHocQueryID = @.iNextRowId
> END
> ELSE
> BEGIN--send a warning and update log
> PRINT 'WARN: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
> convert(char(3),@.warned) +','+ convert(char(1),@.killed)
> --populate variable for warning message and cmd
> SELECT @.warning='Warning: An Ad Hoc query with SPID
> '+convert(varchar(3),@.spid)+' has been running at this workstation for
over 5
> minutes and will be terminated in 5 minutes'
> SELECT @.cmd='master.dbo.xp_cmdshell ''NET SEND '+@.hostname+'
> '+@.warning+''''
> --send warning
> EXEC sp_executesql @.stmt=@.cmd
> --update the log - warning has been sent
> UPDATE AdminDB.dbo.AdHocQueryLog
> SET warned=1
> WHERE AdHocQueryID = @.iNextRowId
> END
> -- Reset looping variables
> SELECT @.iNextRowId = NULL
> -- get the next iRowId
> SELECT @.iNextRowId = MIN(AdHocQueryID)
> FROM AdminDB.dbo.AdHocQueryLog
> WHERE AdHocQueryID < @.iCurrentRowId
> AND killed=0
> -- check if valid next row id?
> IF ISNULL(@.iNextRowId,0) = 0
> BEGIN
> BREAK
> END
> -- get the next row.
> SELECT @.hostname=hostname,
> @.spid=spid,
> @.warned=warned,
> @.killed=killed
> FROM AdminDB.dbo.AdHocQueryLog
> WHERE AdHocQueryID = @.iNextRowId
> AND killed=0
> END
> GO
>|||Andrew,
I have looked at sql_handles (which is not documented in books online) and
it seems that this doesn't contain unique values either. Can you point me to
a website where I can find out more about this column?
As far as fixing the root problem, I completely agree with you but we have
been required to give some users access to run select statements through ODB
C
connections (used by Access) so I am trying to keep them from shooting
themselves in the foot.
"Andrew J. Kelly" wrote:

> You might want to take a look at the sysprocesses table and specifically t
he
> sql_handle column. But I would concentrate on fixing why the long running
> queries occur as a real cure<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> news:C4620B3D-6EDC-440E-902D-6A5C923F0666@.microsoft.com...
> kill
> messages
> over 5
>
>|||While it has been a while since I messed with that column I was pretty sure
it changed with each batch run by the associated spid. I would do a Google
search and see what turns up. Otherwise, sorry I don't know of any docs for
this.In either case you are probably better off using the "query governor
cost limit option" in SQL Server vs trying to write your own.
Andrew J. Kelly SQL MVP
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:4A8DCA0E-10E7-497E-8FA3-DCFD6B6ABFBE@.microsoft.com...
> Andrew,
> I have looked at sql_handles (which is not documented in books online) and
> it seems that this doesn't contain unique values either. Can you point me
to
> a website where I can find out more about this column?
> As far as fixing the root problem, I completely agree with you but we have
> been required to give some users access to run select statements through
ODBC[vbcol=seagreen]
> connections (used by Access) so I am trying to keep them from shooting
> themselves in the foot.
> "Andrew J. Kelly" wrote:
>
the[vbcol=seagreen]
running[vbcol=seagreen]
then[vbcol=seagreen]
to[vbcol=seagreen]
I[vbcol=seagreen]
to[vbcol=seagreen]
AND[vbcol=seagreen]