tran log backup confused 2.5gb log file but 70gb log backup size

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » tran log backup confused 2.5gb log file but...

62 posts, Page 1 of 7

1 2 3 4 5 »»»

tran log backup confused 2.5gb log file but 70gb log backup size

Rate Topic

Display Mode

Topic Options

Author

Message

lawson2305

Posted Saturday, June 09, 2012 10:55 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 3:40 PM
Points: 46, Visits: 182

Ok I have a question I have a 116gb DB the full backup ends up around 70.5GB in size I then perform a transaction log backup which the log file is 2.5GB in size but the log backup ends up being 71.5GB in size.

This is sql 2005 latest sp.
the db is in bulk logged recovery model.

Post #1313513

Lynn Pettis

Posted Saturday, June 09, 2012 11:03 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 21,832, Visits: 27,856

lawson2305 (6/9/2012)

Since the database is using the bulk logged recovery model, it looks like you may have had minimally logged operations. When you back up the t-log, the actual extends updated are backed up to the t-log backup. This keeps the t-log itself small but the backup can be much larger.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1313516

GilaMonster

Posted Saturday, June 09, 2012 3:17 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 38,082, Visits: 30,376

Bulk-logged recovery is why.
http://www.sqlservercentral.com/articles/Recovery+models/89664/

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1313543

opc.three

Posted Saturday, June 09, 2012 6:21 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

Here is another article for your reference that helps explains why bulk logged is not reducing the size of your tlog backups:

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

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1313551

lawson2305

Posted Monday, June 18, 2012 2:25 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 3:40 PM
Points: 46, Visits: 182

Ok I have changed it to full recovery and the tlog is still running at 71GB. Does this make sense? Do I need to do something to get the tlog backup small like the actual tlog?

I have done a full db and tlog backup after changing to full recovery model.

Post #1317577

Lynn Pettis

Posted Monday, June 18, 2012 2:38 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 21,832, Visits: 27,856

What is the result of the following query.


select name, log_reuse_wait_desc from sys.databases;

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1317588

paul.knibbs

Posted Tuesday, June 19, 2012 1:12 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 1,271, Visits: 4,309

Silly question--you're not backing up both the full and transaction log backup to the same file, are you? A SQL backup file can contain multiple backups.

Post #1317755

lawson2305

Posted Tuesday, June 19, 2012 3:10 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 3:40 PM
Points: 46, Visits: 182

No I am not backing up to the same file.

full to a .bak
log to a .trn

Post #1318337

lawson2305

Posted Tuesday, June 19, 2012 3:12 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 3:40 PM
Points: 46, Visits: 182

master NOTHING
tempdb NOTHING
model LOG_BACKUP
msdb NOTHING
ReportServer NOTHING
ReportServerTempDB NOTHING
ActivplantDB LOG_BACKUP

Post #1318342

DLathrop

Posted Wednesday, June 20, 2012 7:27 PM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 9:55 AM
Points: 84, Visits: 205

If your database is heavily indexed, and you are rebuilding indexes frequently, this could account for the large log backups. This would especially be true if you have a small fill factor on some indexes, and its causing lots of page splits.

If that is the case, read:
[url=http://msdn.microsoft.com/en-US/library/ms189858(v=SQL.90).aspx][/url]

David Lathrop
DBA
WA Dept of Health

Post #1319042

« Prev Topic | Next Topic »

62 posts, Page 1 of 7

1 2 3 4 5 »»»

Permissions