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
