sp_who2 active give me back a row with KILLED/ROLLBACK of many week ago

Question

Hi,
during my checks on server... I see a row with KILLED/ROLLBACK of several weeks ago.
I try with KILL 57 but is not active... I think like some ghost record... I don't know.

I checked with DBCC INPUTBUFFER (57) and I got

I think some SSIS call using exec master.dbo.XP_CMDSHELL @CMD
There isn't open transaction around...
so.. How can I delete this row? Restart? No way out?

Thanks Alen

]3

Answer 1

To understand why this happens, we need to know that SQL Server employs co-operative multitasking. That is, every task yields approximately every 4 ms to permit other tasks to run. It is also at these breaks that a process finds out that has been killed. Yielding is nothing that happens automatically, but the yieldpoints are simply programmed into the SQL Server code.

But if a process needs to do work outside SQL Server, for instance an OS call, there is no way it can yield, because that external code does not have any calls to the SQL Server OS - it does not even know what SQL Server is.

So if a process gets stuck outside SQL Server, KILL will not help. What can help is if you can find where the process has actually been hung up, and try to kill the task on that end. One case where this is possible is if you are making a call to a linked server which gets blocked on the remote server. Killing the query on the local server is not going to help - but killing the process on the remote server will.

If this is something called through xp_cmdshell, maybe you can find the process in Windows.

Else, yes, reboot SQL Server.

Answer 2

Hi @Alen Cappelletti ,

Quote from this blog SQL SERVER – SPID is KILLED/ROLLBACK state. What to Do Next?

How would you decide which action to take? It completely depends on the action done by the SPID which was killed. There are only a few things, which you can do:

1.Have patience and wait for rollback to finish. Some large operations may take a long time to rollback. Such SPID should show a change in CPU, Memory and IO column in size. sys.sysprocesses DMV.

2.If the SPID is not showing any change in CPU, Memory and IO column then it is doing nothing. Below is the query which can tell us more about the SPID and from where the connection might have originated.

SELECT spid  
,kpid  
,login_time  
,last_batch  
,status  
,hostname  
,nt_username  
,loginame  
,hostprocess  
,cpu  
,memusage  
,physical_io  
FROM sys.sysprocesses  
WHERE cmd = 'KILLED/ROLLBACK'  

If the process is originated from different machine, then using task manager and doing end process on that machine might help. This decision has to be taken very carefully.

3.Restart SQL Service if it was killed and doing nothing.

Can you reproduce it? The simplest way is to use xp_cmdshell and open notepad.exe using below command. DON’T DO THIS ON PRODUCTION

xp_cmdshell 'notepad.exe'  

Now, it you kill this SPID it would go to KILLED/ROLLBACK state.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".