• 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)