SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Transaction Log shrinking
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 09/26/2013 :  15:34:23  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 09/26/2013 :  15:37:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
337 Posts

Posted - 09/27/2013 :  06:59:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3719 Posts

Posted - 09/27/2013 :  08:22:08  Show Profile  Reply with Quote
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

USA
337 Posts

Posted - 09/27/2013 :  09:14:57  Show Profile  Reply with Quote
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

USA
799 Posts

Posted - 09/27/2013 :  13:09:23  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 09/27/2013 :  13:32:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
337 Posts

Posted - 09/27/2013 :  14:28:29  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 09/27/2013 :  14:33:20  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000