Bulk-Logged Recovery Model SQL Server 2005

585 pts.
Tags:
SQL
SQL Server 2005
SQL Server 2005 performance
If I change the recovery model on the database from full to bulk-logged to run DBCC CheckDB and Rebuild Indexes is changing the recovery model the correct thing to do?



Software/Hardware used:
SQL Server 2005

Answer Wiki

Thanks. We'll let you know when a new response is added.

No, you should pretty much never change the recovery model of the database once you have it set to the model that you want to use. Changing it to run DBCC CHECKDB and to rebuild the indexes shouldn’t really do anything. DBCC CHECKDB doesn’t write anything to the log, unless you are running it in a repair mode. Rebuilding indexes are fully logged operations so having it in bulked logged mode shouldn’t affect anything.

You need the log that records the movement of each block of data in the database while the index rebuild is going on so that if you restore the database and logs the index rebuild is replayed correctly.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Rick Martinez
    I forgot to ask one more question when it's a good idea to use bulk-logged recovery model?
    585 pointsBadges:
    report
  • Denny Cherry
    When you are loading a lot of data via the BULK INSERT or BCP commands, then processed from staging to production tables. Usually a data warehouse would be set to BULK LOGGED.
    66,075 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following