Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jisakson
Starting Member
7 Posts |
 Posted - 2008-08-05 : 13:56:16
|
| We have a 1GB transaction log file. We do a full backup at 7pm every night. Transaction log backups every two hours from 10am to 6pm.I occasionally manually run a maintenance plan after the full backup that sets the recovery mode to bulk logged, then rebuilds indexes, then sets the recovery mode to full. When the plan finishes the used portion of the transaction log is at 30% or 300MB. I have checked and the recovery mode is being changed appropriately. The 10am transaction log backup generates a 17GB .bak file, the size of a normal full backup. The following transaction log backups are the normal 30MB until I run the rebuild index plan. I have also manually run index rebuilds (again changing the recovery mode to bulk logged) on individual files that showed an index fragmentation >80%. The following 10am transaction log backup was 8GB.Anyone know why a 300MB transaction log creates a 17GB transaction log backup file? Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jisakson
Starting Member
7 Posts |
Posted - 2008-08-06 : 13:55:45
|
| Tara, thanks for the quick response. I was looking for more under the hood reasons to the size of the backup. I found it here for thse that are interested.http://www.sqlskills.com/blogs/paul/2008/06/08/SearchEngineQA19MisconceptionsAroundIndexRebuildsAllocationBULKLOGGEDModeLocking.aspxMyth 4: BULK_LOGGED recovery mode decreases the size of the transaction log and log backups for an index rebuildThis myth is partly true.Switching to the BULK_LOGGED recovery mode while doing an index rebuild operation WILL reduce the amount of transaction log generated, which is very useful for limiting the size of the transaction log file (note I say 'file', not 'files' - you only need one log file).Switching to the BULK_LOGGED recovery mode while doing an index rebuild will NOT reduce the size of the transaction log BACKUP. Although the operation will be minimally-logged, the next transaction log backup will read all the transaction log since the last backup plus all the extents that were changed by the minimally-logged index rebuild. This will result in a log backup that's almost exactly the same size as for a fully-logged index rebuild. The ONLY time a log backup will contain data extents is when a minimally-logged operation has taken place since the last log backup - see here on MSDN for more info.If you're considering using the BULK_LOGGED recovery mode, beware that you lose the ability to do point-in-time recovery to ANY point covered by a transaction log backup that contains even a single minimally-logged operation. Make sure that there's nothing else happening in the database that you may need to effectively roll-back with P.I.T. recovery. The operations you should perform if you're going to do this are:In FULL recovery mode, take log backup immediately before switching to BULK_LOGGED Switch to BULK_LOGGED and do the index rebuild Switch back to FULL and immediately take a log backupThis limits the time period in which you can't do P.I.T. recovery. |
 |
|
|
|
|
|
Transaction Log Backup bak File Inordinately Large