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