Using Bulk Logged recovery model for Bulk Operations will Reduce the size of Transaction log backups – Myths & Truths

 

It's has been couple of years for me working on SQL Server and I keep coming across various SQL Server misconceptions (or say a DBA Myth). In this specific post I’ll demystify a common DBA myth around log-shipping: "In a log shipping setup, to have smaller size T-log log backups being shipped to secondary, switch to "BULK_LOGGED" recovery model before performing any bulk operation"

SCENERIO:
- Customer have configured Log Shipping for a high OLTP database
- On Primary Server, to optimize the database performance, a daily RE-INDEXING job is scheduled
- Everyday, after the RE-INDEXING job is executed, T-log grows huge and size consecutive T-Log backup is large. It takes lot of time (Of course the Network Resources!!) to ship T-log backups to secondary server
- So, to resolve this issue, DBA came with below plan:

1. Change the recovery model of database to BULK_LOGGED
2. Perform RE-INDEXING (which qualifies to be minimally logged operation)
3. Change the recovery model back to FULL

- Customer believes, following these steps will reduce the size of T-log and thereby minimize network resource usage. This is "THE MYTH"

Read the complete latest POST on SQLServerFAQ and demystify this one

About SQLServerFAQ

SQLServerFAQ blog contains a plethora of information spread across various aspects of troubleshooting commonly encountered issues with SQL server. The posts are contributed by SQL PSS (Product Support) Engineering community.