Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

tran log backup confused 2.5gb log file but 70gb log backup size Expand / Collapse
Author
Message
Posted Saturday, June 9, 2012 10:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 61, Visits: 253
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
Posted Saturday, June 9, 2012 11:03 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 23,224, Visits: 31,909
lawson2305 (6/9/2012)
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.


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.



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
Posted Saturday, June 9, 2012 3:17 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 42,764, Visits: 35,862
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
Posted Saturday, June 9, 2012 6:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 PM
Points: 7,097, Visits: 12,598
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
Post #1313551
Posted Monday, June 18, 2012 2:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 61, Visits: 253
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
Posted Monday, June 18, 2012 2:38 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 23,224, Visits: 31,909
What is the result of the following query.


select name, log_reuse_wait_desc from sys.databases;





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 #1317588
Posted Tuesday, June 19, 2012 1:12 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:40 AM
Points: 1,628, Visits: 5,565
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
Posted Tuesday, June 19, 2012 3:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 61, Visits: 253
No I am not backing up to the same file.

full to a .bak
log to a .trn
Post #1318337
Posted Tuesday, June 19, 2012 3:12 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 61, Visits: 253
master NOTHING
tempdb NOTHING
model LOG_BACKUP
msdb NOTHING
ReportServer NOTHING
ReportServerTempDB NOTHING
ActivplantDB LOG_BACKUP
Post #1318342
Posted Wednesday, June 20, 2012 7:27 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 7, 2014 6:15 PM
Points: 86, Visits: 216
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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse