SQL Server with Mr. Denny

May 1 2013   2:00PM GMT

I’m using simple recovery, so the log file isn’t important right?



Posted by: Denny Cherry
Tags:
Bulk Logged Recovery Model
Database Administration
Full Recovery Model
Manageability
Recovery Model
Simple Recovery Model
SQL Server
Storage
Transaction Log

One of the big myths of SQL Server resolves around the transaction log, and how it’s used with the simple recovery model.

Many people think that the simple recovery model doesn’t use the transaction log.  More specifically they think that it’s there because it has to be there, but that SQL Server doesn’t actually use it.  The reality is that SQL Server still uses the transaction log, much like it does in full or bulk logged recovery modes.  There are some transactions which are going to be minimally logged, but for the most part the INSERT, UPDATE and DELETE commands are going to be fully logged just like normal.

What SQL Server does with the transaction log in simple recovery model is that when the transactions are committed they are written to the transaction log and the pages are dirtied in the buffer pool.  When checkpoint runs the dirty pages in the buffer pool are written to the disk.  Everything up to this point is basically the same as with the other recovery models.  Once the checkpoint has been completed things get different between simple recovery and the other two recovery models.  With the simple recovery model the virtual log files which were just checkpointed and had their dirty pages written to disk will be marked as no longer in use (status=0).  With bulk logged and full recovery this doesn’t happen until the transaction log backup has been completed.

Hopefully this helps dispel the myth.

Denny

 Comment on this Post

 
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 other members comment.

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

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: