2

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
  • Everytime a session is killed it is written to the errorlog, but you could also add it to a history table of your own and then query it for the changes at the end. – Sean Gallardy - Retired User May 7 '19 at 23:08
  • Note that sysprocesses has a s is column but it is not reliable. If I connect to database A and run a query that joins tables between databases B, C, and D, which one do you think will be in sysprocesses? Also what are you going to do with the knowledge of who was killed? Why does your maintenance job need to kill anyone? What is it doing that it requires exclusive access? – Aaron Bertrand May 7 '19 at 23:51
  • There are some locks that are causing the maintenance tasks to run, specifically statistics updates. I'd like to compile statistics on the results of the script. – the dave May 8 '19 at 0:17
  • Statistics are probably updated by the maintenance script, so why are they locked? Normally transaction isolation should mean its possible to read and update the same innodb table at the same time. Any explicit locking is the wrong way to be writing an application. I'd show your code/sql around update and select with table structures to resolve this without cludgy code that kills off user connections. – danblack May 8 '19 at 5:24
  • Which SQL server version you are using and whether its standard or enterprise? Why do you need to terminate user's session while you are running maintenance job. – Learning_DBAdmin May 8 '19 at 10:54
1

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.