Killjob:: Stored Procedure: Process SQL server.

 SET NOCOUNT ON

-- Table variable to hold InputBuffer data

DECLARE @Inputbuffer TABLE

    (

      EventType NVARCHAR(30) NULL,

      Parameters INT NULL,

      EventInfo NVARCHAR(4000) NULL

    )

-- Table variable to hold running processes information

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%' and DB_NAME(dbid)='gHRM'

            

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

-- Create html mail 

IF EXISTS(SELECT 1

FROM  @BusyProcess I

WHERE   EventInfo NOT LIKE '---BusyProcess Detection%'

AND EventTime >= 3

)

BEGIN

Declare @Body varchar(max), @TableHead varchar(1000), @TableTail varchar(1000)

Set NoCount On;

DECLARE @QKILLsp VARCHAR(1000)

SET @QKILLsp= (SELECT DISTINCT '  KILL '+ CONVERT(VARCHAR,SPID)

FROM  @BusyProcess I

WHERE   EventInfo NOT LIKE '---BusyProcess Detection%'

AND EventTime >= 3 -- Transactions Running for 3 minutes or more

for XML path('')

)

EXEC(@QKILLsp) 

END