he first question which would come to mind would be “what is killed/rollback command?” The answer is very simple – sometimes, DBA/Developer use “KILL” command to end processes that appear in a hung state. Once that happen, if SQL knows how to rollback that work, it would start “real” rollback process and would undo the work done. There are situations where SQL doesn’t know and has no control of work done of the work done and it has no control to roll back the work. These are the cases where its stays in KILLED/ROLLBACK state forever.
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:
- 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 (query in the next step).
- If the SPID is not showing any change in CPU, Memory and IO column then it is doing nothing. Here 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 as it might be a process of a 3rd party application.
After figuring out the SPID we can run the DBCC INPUTBUFFER (<SPID>) to know what is the batch running under this session.
- Restart SQL Service if it was killed and doing nothing.
No comments:
Post a Comment