June 9, 2012 at 10:55 am
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.
June 9, 2012 at 11:03 am
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.
June 9, 2012 at 3:17 pm
Bulk-logged recovery is why.
http://www.sqlservercentral.com/articles/Recovery+models/89664/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2012 at 6:21 pm
Here is another article for your reference that helps explains why bulk logged is not reducing the size of your tlog backups:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 18, 2012 at 2:25 pm
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.
June 18, 2012 at 2:38 pm
What is the result of the following query.
select name, log_reuse_wait_desc from sys.databases;
June 19, 2012 at 1:12 am
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.
June 19, 2012 at 3:10 pm
No I am not backing up to the same file.
full to a .bak
log to a .trn
June 19, 2012 at 3:12 pm
masterNOTHING
tempdbNOTHING
modelLOG_BACKUP
msdbNOTHING
ReportServerNOTHING
ReportServerTempDBNOTHING
ActivplantDBLOG_BACKUP
June 20, 2012 at 7:27 pm
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:
David Lathrop
DBA
WA Dept of Health
June 27, 2012 at 3:31 pm
my second backup finally fixed this.
Log backups now down to 1.6GB for the week.
July 16, 2012 at 11:40 am
ok I just don't get it. They are back up to 66GB again.
I run a tran backup and shrink every day. I append the backup daily except for Saturday I do a overwrite.
The tran backup is now 66 GB and the ldf file is 66GB with 99% available free space.
July 16, 2012 at 1:10 pm
lawson2305 (7/16/2012)
ok I just don't get it. They are back up to 66GB again.I run a tran backup and shrink every day. I append the backup daily except for Saturday I do a overwrite.
The tran backup is now 66 GB and the ldf file is 66GB with 99% available free space.
Have you read the link supplied to you by Gail Shaw?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 16, 2012 at 1:14 pm
lawson2305 (7/16/2012)
ok I just don't get it. They are back up to 66GB again.I run a tran backup and shrink every day. I append the backup daily except for Saturday I do a overwrite.
The tran backup is now 66 GB and the ldf file is 66GB with 99% available free space.
And what does the following show for the database in question?
select name, log_reuse_wait_desc from sys.databases;
July 16, 2012 at 1:23 pm
lawson2305 (7/16/2012)
ok I just don't get it. They are back up to 66GB again.I run a tran backup and shrink every day. I append the backup daily except for Saturday I do a overwrite.
The tran backup is now 66 GB and the ldf file is 66GB with 99% available free space.
Something is causing your log to bloat. One usual suspect is an index maintenance job. Stop shrinking your tran log every day first of all. As you can see it will simply grow again. The next course of action is to figure out what is causing the log to bloat. Look for index maintenance jobs, large batch processing jobs, rogue apps not closing their transactions, anything that would create a lot of modifications to data or indexes. That can help in troubleshooting. Is your database a publisher in a transactional replication scheme? Also try to narrow down the time frame.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 61 total)
You must be logged in to reply to this topic. Login to reply