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.
| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2012-12-05 : 10:29:09
|
| Something I don't understand.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.Anyone know why I had to do this twice? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-05 : 10:37:55
|
| 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. |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-12-05 : 10:52:12
|
quote:
Originally posted by nigelrivett
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.
Thankyouquote:
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... |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-05 : 11:07:09
|
| >> daily or weekly Tran log backupsThat 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?