| Author |
Topic |
|
djj55
Constraint Violating Yak Guru
352 Posts |
 Posted - 2013-09-26 : 15:34:23
|
| Hello, I would like to shrink a 5GB log file after we do a full backup. However, since we are running log shipping DBCC SHRINKFILE does not work.Is there a way to shrink the log file and not break log shipping?Thanksdjj |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-09-26 : 15:37:59
|
| Log shipping and shrinkfile are compatible. Shrinking the log file does not break log shipping, unless you specify to truncate the log. Truncating the log DOES break log shipping as you are breaking the tlog chain. A regular shrink file is fine though.How often are you running tlog backups through the log shipping plan?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-09-27 : 06:59:03
|
| The transaction log gets backed up between 15 and 60 minutes, depending on the criticality and use of the database. Just had a thought. If I shrink the transaction log the file will remain the same size but the free space changes. Is that correct?djj |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-27 : 08:22:08
|
If I understood you correctly, that is not what shrinking does. It releases free space within the log file (and so the file size will change), but it will not logically change anything in the active portion of the log. How much free space do you have? Run this to seedbcc sqlperf(logspace) |
|
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-09-27 : 09:14:57
|
| The LDF file is 5G, the results of sqlperf says log size 4900.992 MB and Log Space Used 0.8684964 %djj |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-09-27 : 13:09:23
|
| Why do you think you need to shrink the file after you perform backups? The log will just grow again - causing file fragmentation and performance issues. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-09-27 : 13:32:05
|
quote:
Originally posted by djj55
If I shrink the transaction log the file will remain the same size but the free space changes. Is that correct?
No. Shrinking the file affects the file size. Backing up the transaction log (and also truncating it, but don't do this as it impacts log shipping) affects the free space inside the transaction log.A 5GB log file is pretty small. I'm not sure you should bother shrinking it. How big is your data file(s)?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-09-27 : 14:28:29
|
| Thank you everyone. I will leave the file for now. Just had some time and was thinking about the log file size. A while ago it had grown to half the total drive space (100G) and I had to shrink the file and restart log shipping. This is not the only database log file on the drive.But if I can remember this thread I will not try to do something not nessasary.djj |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-09-27 : 14:33:20
|
| If you had to restart log shipping after shrinking the log file, then you specified the truncate option. Do not do that going forward. Run a tlog backup and then a regular dbcc shrinkfile.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Transaction Log shrinking