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

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

Rate This
  • Comments 18

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 (http://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          
: http://technet.microsoft.com/en-us/library/ms190692(SQL.90).aspx
Overview of the Recovery Models                                    : http://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

Leave a Comment
  • Please add 5 and 4 and type the answer here:
  • Post
  • So - what is the best practice here? Switch to Simple?

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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 :)

  • 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

  • Beautiful explanation dude....Not its our turn to do on our production servers and ask you the queries

  • 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...

  • 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

  • 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

  • Thanks varun for your reply..Now It had cleared my doubts in logshipping

  • 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

Page 1 of 2 (18 items) 12