January 23, 2014 at 1:13 am
Hi,
I have a Database called MyDatabase1 that has is 307GB of size running on SQL 2008 R2,
I have condigured logshipping for that database.
The problem i have is that the Transaction log backup file grows too large to aboout 73Gb and i need to copy it across the network to the secondary server for restores.
The Backup interval is 15mins.
Can someone help me on how to reduce the log file growth so that it is kept at minimum. What must i try?
Thanks in advance
January 23, 2014 at 1:25 am
THE-FHA (1/23/2014)
Can someone help me on how to reduce the log file growth so that it is kept at minimum.
Minimize log file growth? Do less stuff on your database. (you could switch to the bulk-logged recovery model and make use of minimally logged operations)
Or enable backup compression to reduce the size of your transaction log backup.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 23, 2014 at 1:40 am
Hi,
Do you mean i should restrict log growth to 1MB for instance?
Cause currently the database is set to unrestriced growth of 5MB.
January 23, 2014 at 1:52 am
THE-FHA (1/23/2014)
Hi,Do you mean i should restrict log growth to 1MB for instance?
Cause currently the database is set to unrestriced growth of 5MB.
Noooooooooo.
You have two settings: the incremental steps at which a log file can grow if auto-growth, in either % or MB.
The other setting is the maximum limit at which the log file is allowed to grow.
The incremental size should be big enough so that the log file doesn't need to grow all the time. It shouldn't be too large though, otherwise you are wasting space.
If you set a limit for your log file growth (and 1MB is certainly way too small), and this limit is reached, transactions will stop and your database will come to a grinding halt. You can set a limit, but make sure it is high enough to incorporate normal transaction log growth until the next transaction log backup.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 23, 2014 at 2:49 am
If the log backups are too large, either reduce the amount of changes happening in the DB (usually not practical) or take log backups more often.
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
January 23, 2014 at 2:51 am
Hi,
Could index fragmentation have also have an impact on this problem?
If there are index fragmentation perc of above 40% with majority sitting at 95-97%.
Can index rebuild help with the trn file size to be reduced?
Backup with compression for trn is only available on SQL2012 and this is a SQL 2008 R2 log shipping solution.
January 23, 2014 at 2:57 am
Can index rebuild help with the trn file size to be reduced?
No; an index rebuild will cause your transaction log to grow, so your backups will probably get bigger.
As Gail says, can you back up your log more frequently?
January 23, 2014 at 3:09 am
THE-FHA (1/23/2014)
Backup with compression for trn is only available on SQL2012 and this is a SQL 2008 R2 log shipping solution.
SQL 2008R2 supports backup compression:
Log Shipping Transaction Log Backup Settings
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 23, 2014 at 3:09 am
Ok Thanks a million, i shall let you know of the outcome.
January 23, 2014 at 3:18 am
You should take transaction log backups more often, once you have preformed a transaction log backup you can then execute a shink command on your .ldf file.
--------------------------------------------
Laughing in the face of contention...
January 23, 2014 at 3:33 am
THE-FHA (1/23/2014)
Hi,Could index fragmentation have also have an impact on this problem?
No. The size of the log backups is a function of the changes to the database over the period which that log backup contains
Can index rebuild help with the trn file size to be reduced?
Opposite. Index rebuilds are data changes, therefore doing more data changes will result in your log backups being larger, not smaller.
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
January 23, 2014 at 3:44 am
arrjay (1/23/2014)
You should take transaction log backups more often, once you have preformed a transaction log backup you can then execute a shink command on your .ldf file.
Why would you want to do that?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply