February 17, 2012 at 5:05 am
Need some advice with regards to Transaction Log size.
Our production database is 150Gb+ in size and the transaction log is currently 86Gb.
We are doing full backup every night and transaction log backups every hour. The transaction log backups are manageable sizes though the main transaction log file grows and never shrinks.
We are in full recovery mode and I guess I'm wondering what to do to manage the size of the transaction log. Any advice would be much appreciated.
February 17, 2012 at 5:18 am
Leave it as it is, otherwise it'll only grow again, wasting system resources and causing fragmentation. If there's been a one-off event that has caused the log to grow and you need to recover some of the space, then by all means shrink the log file, but don't shrink as part of regular maintenance.
John
February 17, 2012 at 5:38 am
Please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
If it's just growing and growing then something is holding the log active, you need to find what it is and resolve it.
Never shrinking is expected. The only things that shrink a log file are autoshrink and the DBCC statements shrinkdatabase and shrinkfile, and those shouldn't be used without good reason.
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply