Sql-server – How to view who gets kicked from the SQL Server Script

sql server

I have a maintenance job in SQL that was failing because of an application that was holding a lock for a specific user. I'm now running a script before the step that runs the maintenance job to kick the user. Now the maintenance job runs without issues. I'd like to get output from the step as it runs so I can see in the job activity monitor what gets kicked. How to make the output more verbose?

--Create a cursor
declare mycursor cursor
for

-- Identify any server process id used by your user for the database. Update fields below as required. The username or database name can be removed if you wish to disconnect all users or a user from all databases.
select spid, Loginame, hostname
from master..sysProcesses
where HostName='johnpc' or loginame='browser' and dbid=db_id('approd')

open mycursor

declare @spid int, @loginame varchar(255), @cmd varchar(255), @hostname varchar(255)

-- For each row in the cursor, kill the process by spid
Fetch NEXT FROM MYCursor INTO @spid, @loginame, @hostname
While (@@FETCH_STATUS <> -1)
begin
    select @cmd = 'kill ' + cast(@spid as varchar(5))
    exec(@cmd)
    print @loginame
    Fetch NEXT FROM MYCursor INTO @spid, @loginame, @hostname
end

close mycursor
deallocate mycursor
go

Best Answer

In the Job Step Properties, on thew Advanced page, enable the option to include step output in job history:

enter image description here

Then include a PRINT statement in your cursor loop to output the details of each KILL.

Example:

declare @message varchar(max), @spid int, @loginame varchar(255), @cmd varchar(255), @hostname varchar(255)

-- For each row in the cursor, kill the process by spid
Fetch NEXT FROM MYCursor INTO @spid, @loginame, @hostname
While (@@FETCH_STATUS <> -1)
begin
    select @cmd = 'kill ' + cast(@spid as varchar(5))
    exec(@cmd)

    set @message = 'SPID: ' + CAST(@spid AS VARCHAR(5)) + ' | Login: ' + @loginame + ' | Host: ' + @hostname;

    PRINT @message;

    Fetch NEXT FROM MYCursor INTO @spid, @loginame, @hostname
end
Related Question