Gets a list of long running processes

busy_processes.sql

/* 
select * from sys.sysprocesses
where hostname <> ''
order by hostname asc
*/

-- Table variable to hold InputBuffer data
declare @Inputbuffer table
  (
    EventType nvarchar(30) NULL,
    Parameters int NULL,
    EventInfo nvarchar(4000) NULL
  )

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

select * from @BusyProcess
where EventTime > 4