March 13, 2012 at 1:34 pm
I had a process that was running for many hours from my SSMS. It was BDCC shrinkfile. I tried to Cancel query from SSMS, but again, it shows "cancelling query" for many hours.
sys.dm_exec_requests shows 80.60408 percent_complete for that session_id and does not move. What is solution ?
Thanks
March 13, 2012 at 1:52 pm
The solution is to wait. It is rolling back everything that was done and may take some time.
Jared
CE - Microsoft
March 13, 2012 at 1:58 pm
This is what I also thought, but hoped that there is some solution. I've been waiting for many hours already. Actually it is cancelling longer than was original execution. Will recycling the server help ? Most of our databases, if not all of them, are in simple recovery model.
March 13, 2012 at 2:15 pm
SQL Guy 1 (3/13/2012)
This is what I also thought, but hoped that there is some solution. I've been waiting for many hours already.
Wait until it's finished, it's not uncommon for rollbacks to take longer than the roll forward. Check that it isn't blocked by anyone.
Will recycling the server help ?
Maybe, or maybe you'll have to continue waiting with the database offline and unavailable to anyone.
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
March 13, 2012 at 2:28 pm
Sometimes, restore this database is faster in my experience.
March 13, 2012 at 3:46 pm
But can we restore a database in at least one connection is not closed ?
March 13, 2012 at 5:22 pm
SQL Guy 1 (3/13/2012)
But can we restore a database in at least one connection is not closed ?
no Restore requires exclusive access to the database. with the canceling of the query it is rolling back the transaction so even ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE wont really work for you as it would need to roll back the transaction your all ready trying to rollback.
How many times can i say rollback in one sentence and still make sense?
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply