Bulk-logged mode makes Tlog backups larger

Question

Bulk-logged mode makes Tlog backups larger

SQLEngine

SSCommitted

Points: 1633
More actions
November 2, 2009 at 6:47 am

#211759

I am thinking of switching to Bulk-logged during INdex defragging, because of minimal logging
But hear that Bulk-logged mode makes Tlog backups larger. I have some guesses, but not sure I completely understand why, can someone verify or explain this?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

What you suspect is true.

To be able to restore your database to a point in time, you need to know exactly what changes have been made since your last full backup. Those changes are written to the transaction log, which you then back up from time to time, and use in the case of a restore.

With the database in BULK_LOGGED, the actual bulk activity is quicker, because only details of page allocations are written to the transaction log (not full details of the changes). It's not until you back up the transaction log up that details of the actual data changes are written out.

When you are in BULK_LOGGED mode, until you back up the transaction log, you are at risk of losing any changes made to the database. In FULL mode, those changes are always in the transaction log.

Your transaction log backups aren't going to be any bigger than they would be in FULL recovery mode. All you are saving is writing full changes to the transaction log.

SQLEngine SSCommitted Points: 1633 More actions · Answer 2

Thanks Ian. So based on your reply I understand that in Bulk-Logged mode the Tlog backup will be slower, because it is during this backup that the full detail changes are written to the log. Is this true?

"It's not until you back up the transaction log up that details of the actual data changes are written out."

If so, it's a trade of - keep it in Full mode and have slower index defrag because of full logging, or put it in Bulk Logged and have slower backups because of detail writes to log during backup.;-)

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

Thanks Ian. So based on your reply I understand that in Bulk-Logged mode the Tlog backup will be slower, because it is during this backup that the full detail changes are written to the log. Is this true?

Almost... In BULK_LOGGED, the actual changes are only written to the transaction log backup, not the log itself. In FULL, the actual changes are written to the log itself and then the log backup when it is run.

So, the transaction log backups will be the same size, but you are right, the backups will be slightly slower, because the actual changes have to be read from the database, rather than directly from the transaction log.

eric.lyons SSC Eights! Points: 860 More actions · Answer 4

SQLEngine (11/2/2009)
Thanks Ian. So based on your reply I understand that in Bulk-Logged mode the Tlog backup will be slower, because it is during this backup that the full detail changes are written to the log. Is this true?
"It's not until you back up the transaction log up that details of the actual data changes are written out."
If so, it's a trade of - keep it in Full mode and have slower index defrag because of full logging, or put it in Bulk Logged and have slower backups because of detail writes to log during backup.;-)

In watching the MCM video on recovery scenarios, it was pointed out that Bulk Logging meant that a tail log backup could not be taken. That surprised me a litte.:w00t:

*****

Give a man a fish, and you've given him a meal. Teach a man to fish and he'll ask to borrow your boat.:-P

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 5

It’s old thread. Please start a new thread for your question / doubt.

Quick Help:

http://msdn.microsoft.com/en-us/library/ms190692.aspx

http://msdn.microsoft.com/en-us/library/ms179314.aspx