Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

Starting with SQL Server 2005 and all later versions, Enterprise Edition includes a feature called "Fast Recovery".

a) Does fast recovery use multiple OS threads to perform "REDO" operation?

b) Does SQL Server write to the transaction log in parallel spawning multiple threads or is the log written sequentially so only one thread is used for writing to the transaction log?

share|improve this question

1 Answer

up vote 0 down vote accepted

Understanding Logging and Recovery in SQL Server

Replaying log records is called the REDO (or roll forward) phase of recovery. Reverting log records is called the UNDO (or roll back) phase of recovery. In other words, recovery will make sure that a transaction and all its constituent log records are either redone or undone.

Fast recovery allows a database to come online as soon as the REDO phase completes, before UNDO is run. This is made possible by lock logging, which records the locks that were applied by a transaction in the associated log record.

Fast recovery reads the log records that will be UNDONE, acquires the locks, then brings the database online. The UNDO processing can then proceed in due course while other database activity occurs as the resources that will be affected are protected by locks. See Paul Randal's Lock logging and fast recovery post for the gory details.

REDO operations are single threaded on Standard Edition and Enterprise Edition with less than 5 cores. On Enterprise with 5+ cores, there is a redo thread for every 4 cores (reference). This is separate and unrelated to the Fast Recovery feature as far as I can tell.

In answer to your other question, a single thread is responsible for transaction log writes.

share|improve this answer
Does fast recovery use multiple OS threads to perform "REDO" operation? – SQL Learner Aug 5 '12 at 15:41
@SQLLearner Missing paragraph edited in. – Mark Storey-Smith Aug 5 '12 at 16:23
When I did a google search before posting this question I found in database mirroring in EE it does Parallel Redo however I was not sure whether it applies to also normal "Crash Recovery" not using mirroring, just checking with you are you sure? – SQL Learner Aug 5 '12 at 17:10
Crash recovery is crash recovery, regardless of the context under which it's run. – Mark Storey-Smith Aug 5 '12 at 18:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.