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]

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,
@.killedbit,
@.warningnvarchar(256),
@.cmdnvarchar(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%'
ANDprogram_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
You 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 ODBC
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 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...
> 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]

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
>|||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
> 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
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
> > >
> >
> >
> >sql

Script to identify line count in an procedure

Hi Guys,
Do any one of you have any scripts which return the procedure name and
number of lines for all procedures in a database.
Say I have a server with 1000 stored procedure,I need a script which return:
Procedure name Number of lines
-- --
Thanks in advance
HariI think this should do it:
SELECT O.name, SUM(LEN(text)-LEN(REPLACE(text,CHAR(13),'')))
FROM syscomments AS C
JOIN sysobjects AS O
ON C.id = O.id
WHERE O.xtype='P'
GROUP BY O.id, O.name
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks a lot.
Regards
Hari
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:iMidnTtJieiJzlqiRVn-gQ@.giganews.com...
> I think this should do it:
> SELECT O.name, SUM(LEN(text)-LEN(REPLACE(text,CHAR(13),'')))
> FROM syscomments AS C
> JOIN sysobjects AS O
> ON C.id = O.id
> WHERE O.xtype='P'
> GROUP BY O.id, O.name
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>

Script for SQL Agent "Weighted Job Duration"

Hi,

Does anyone know of a script that will give "weighted job duration"?
I want to use it, to identify which jobs are hogging the CPU. That is
for a given server, list the sql agent jobs ordered by:

(avg job duration in minutes) times (avg num of times job runs in a
given day).On 20 Jul 2004 08:54:48 -0700, Louis wrote:

> Hi,
> Does anyone know of a script that will give "weighted job duration"?
> I want to use it, to identify which jobs are hogging the CPU. That is
> for a given server, list the sql agent jobs ordered by:
> (avg job duration in minutes) times (avg num of times job runs in a
> given day).

Assuming SQL Server 2000 or higher, I came up with this:

Select T3.name, avg(T3.Recurrences) 'AvgRecurrencesPerDay',
sum(T3.[TotalDailyDuration])/sum(T3.Recurrences) 'AvgDuration',
avg(T3.Recurrences) * sum(T3.TotalDailyDuration)/sum(T3.Recurrences)
'Weight'
from (
select T2.name, T1.run_date,
count(*) 'Recurrences',
sum(T1.run_duration) 'TotalDailyDuration'
from msdb.dbo.sysjobhistory T1
inner join msdbo.dbo.sysjobs T2 on T1.job_id = T2.job_id
group by T2.name, T1.run_date
) T3
group by T3.name
order by Weight desc, T3.Name asc|||Thanks Ross. I played with your script and altered it a bit. I decided
what I really want is avg minutes per day.
- Louis

SELECT name as Job,
str(occurrences/@.numDays,10,1) as RunsPerDay,
str(duration/occurrences,10,1) as MinsPerJob,
str(duration/@.numDays,10,1) as MinsPerDay
FROM (
SELECT T2.name,
cast(count(*) as dec) as occurrences,
cast(sum(
(run_duration / 100)/100*60 + (run_duration / 100)%100 --
run_duration is in hhmmss crazy format
) as dec) as duration
FROM msdb.dbo.sysjobhistory T1
INNER JOIN msdb.dbo.sysjobs T2
ON T1.job_id = T2.job_id and step_id=0
and cast(rtrim(T1.run_date) as datetime) between @.startdate and
@.enddate
GROUP BY T2.name
) T
ORDER BY MinsPerDay desc

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

script for comparing tables

Hi,

Can I have a script to compare the two tables. Such that I can identify the missing rows of Table A in Table B, in the absece of any know key.

Quote:

Originally Posted by ckmoied

Hi,

Can I have a script to compare the two tables. Such that I can identify the missing rows of Table A in Table B, in the absece of any know key.

If you did it by hand, how would you determine if a row of Table B does or does not exist in Table A?