We had a large log file that we hadn't backed up (test machine). I backed it up and shrunk it. The size did not budge. I backed it up and shrunk it a second time. This time the log file size reduced significantly.
Probably the active part got moved from the end to the start between bacups. It's a circular file so virtual files get reused. First backup released space so the active entry was at the end - didn't shrink before that. Updates reused the start of the log. Second backup released entries at the end so the shrink dropped virtual files back to the begining. Can you set the recovery model to simple - and also change model so that is the default for new databases?
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
Probably the active part got moved from the end to the start between bacups. It's a circular file so virtual files get reused. First backup released space so the active entry was at the end - didn't shrink before that. Updates reused the start of the log. Second backup released entries at the end so the shrink dropped virtual files back to the begining.
Thankyou
quote: Can you set the recovery model to simple - and also change model so that is the default for new databases?
Although this is a test machine, we still wan't full recovery on some of our Databases. We are in the midst of setting some things up - we are moving from simple to full rather than from full to simple. So the next step will be daily or weekly Tran log backups.
The reason our log file got so big is because we are doing weekly rebuilding all our indexes. Although that is taking too long, which I know is another issue...
>> daily or weekly Tran log backups That should be the first step when you create the database. Do a full backup and schedule tr log backups. Should only rebuild indexes if it's needed - and probably need to allow log space for it - maybe include backups in the rebuild job.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
Large Log File - Needed to back it up twice?
Posted - 12/05/2012 : 10:29:09
