|
R
Constraint Violating Yak Guru
United Kingdom
328 Posts |
 Posted - 07/04/2012 : 11:25:57
|
I normally use the following process within SSMS 2005 to reduce the file size of the transaction log. It seems to be the only process that works:
1) Full db backup (Tasks > Back Up > Full)
2) Reduce transaction log size (Tasks > Shrink Files > Log > Reorganize pages before releasing unused space [50MB]
3) Transaction log backup (Tasks > Back Up > Transaction Log)
4) Repeat step 2
Eventually I'd like to add an index rebuild before step 1, and combine the whole thing into an automated maintenence plan. Can anyone advise on the suitability of doing this (recommended or not?), and/or offer any tips on the process I currently use above?
Thanks. |
|
|
robvolk
Most Valuable Yak
USA
15567 Posts |
Posted - 07/04/2012 : 12:11:04
|
Shrinking files is never recommended as regular maintenance, and should only be done in circumstances where disk space is critical (and if disk space is critical, more and/or larger disks should be installed).
The best option is to determine how much log space is needed to accommodate your index maintenance, and size your transaction log accordingly. Leave some room for other transactions and make sure the autogrowth increment is not too small or too large. In addition you should be making frequent and regular transaction log backups, this should limit the file growth.
In your current sequence, step 2 is somewhat pointless since it occurs before the log is backed up. If you have to shrink do it after the log backups. |
 |
|
Too many steps to reduce Transaction Log size?
