Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Transaction Log shrinking

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?

Thanks

djj

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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
Go to Top of Page

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 see
dbcc sqlperf(logspace)
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -