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)
 Large Log File - Needed to back it up twice?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 12/05/2012 :  10:29:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/05/2012 :  10:37:55  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 12/05/2012 :  10:52:12  Show Profile  Reply with Quote
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.


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...
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/05/2012 :  11:07:09  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
>> 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.
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