Transaction Rollback Operation is taking longer

Category: sql server dbengine

Question

Vaibhav-Chaudhari on Tue, 09 Feb 2016 13:18:43


Hi All,

I stopped a procedure which was in the middle of a update process and it was in cancelling state. After 30 minutes, I tried to kill the SPID and later on closed the SP running window. Now whenever I try to run the SP, the corresponding SPID seems to be blocked by SPID = 73. When I try to kill spid 73, i get the bellow message-

SPID 73: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

Completion progress has been 0% since 3 hours and also not able truncate this table.

On checking sysprocesses table, the SPID 73 is seen to be in suspended state and the command fired is KILLED/ROLLBACK.

Any suggestions?


Cheers
Vaibhav
MCSA (SQL Server 2012)

Replies

Olaf Helper on Tue, 09 Feb 2016 13:25:51


Hello Vaibhav,

You have to wait until the rollback has finished, there is no way around. Even if you would restart SQL Server (!!don't do it!!), then rollback will continue after restart.


Olaf Helper

[ Blog] [ Xing] [ MVP]


Visakh16 on Tue, 09 Feb 2016 13:32:11


That means it still has some pending things to do for the rollback to complete. You need to wait until its completely done.

Sebastian vd Putten on Tue, 09 Feb 2016 13:35:49


Hi Vaibhav,

Its odd that the process is stuck on 0% for 3 hours. unfortunately, you cán't undo the rollback so it will have to complete one way or the other.

Be sure nothing is blocking or in the way of the execution of SPID 73...some rollbacks require really aggresive locks (Exclusive table-locks).
Other then that, a reason for slow progression can be linked-servers, XP_CMDShell-commands..?

And if everything fails, it 'might' help.. and i know its cursing but restarting the server in single-user mode might allow the rollback to finish faster. Though i would never be quite happy to do it for a production-server!

So if you want us to look or give advice, the query and guestimate about table-sizes will help us to give a more appropriate suggestion.

Sebastian

Uri Dimant on Tue, 09 Feb 2016 13:38:13


In addition to what others have already said , please do NOT restart MS SQL Service....

-Kiron on Tue, 09 Feb 2016 18:32:23


Hi Vaibhav,

As said above , Please wait till transactions rollback has completed,b/w check database recovery phase status in error log about to remaining percentage to completing.

Please go through below url about to database recovery phase details.

http://stackoverflow.com/questions/2391399/three-phases-of-recovery-the-analysis-phase-the-redo-phase-and-finally-the-u

Erland Sommarskog on Tue, 09 Feb 2016 22:52:45


What more exactly was the process doing when it was killed? If it was accessing a linked server and got stuck there, things will never resolve on your local server, because the process will never detect that it has been killed. (Since SQL Server OS employs co-operative multi-tasking, processes needs to check that they have been killed themselves, but they cannot do it, if they are executing outside SQL Server.)

Vaibhav-Chaudhari on Wed, 10 Feb 2016 06:12:56


Hi All,

Thank you for comments.

The SP was running in development server and we have full access to it. The SP was performing UPDATE operation and it doesn't contain linked server or CMDSHELL commands. The table size is ~15GB.

Below is the result of

SELECT * FROM sys.sysprocesses P inner join sys.dm_exec_requests E on E.session_id = P.spid

WHERE p.cmd like '%ROLLBACK%'

Uri Dimant on Wed, 10 Feb 2016 06:46:21


what  does percent_complete column return?

select  start_time,
 percent_complete ,estimated_completion_time 
 from sys.dm_exec_requests 

where session_id=73 


Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence


Vaibhav-Chaudhari on Wed, 10 Feb 2016 06:53:02


what  does percent_complete column return?

select  start_time,
 percent_complete ,estimated_completion_time 
 from sys.dm_exec_requests 

where session_id=73 


Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence


Our teammate is planning to restart SQL services and see if it frees up the buffer/log space. (DEV server it is)

Below is the result:


Cheers
Vaibhav
MCSA (SQL Server 2012)


Uri Dimant on Wed, 10 Feb 2016 06:55:19


It may or may not leave your database in recovering mode.... I would still wait till rollback is completed...

Erland Sommarskog on Wed, 10 Feb 2016 08:27:02


What values do you see in sys.dm_exec_requests for wait_type, wait_time and last_wait_type?

Vaibhav-Chaudhari on Wed, 10 Feb 2016 09:05:05


What values do you see in sys.dm_exec_requests for wait_type, wait_time and last_wait_type?


SQL Service is restarted and session id 73 doesn't exist now. DB has gone into recovery state with progress 15%.

Vaibhav-Chaudhari on Fri, 12 Feb 2016 07:15:19


Since this was DEV server, we restarted SQL service and later got the DB in Recovery state which was completed in ~12 hours. We ended up doing this as Transaction Rollback was showing any progress at all. Meanwhile we used another DEV server for our work.

As mentioned by others, Services should not be restarted ideally (Specifically in PROD)

Thank you all.

Olaf Helper on Fri, 12 Feb 2016 08:38:19


Recovery state which was completed in ~12 hours. 


One of the many reasons why I always run updates/deletes in smaller chunks, if an issue occurs the rollback finish in a shorter period.

Doyel on Mon, 17 Apr 2017 10:20:18


Hi,

I am facing similar issue. Yes rollback will take time to finish and there is no other way around.But what I observed is the VLF count is around 8000, Does it has any impact?