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

  • Article

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"

EXPLANATION:
- Backups under BULK_LOGGED recovery model, will include log records and the data pages changed by bulk operations, which will actually make a log backup very large.

Here’s an extract from the Books Online (https://technet.microsoft.com/en-us/library/ms190692(SQL.90).aspx). Note the parts marked in Bold Red below:

Compared to the full recovery model, which fully logs all transactions, the bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions. The bulk-logged recovery model protects against media failure and, for bulk operations, provides the best performance and least log space usage.

Under the bulk-logged recovery model, if a log backup covers any bulk operations, the log backup contains both log records and the data pages that were changed by bulk operations. This is necessary to capture the results of the bulk-logged operations. The incorporated data extents can make a log backup very large. Additionally, backing up the log requires access to the data files that contain the bulk-logged transactions. If any affected database file is inaccessible, the transaction log cannot be backed up and all operations committed in that log are lost.

LET’S BREAK THIS MYTH – With some testing!!

For purpose of demonstration, I will be using AdventureWorks sample database.

--1. Verify the recovery model of the AdventureWorks database

 sp_helpdb 'AdventureWorks' 
Go

AdventureWorks Recovery=FULL

--2. Verify current T-log usage

 dbcc sqlperf(logspace)

Database Name Log Size (MB) Log Space Used (%) Status

------------------ ------------- ------------------ -----------

AdventureWorks 257.9922 1.920442 0

--3. Perform re-indexing on AdventureWorks database

 Use AdventureWorks 
Go 
exec sp_MSforeachtable "DBCC DBREINDEX ('?')" 
Go

--4. Verify T-log usage after Re-Indexing

 dbcc sqlperf(logspace)

Database Name Log Size (MB) Log Space Used (%) Status

----------------- ------------- ------------------ -----------

AdventureWorks 257.9922 41.09579 0

--5. Backup T-Log in FULL recovery model

 BACKUP LOG [AdventureWorks] TO  DISK = N'C:\Backups\LOG_BCKUP_IN_FULL_RECOV.trn' 
GO

--6. Verify the size of T-log Backup in FULL recovery

clip_image002

Size = 102.9 MB

--8. Change the Recovery Model to BULK_LOGGED

 USE [master] 
GO 
ALTER DATABASE [AdventureWorks] SET RECOVERY BULK_LOGGED WITH NO_WAIT 
GO

--9. Verify the recovery model of the AdventureWorks database

 sp_helpdb 'AdventureWorks' 
Go

AdventureWorks Recovery=BULK_LOGGED

--10. Verify current T-log usage

 dbcc sqlperf(logspace)

Database Name Log Size (MB) Log Space Used (%) Status

----------------- ------------- ------------------ -----------

AdventureWorks 257.9922 2.983337 0

--11. Perform re-indexing on AdventureWorks database

 Use AdventureWorks 
Go 
exec sp_MSforeachtable "DBCC DBREINDEX ('?')" 
Go

--12. Verify T-log usage after Re-Indexing

 dbcc sqlperf(logspace)

Database Name Log Size (MB) Log Space Used (%) Status

----------------- ------------- ------------------ -----------

AdventureWorks 257.9922 4.773189 0

--13. Backup T-Log in BULK_LOGGED recovery model

 BACKUP LOG [AdventureWorks] TO  DISK = N'C:\Backups\ADW_REINDX_IN_BULK.trn' 
GO

--14. Verify the size of T-log Backup in BULK_LOGGED

clip_image004

Size = 109.7 MB

CONCLUSION: Myth Busted!! - Bulk operations (like Re-indexing) under BULK_LOGGED recovery model are minimally logged, which will reduce T-log file growth. However this will NOT

reduce the size of consecutive T-log backups[1]. So this approach is no good for scenario in subject.

NEXT STEPS:

- Go test this yourself

REFERENCE READ: Backup Under the Bulk-Logged Recovery Model          
: https://technet.microsoft.com/en-us/library/ms190692(SQL.90).aspx

Overview of the Recovery Models                                    : https://msdn.microsoft.com/en-us/library/ms189275(SQL.90).aspx

[1] A T-log Backup under Bulk Logged recovery model will contain both ‘modified data pages’ and ‘information from T-log files’. As such, there are
2 additional factors that can influence number of data pages being backed up and consequently the overall size of T-log backup in bulk logged recovery model:

Page Fullness – Is derived from Average Bytes free per page and Average Page Density(FULL). The higher the numbers are, the less full the pages. So basis page fullness, we can consume more or less data pages for transactions.

Index Fill Factor – Fill factor is the % of space in an Index Page that is used when an index is created or rebuild. The purpose is to leave behind some free space in the index page to accommodate future insert/update to avoid page splits.

Varun Dhawan
Sr. Support Engineer, Microsoft SQL Server PSS

Reviewed by

Balmukund Lakhani & Shamik Ghosh
Technical Lead, Microsoft SQL Server PSS

Comments

  • Anonymous
    January 10, 2011
    So - what is the best practice here? Switch to Simple?

  • Anonymous
    January 10, 2011
    Hello Sean,
    Your Question:
    So - what is the best practice here? Switch to Simple?

    Our Answer:
    Choosing a recovery model for database depends on your specific requirement.

    So to decide upon the recovery model for a particular database, you should consider both the recovery goals and requirements for the database and whether you can manage log backups In general, The simple recovery model is generally appropriate for a test or development database. However, for a production database, the best choice is typically the full recovery model, optionally, supplemented by the bulk-logged recovery model (if you are using bulk operations).
    As a next steps, I will recommend you to read following SQL BOL Article, which will help you in getting answer to you query
    msdn.microsoft.com/.../ms175987.aspx Thanks for reading.

    HTH
    Varun Dhawan | Senior Engineer, Microsoft SQL Server

  • Anonymous
    January 10, 2011
    Additionally, If your question is specific to log shipping, please note log shipping does not work in SIMPLE recovery model and  changing the recovery model of Primary Database will break log shipping. Ref: msdn.microsoft.com/.../ms187103.aspx The recommended practice is to switch to the bulk-logged recovery model right before a set of bulk operations, then perform the operations, and then immediately switch back to the full recovery model. By doing this you can restrict huge T-log growth on primary database caused by BULK OPERATIONS. HTH Varun Dhawan | Senior Engineer, Microsoft SQL Server

  • Anonymous
    January 12, 2011
    Balmukund, Thanks for a great post and breaking the myth. So is it suggestable to change the DB to bulklogged when i am doing Reindexing of a VLDB and i dont bother ofT-Log backup? One more question is if at all i change the bulk logged to full after the reindexing and take a tlog backup.What would be the behaviour? I'm aware that T-log chain wouldnot break by switching betweem this two models but what about the backups that were taken. Thanks in anticipation. -Aditya Badramraju

  • Anonymous
    January 12, 2011
    Hello Aditya, Thanks for your comment. Now regarding your queries Q1. So is it suggestible to change the DB to bulk logged when I am doing Re-indexing of a VLDB and i don’t bother of T-Log backup? A1. YES, if in case you want to retain database performance and optimize T-log (LDF file) growth, during re-indexing then switching BULK –Logged Model is recommended. Q2. One more question is if at all I change the bulk logged to full after the Re-indexing and take a tlog backup. What would be the behavior? I'm aware that T-log chain would not break by switching between this two models but what about the backups that were taken? A2. Basically, bulk logged model DOES NOT allow Point-In-Time recovery. So to minimize data loss, we recommend below steps: Step 1. Take a T-log backup, before switching to Bulk-logged recovery Step 2. Set Database to Bulk-logged recovery Step 3. Perform  Bulk-logged operations Step 4. Set Database to FULL Step 5. Again, take a T-log backup Doing so, will ensure that there is a minimum data loss, if there is a database failure while you were in ‘Bulk-logged’ model. Reference: technet.microsoft.com/.../ms186229(SQL.90).aspx HTH Varun Dhawan | Senior Engineer, Microsoft SQL Server

  • Anonymous
    January 13, 2011
    Varun, So nice of you for the time . My question is what actually is difference between the log backup size of bulklogged after bulk operation and switching the same to Full and taking the log backup. Does backup recrds the bulk operations even after i switch to Full from Bulk logged... Thanks, Aditya

  • Anonymous
    January 13, 2011
    Hi Aditya Q1. What actually is difference between the log backup size of bulk logged after bulk operation and switching the same to Full and Taking the log backup? A1. When you switch to Bulk-Logged model and perform any bulk operation, Only these bulk operations will be logged minimally. The benefit is improved performance and minimum growth of T-log (.ldf) files. Once you switch back to FULL recovery model, all operations (including Bulk Operations) are logged fully Q2. Does backup records the bulk operations even after i switch to Full from Bulk logged? A2. Under a FULL recovery model, all BULK OPERATIONS are FULLY logged Summary: The sole purpose of the Bulk Logged Recovery Model is to reduce Log Space Consumption by minimally logging some bulk activities, thereby improving the overall performance of the bulk operations. However, this recovery model will result in large log backups, as incorporated data extents can make a log backup very large. HTH Varun Dhawan | Senior Engineer, Microsoft SQL Server

  • Anonymous
    January 14, 2011
    Hi Aditya, To add to Varun's point and to answer your question, I performed a demo to test scenario which you are asking I have a Database Adventureworks in Full Recovery Mode which I switch to Bulk Logged Recovery Model while reindexing the database and switch it back to Full once the Reindexing is over, however I make sure that I take backup of Tlog only after I switch to Full Recovery. So this how I do it ALTER DATABASE Adventureworks SET RECOVERY BULK_LOGGED sp_MSforeachtable 'DBCC DBREINDEX(''?'')' ALTER DATABASE Adventureworks SET RECOVERY FULL BACKUP LOG Adventureworks TO Disk='D:test' with stats=5 And in the Backup message  of the Tlog I see the following Processed 19704 pages for database 'Adventureworks', file 'AdventureWorks_Data' on file 6. 100 percent processed. Processed 707 pages for database 'Adventureworks', file 'AdventureWorks_Log' on file 6. BACKUP LOG successfully processed 20411 pages in 10.130 seconds (15.740 MB/sec). So we observe that even though we switched to Full Recovery model and then took a Tlog backup, the Tlog backup backed up the Data Pages affected from Bulk Operation (reindexing in my case) from the file 'AdventureWorks_Data' which normally doesn't happen in Full Recovery Model. So to conclude, If you switch to Full Recovery from Bulk Logged and if there are bulk operations performed which is minimally logged by Database, the first Tlog backup after we switch to FULL will have a behavior similar to bulk logged and will backup Data Pages alongwith Tlog to maintain the consistency when you perform Recovery Hope this clarifies :)

  • Anonymous
    January 16, 2011
    Hi Aditya, Seems I never got your question right in the first place, my bad. And thanks Parikshit, for replying on this! To summarize your question and our answer (for benefit of all the readers) YOUR QUESTION: What actually is difference between the T-log backup size of Bulk-Logged AFTER bulk operation and switching the same to Full and taking the T-log backup. Does backup records the bulk operations even after I switch to Full from Bulk-Logged? OUR ANSWER: Yes, All 'data pages' changed by BULK OPERATION will be logged in 1st consecutive T-log backup. This behavior will remain same, irrespective that you are still in Bulk-Logged recovery or have switched back to FULL recovery. HTH Varun Dhawan | Senior Engineer, Microsoft SQL Server

  • Anonymous
    January 18, 2011
    Beautiful explanation dude....Not its our turn to do on our production servers and ask you the queries

  • Anonymous
    January 21, 2011
    so varun I had a small question.our architecture is as belown we had a logshipping with standby mode configured on our production server....we had requriment of porting 3 crores of data from one server to logshipping configured server and we are using DTS for that activity and all that data should be reflected on the secondary server because we are using the secondary server for reporting purposes.. In the above case is it advisable to switch from full to bulklogged during the porting time..if so how could be the 3 crores of data reflected on the secondary server if I switch fron full to bulklogged...because at that time the bulk operations are minimally logged and as per my knowledge logshipping works on the transaction log...

  • Anonymous
    January 24, 2011
    Hi Sunil, Thanks for reading and posting your query! Further, I understand your query as below: We had a log-shipping with standby mode configured on our production server....we had requirement of porting 3 cores of data from one server to log-shipping configured server and we are using DTS for that activity and all that data should be reflected on the secondary server because we are using the secondary server for reporting purposes.. Question # 1

  • In the above case is it advisable to switch from full to bulk logged during the porting time? Answer # 1
  • Weather your INSERT’s will be logged minimally under a bulk logged recovery model purely depends upon how are you inserting the rows. If you are using  BCP, Bulk Insert etc then these operations will be logged minimally under Bulk Logged recovery.
  • For a complete list of command/operations that qualifies to be logged minimally refer >> msdn.microsoft.com/.../ms191244.aspx Question # 2 If so how could be the 3 cores of data reflected on the secondary server if I switch from full to bulk logged...because at that time the bulk operations are minimally logged and as per my knowledge log shipping works on the transaction log... Answer # 2
  • “Assuming” that you are using BULK INSERT to insert 3 cores of rows in PRIMARY Server. Under of BULK LOGGED model, these will be logged minimally. Now, during the 1st consecutive T-log backup (after you’ve switched to bulk_logged) will contains 2 things:
  1. Log Records
  2. Data pages that are changed by bulk operation (Bulk Insert …here in this case)
  • When this T-log backup shipped to SECONDRY server and then restored, using the both the above things (Log records and Changed Data Pages), SQL Server will be able to re-construct the complete transaction and you will be able to see all data, as it exists in PRIMARY. Ref >> msdn.microsoft.com/.../ms190692.aspx Hope this answers your query. Regards, Varun Dhawan | Senior Engineer, Microsoft SQL Server

  • Anonymous
    February 09, 2011
    Thank you very much Varun for the clarification and i owe MS for taking care of data what ever the sitauion might be :. Thank you Parikshit for a good explanation Aditya

  • Anonymous
    February 10, 2011
    Thanks varun for your reply..Now It had cleared my doubts in logshipping

  • Anonymous
    November 10, 2011
    Hello Balmukund very interesting article, many thanks; I have just one question, please. Why do you state that if the tlog back up could not be taken, whatever the reason, you will loose all the bulk operations committed in that tlog? How can also this keep data consinstency DR side? I am just wondering if those pages will be marked as changed anyway, and they will be taken out, or remain in the log as changed, as late as a succesful tlog back up will be taken. Could you please help me with this question ? Much appreciated. Thanks again        Marco

  • Anonymous
    December 20, 2011
    Hello Marco Glad that you found the information useful. Now, about your questions, let me first re-iterate this for a better understanding: QUESTION: What If the T-log backups could NOT be taken and the database is in BULK LOGGED recovery model. Now if my DB fails, Do I stand to loose data in such case? ANSWER: Yes, There can a database loss here. Let’s know why… In a BULK LOGGED recovery model, only the BULK_LOGGED transactions are logged minimally. Rest everything (i.e. all normal Trans) are logged fully.  Now to be able to provide complete recovery, the T-Log backup will contains 2 things:

  1. Log Records
  2. Data Pages (Extents actually!) that were modified by Bulk Transaction  This bring some dependency as below: Dependency
  • For a T-Log Backup to be able to record Data Pages, it needs to read the physcial file (.mdf). So, If due to ‘any’ issue, file/s are inaccessible, then the backup will fail.
  • A failed backup = No Backup = No Recovery !!! HTH Varun Dhawan | Database Architect

  • Anonymous
    April 04, 2012
    Good blog.

  • Anonymous
    April 17, 2012
    Thanks Ajay!