April 16, 2009 at 2:12 pm
OK. I was going through the post here and has a question.
If i run a long insert statement in bathces of 10k records then when i kill the job after running for 7 hrs then does the roll back finish in 10min only as it took 10min to insert 10k records.
April 16, 2009 at 2:32 pm
Depends. Was each of those inserts in it's own transaction or was there a single begin transaction at the beginning and a commit at the end?
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
January 29, 2010 at 12:17 pm
I don't know why people keep saying that the rollback will resume when restarting the server. That's BS:
By definition, an ongoing transaction (which includes a rollback) is not part of the consistent state of the db and will be lost upon restart. The transaction log is only going to be used to get the db up to date with the last completed transactions that were not flushed out to disk yet.
January 29, 2010 at 12:32 pm
rotkehlchen105-kak (1/29/2010)
I don't know why people keep saying that the rollback will resume when restarting the server. That's BS
Nope, it's fact. I can pull error log entries out to show you if you like. It's fairly trivial to prove.
By definition, an ongoing transaction (which includes a rollback) is not part of the consistent state of the db and will be lost upon restart.
Not necessarily. Checkpoint, when it runs, flushes all dirty data pages to disk, regardless of whether or not the transaction that changed them has committed or not. I'm pretty sure that the lazy writer (that writes pages to disk in cases of memory pressure) also doesn't care if the transaction has committed or not.
Hence, if the database is shut down suddenly there are two things that have to occur before the DB can be brought online.
1) Any transactions that had committed but whose changes had not been hardened to disk must be replayed. This is called the redo phase of recovery
2) Any transactions that had not completed but had some of their changes hardened on disk must be ondone. This is called the undo phase of recovery
In the Enterprise edition the database comes online after the redo has completed and locks are held so that the rollbacks can finish. In all other editions of SQL the DB comes online after the redo and undo phases have completed.
There are some details on checkpoint here (my blog) http://sqlinthewild.co.za/index.php/2009/06/26/the-lazy-writer-and-the-checkpoint/
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
January 29, 2010 at 12:38 pm
on a few databases i've seen the recovery process take 2 hours if we restarted SQL during the rollback of a big transaction
January 30, 2010 at 12:18 pm
Hi,
First we cannot KILL a process which is in ROLLBACK state. When a process is in ROLLBACK state it has to complete its work only then it will come back to normal. If we try to restart SQL Server then recovery of the database will take time and during Rollforward-Rollback phase it will again wait for this transaction to be complete.
We can cheeck the Percentage of ROLLBACK done using below command.
KILL 56 WITH STATUSONLY
*where 56 is SPID which is doing ROLLBACK.
Patience is the key in such scenarios.
Regards
GURSETHI
February 2, 2010 at 11:32 am
One word of caution about restarting the SQL service during a rollback. Like several have said, the rollback will continue after the service has restarted. This continuation of the rollback will take place as part of the recovery process. Your database will not be available until the rollback has completed. I learned that the hard way. That rollback took several hours to complete.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 3, 2010 at 2:52 am
GilaMonster (1/29/2010)
rotkehlchen105-kak (1/29/2010)
I don't know why people keep saying that the rollback will resume when restarting the server. That's BSNope, it's fact. I can pull error log entries out to show you if you like. It's fairly trivial to prove.
By definition, an ongoing transaction (which includes a rollback) is not part of the consistent state of the db and will be lost upon restart.
Not necessarily. Checkpoint, when it runs, flushes all dirty data pages to disk, regardless of whether or not the transaction that changed them has committed or not. I'm pretty sure that the lazy writer (that writes pages to disk in cases of memory pressure) also doesn't care if the transaction has committed or not.
Hence, if the database is shut down suddenly there are two things that have to occur before the DB can be brought online.
1) Any transactions that had committed but whose changes had not been hardened to disk must be replayed. This is called the redo phase of recovery
2) Any transactions that had not completed but had some of their changes hardened on disk must be ondone. This is called the undo phase of recovery
In the Enterprise edition the database comes online after the redo has completed and locks are held so that the rollbacks can finish. In all other editions of SQL the DB comes online after the redo and undo phases have completed.
There are some details on checkpoint here (my blog) http://sqlinthewild.co.za/index.php/2009/06/26/the-lazy-writer-and-the-checkpoint/
Presumably, information about committed transactions that have not yet been written to disk is stored in memory.
What happens to this information if the computer is suddenly rebooted and how does that affect the redo phase of recovery?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 3, 2010 at 3:25 am
First the commit is written to T-Log immediately when it is successful. So on recovery from T-log it will updated on the Data file.
Regards,
Raj
February 3, 2010 at 5:15 am
Marios Philippopoulos (2/3/2010)
Presumably, information about committed transactions that have not yet been written to disk is stored in memory.What happens to this information if the computer is suddenly rebooted and how does that affect the redo phase of recovery?
They'll be lost (they were only in memory). The redo phase of recovery redoes those transactions from the log entries. Essentially, the transactions get replayed after restart.
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
February 3, 2010 at 5:30 pm
Very elucidating. Thanks
February 3, 2010 at 6:13 pm
Another comment/question to obi wan:
What if read committed snapshots are enabled across the board?
I would assume that even if uncommitted transaction have been hardened to disk, they don't need to be rolled back because they are just a 'version' of the database tables affected and can simply be dropped from the temp db when recovering. I'm saying this because I know that the consistent-state earlier version of the affected table is visible at all times until the transaction commits - so at startup, the db just has to mark this version as the only one available.
February 4, 2010 at 2:03 am
rotkehlchen105-kak (2/3/2010)
Another comment/question to obi wan:
Obi-wan? If you're referring to me, the avatar is Qui-gon Jinn.
What if read committed snapshots are enabled across the board?
No difference as far as I'm aware
I would assume that even if uncommitted transaction have been hardened to disk, they don't need to be rolled back because they are just a 'version' of the database tables affected and can simply be dropped from the temp db when recovering. I'm saying this because I know that the consistent-state earlier version of the affected table is visible at all times until the transaction commits - so at startup, the db just has to mark this version as the only one available.
No, the transaction still has to be rolled back so that the real table in the real DB is transactionally correct. On startup TempDB is recreated and hence all row versions are lost. There's nothing in there to make as the 'real' version.
Also, bear in mind that what TempDB contains in snapshot isolations are the older versions of the row for transactions/statements that started before a change to the row. The latest is always found in the actual table.
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
February 14, 2010 at 9:53 pm
Alvin Ramard (2/2/2010)
One word of caution about restarting the SQL service during a rollback. Like several have said, the rollback will continue after the service has restarted. This continuation of the rollback will take place as part of the recovery process. Your database will not be available until the rollback has completed. I learned that the hard way. That rollback took several hours to complete.
Lucky for me to benefit from the Forum collective experience.
Have just experienced the never-ending KILLED/ROLLBACK on a 120GB database . . . with estimated "wait" times growing from 500,000 to 2.5 mil to 3.5 million ... seconds? miliseconds?
Luckily, in our ETL setting, I could revert to the last full backup since the imported data can be recovered by a good working ETL, so all I had to do was:
0. Verify last night's full backup worked & DBCC found 0 errors (very important!)
1. Stop the SQL Server Service (no other way to detach or take db offline because of ROLLBACK)
2. Physically delete the MDF, NDF, LDF files from disk before restarting MSSQL
3. Restore last night's backup
4. DBCC CHECKDB etc. to verify the restore worked.
Obviously, anybody who cannot afford the data loss cannot use this scenario, but all I wanted was to regain control of the database as it was yesterday. If you also have good transaction log backups, you might use the scenario to minimise the data loss if the "rollback" will not work.
October 8, 2010 at 7:41 am
You can kill rollback only by doing that but you must have latest backup:
1. stop SQL engine
2. delete db and tlog files
3. start SQL engine
4. delete db
5. restore db
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply