couldn't kill a query as it is in roll back process..but it has been rolling back fro more than a day now

Question

couldn't kill a query as it is in roll back process..but it has been rolling back fro more than a day now

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Doesn't need a reboot. Just a restart of SQL. Stop, start.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eduard SSCommitted Points: 1761 More actions · Answer 2

i'd like to confirm GilaMonster. I have seen several situations where a rollback couldn't get out of the rollback.

A risk you run is that the rollback still needs to be done and the db wont be online as fast as you would like. In 3 out of 4 cases sql server fixed the rollback in seconds. The 4th case was a recovery of 7 hours ... (which is still less then days).

However with a sql server restart (not windows) sql server can purely focus on recovery without serving others as it releases the db after rollback/forward. The process goes faster then.

wannabe1 SSCommitted Points: 1960 More actions · Answer 3

Guys..the sql server restart worked!!

it killed the rollback process and the database was accessible..

just to make sure that I don't run into issues, i took a full backup and verified the data by restoring it. Only after that I went ahead with the sql server restart.

Thank you all for your help and advice!!

Alan-314321 Default port Points: 1439 More actions · Answer 4

It is so awkward you need to restart the SQL server to kill the stuck backup rollback. I had similar problem when my maintenance job (does the backup) got stuck and I tried to kill in and ended in never ending rollback, had to schedule a outage immediately to clear the rollback as next backup doesn't like the fact the previous backup is rolling back. I remember in the SQL 2000 you could just kill sqlmaint.exe to free up the locked process (which make sense as sqlmaint.exe is a independent process from SQL server). Is there really no better way of getting around this?

Regards,

Alan

Jeffrey Williams SSC Guru Points: 90300 More actions · Answer 5

Now that this is fixed, you really need to remove that log truncation/shrink process. It is not necessary and is causing more problems than it would ever resolve.

And, please tell me you are not using BACKUP LOG ... WITH TRUNCATE_ONLY. First, that is a deprecated statement and will not work in SQL Server 2008 and second, it breaks the log chain and will prevent a restore to a point in time from a previously known good backup.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Alan-314321 Default port Points: 1439 More actions · Answer 6

Hi Jeff,

Same as you I am the strong opponent against the log truncation and file shrinking. For my situation it was the external backup program and maintenance cleanup task competing for the lock of the previous day's backup file which drove the maintenance task into hanging.

Alan