Good overview for beginners. There are a couple of points that I think should have been included or are actually incorrect.
First, in the Simple Recovery mode section, you say...
This is done to reclaim the log space, and thus keeping the log space consumption to minimum.
It may be a matter of semantics and it may be the goal of the DBA but that's not why SQL Server truncates the log. The system still writes to the log so it can roll back a transaction that goes awry but, once a transaction succeeds or fails and because log backups are not an option in the Simple Recovery mode, there's no longer a need to keep the information in the log, so it gets rid of it.
You also didn't mention that the Simple Recovery mode will also do minimal logging.
In the Bulk Logged section, you say...
You still have an option to restore your database to a point in time until a bulk operation is recorded in the log.
I realize this article wasn't about backups but because backups are so closely tied to the Recovery mode and because newer folks (and even some seasoned DBAs) don't understand what the Bulk Logged Recovery mode actually does, I think some additional information should have been included in the article. The statement above is also a bit confusing.
First, and to be clear, you can, in fact, resume PIT (point-in-time) restores after switching back to Full Recovery. But, you need to be keenly aware those files that were in the Bulk Logged Recovery mode, even for a microsecod, can only be restored in an all or nothing manner even if it was for only a microsecond. Of course, if the PIT you're looking for is during such a file as is an error, you can't restore that Bulk Logged file and you could be missing a whole lot more data than you planned on. And there's no such thing as being able to skip a single log file. Only unbroken log chains can be restored. To that point, it would also have been worth the mention that doing a Differential or Full Backup immediately after returning to Full Recovery is absolutely the right thing to do so that you can actually do a PIT restore within the timeframe that the database was in the Bulk Logged recovery mode.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs