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

Published On: 2013-05-01By:

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.


Contact the Author | Contact DCAC


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link