March 14, 2013 at 3:55 am
The Situation is as follows:
1. server Sql 2008 r2 (10.50.1600)
2. data base Competablitiy Level 2000 (80)
a job was stopped and the process is killed
The transaction is beeing rolled back for serveral days now
The amount of processing made by the job does not justify a rollback of more than an hour
The Table in which the data is beeing rolled back is expendable and can be droped or truncated.
Kill The spid of the rolled back transaction displays this message -:
SPID 173: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.:hehe:
how can I drop the table .:hehe:
March 14, 2013 at 4:58 am
Can you restart the instance? If you can then it might by an idea. It sounds like the job has hung.
Another solution might be to perform a full backup (including the transaction log), put the database recovery model to simple, drop the table and reset the database recovery model back to full. Do a full backup again right afterwards.
Both methods are somewhat extreme but they will do the job.
March 14, 2013 at 5:08 am
Thanks for the advices
1. The Recovery model is simple in the first place
2. Restarting the instance is risky and in this situation the worst result might be suspected database.
the best result is that the rollback will continue.
March 14, 2013 at 5:25 am
Restart the instance, it's a hung rollback. If it's 0 seconds remaining, there won't be a prolonged recovery period.
Recovery models have no effect on rollback, so doesn't matter which one you're in. Restarting with a rollback in progress will not set the DB suspect unless the DB is corrupt and the rollback encounters that corruption, but that would cause suspect status without a restart as well.
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 14, 2013 at 7:37 am
My train of thought is that if the recovery model were changed to SIMPLE, we could in effect "unlock" the table and delete it. It was only a calculated guess but if it worked would prevent the instance being offline during a restart, something that some businesses really can't afford.
I am interested though to see what he actually does and how it works out....
March 14, 2013 at 7:43 am
kevaburg (3/14/2013)
My train of thought is that if the recovery model were changed to SIMPLE, we could in effect "unlock" the table and delete it.
The only two things that change between full and simple recovery model are how much is logged for bulk operations (which a drop table is not) and how long the log records are retained (cleared by a checkpoint vs a log backup). That's it, there's no differences in rollback behaviour, logging for any non-bulk operation, etc.
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 14, 2013 at 9:21 am
GilaMonster (3/14/2013)
Restart the instance, it's a hung rollback. If it's 0 seconds remaining, there won't be a prolonged recovery period.Recovery models have no effect on rollback, so doesn't matter which one you're in. Restarting with a rollback in progress will not set the DB suspect unless the DB is corrupt and the rollback encounters that corruption, but that would cause suspect status without a restart as well.
Very interesting comment.
May I ask this, if you don't mind?
Casually, I was inserting some data via BCP into a table (test environment) The machine is painfully slow because it is a virtual machine and was not properly optimized. Anyway, the bcp was taking for ever, so I killed the transaction. That made things worse, the VM turned more slow and the rollback was taking for ever. So I restarted the SQL instance, because the hanged process or rollback and the database went into suspect mode.
It was a test machine and have a fresh backup, so I restored from it, but it surprised me that the restart damaged the database.
I should say, the hanged process was a batch file with had inside:
-Step to create an empty table using pure T-SQL
-bcp to fill the empty table
-Steo to apply Indexes on the table
By the way, the SQL instance was SQL2012 Dev edition.
March 14, 2013 at 9:31 am
sql-lover (3/14/2013)
It was a test machine and have a fresh backup, so I restored from it, but it surprised me that the restart damaged the database.
The restart didn't damage the database. SQL's not in the habit of corrupting its own databases (unless you encountered a bug, if so, you need to report it to Microsoft).
There are two things that will send a database into the suspect state
- Corruption encountered (log or data file) when rolling back a transaction
- Corruption encountered (log or data file) when running crash recovery on a database at startup.
Additionally, a missing file (log or data) on startup can send a database into the 'recovery pending' state.
What most likely happened is that something in the IO subsystem didn't write correctly (maybe as a side effect of the VM being under load) and when SQL ran crash recovery on the database it encountered something incorrect in the data or log file, that sent the database suspect.
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 14, 2013 at 9:38 am
GilaMonster (3/14/2013)
sql-lover (3/14/2013)
It was a test machine and have a fresh backup, so I restored from it, but it surprised me that the restart damaged the database.What most likely happened is that something in the IO subsystem didn't write correctly (maybe as a side effect of the VM being under load) and when SQL ran crash recovery on the database it encountered something incorrect in the data or log file, that sent the database suspect.
Yeah, that's what I was thinking, it makes sense. As the IO has been really slow since our IT guy delivered the VM to me.
I know the restart did not damage it itself, but the recovery somehow, found a corrupted table, data or something so SQL tried to protect the database and marked as suspect. I tried to recover putting the db in emergency state, run dbcc, etc, just for fun, but was taking too much, was busy, so I restored from fresh backup.
When I stopped the DBCC, I think there was lot of comments about that table and unable to rollback or something; did not save the exact comment.
I was also thinking about a weird SQL2012 bug, but have no evidence about it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply