question

Wilfred van Dijk avatar image
Wilfred van Dijk asked

Roll forward/back and SIMPLE recovery

Is this correct: If I have a database in SIMPLE recovery mode, can I assume the ROLL forward/back is shorter, because MSSQL is only checking the uncommited transactions? All other (committed) logrecords are unusable, because these actually don't exist in the logfile (as a result of the SIMPLE recovery mode) And, if I have FULL recovery: Is MSSQL checking the whole logfile in case of a FULL recoverymode? thus making the roll-forward/-back longer?
rollbackrecovery-modelogfile
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
I assume we're talking about a RESTORE operation. And the short answer is, no, SIMPLE won't speed things up at all. When a backup starts, it gets a marker from the transaction log so that it knows what all the uncommitted transactions are. Then it runs. Then it gets all the transactions that committed or rolled back during it's operation. That is the only information that is used for recovery during the restore operation. It doesn't matter if the database is in FULL or SIMPLE, the backup and restore processes are the same.
5 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Same situation. Transactions that are committed and written to the log are not a part of recovery.
3 Likes 3 ·
KenJ avatar image KenJ commented ·
In a system with concurrent transactions you will see both committed and uncommitted transactions in the transaction log file (whether or not they lead to rollback). Here is a quick explanation that is specific to mirroring but also applies to non-mirrored databases - http://blogs.msdn.com/b/psssql/archive/2013/04/22/how-it-works-always-on-when-is-my-secondary-failover-ready.aspx
2 Likes 2 ·
Wilfred van Dijk avatar image Wilfred van Dijk commented ·
Actually, I mean after a restart of MSSQL
0 Likes 0 ·
Wilfred van Dijk avatar image Wilfred van Dijk commented ·
are only committed transactions in the logfile, or also transactions which lead to a rollback ?
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Sorry, I was on the road. yeah, what @KenJ said.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.