I use this following code to find any long running spids and kill them off. you could easily tweak it to email you about the spids rather than kill them. I do need to update it to use the DMV's for 2005 servers but have not had time, and it uses a cursor when its not neccesarily needed, but use what you want, and enhance what you want
/* =======================================================================================
CHANGE HISTORY
VersionDateAuthorChange Detail
1.119/12/07JMBecause new connections from Crystal opened with a last batch of 1900-01-01 00:00
I am taking these out so we dont kill them
========================================================================================*/
use tempdb
set nocount on
create table #sp_who (
spidsmallint,
ecidsmallint,
statusnchar(30),
loginamenchar(128),
hostnamenchar(128),
blkchar(5),
dbnamenchar(128),
cmdnchar(16))
insert into #sp_who execute sp_who
--select * from #sp_who where cmd = 'AWAITING COMMAND' and spid > 50
--drop table #sp_who
create table [tempdb].[dbo].[logged_in_users]
(
spid smallint,
login_name [varchar] (50),
DBName [varchar] (50),
LoginTime [datetime],
Lastbatch [datetime]
)
--drop table [tempdb].[dbo].[logged_in_users]
INSERT INTO [tempdb].[dbo].[logged_in_users]
(
[spid],
[Login_Name],
[DBName],
[LoginTime],
[Lastbatch])
SELECT master.dbo.sysprocesses.spid, master.dbo.sysprocesses.loginame,
master.dbo.sysdatabases.name,
master.dbo.sysprocesses.login_time,
master.dbo.sysprocesses.last_batch
FROM master.dbo.sysprocesses INNER JOIN
master.dbo.sysdatabases ON master.dbo.sysprocesses.dbid = master.dbo.sysdatabases.dbid
where master.dbo.sysprocesses.spid > 50
--select * from [tempdb].[dbo].[logged_in_users]
/*Leave this bit in for testing!
select * from [tempdb].[dbo].[logged_in_users] LI inner join #sp_who SP on SP.Spid = LI.Spid
where SP.cmd <> 'AWAITING COMMAND' and SP.spid > 50 and LI.DBName <> 'CE10'
--Then decide how long you is too long!
and lastbatch < dateadd(hh,-0,getdate())
order by lastbatch
*/
create table #Loop
(
id smallint identity(1,1),
spid smallint
)
insert into #Loop (spid)
select LI.spid from [tempdb].[dbo].[logged_in_users] LI inner join #sp_who SP on SP.Spid = LI.Spid
where SP.cmd <> 'AWAITING COMMAND' and SP.spid > 50 and LI.DBName <> 'CE10'
--Then decide how long you think is too long!
and lastbatch (select @@SPID)
--v1.1
and lastbatch > '02 jan 1900'
--v1.1End
select * From #Loop
declare @counter smallint
declare @max-2 smallint
declare @kill smallint
set @counter = 1
set @max-2 = (select max(id) from #Loop)
while @counter <= @max-2
begin
set @kill = (Select spid from #Loop where id = @counter)
print @kill
EXEC ('KILL '+ @kill)
set @counter = @counter + 1
end
--drop tables
drop table [tempdb].[dbo].[logged_in_users]
drop table #sp_who
drop table #Loop
--sp_who2 180
--dbcc inputbuffer (180)