Database Recovery Process /Crash recovery

Question

Database Recovery Process /Crash recovery

DicksonMulwa

Old Hand

Points: 365
More actions
April 30, 2015 at 3:56 pm

#317197

What is the logic used by the DB recovery/Crash Recovery process while recovering uncommitted data. Does it simply rollback all changes? Say, for example, if a database crashes before transaction (T1) is "hardened" to the
transaction log file. Will the SQL Server engine recovery process roll forward or roll backwards transaction (T1)?
October 31, 2015 at 12:12 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

DicksonMulwa (4/30/2015)
What is the logic used by the DB recovery/Crash Recovery process while recovering uncommitted data. Does it simply rollback all changes? Say, for example, if a database crashes before transaction (T1) is "hardened" to the
transaction log file. Will the SQL Server engine recovery process roll forward or roll backwards transaction (T1)?

Recommend having a look at Books by Kalen Delaney and for a quick answer there are various articles / blogs like this one FOUR PHASE SQL SERVER DATABASE RECOVERY PROCESS[/url]

😎

Grant Fritchey SSC Guru Points: 398680 More actions · Answer 2

Yes, it will roll forward or rollback the transaction depending on if the transaction is complete within the log. If complete, it rolls it forward. If incomplete it rolls it back.

A second recommendation for Kalen's excellent book. Always have a copy of that available.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

DicksonMulwa (4/30/2015)
Will the SQL Server engine recovery process roll forward or roll backwards transaction (T1)?

If the COMMIT TRANSACTION for T1 is recorded in the log it will be rolled forward. If no COMMIT for that transaction can be found in the log, it'll be rolled back.

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

henri2398 SSC Journeyman Points: 86 More actions · Answer 4

Thanks for this useful information. I have managed to restore my data with the help of this two command: Roll Forward and Rollback