Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Question about a management plan
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

32 Posts

Posted - 02/28/2013 :  07:57:28  Show Profile  Reply with Quote
Hi guys,

I have a database which .mdf file size is more than 180 GB. Until now was set on simple recovery model but I want to let it on full recovery model because we want to backup log file also.

Ok, so the management plan is like this:
- full backup every morning;
- log backup every two hours between 8:00 AM and 7:00 PM;
- rebuild indexes every night at 1:00 AM;
- update statistics every sunday;

So far so good...but after I switched the database to full recovery, the size of first log backup was like 23 GB compressed! I've shrinked the log file to 1 MB.
Next day, the size of log was like 134 GB. What happened during night because no one is is possible that log file to grow because of rebuild indexes job? (I've read that is a cause too).

My question is: what should I change in this management plan? I want to keep the database on full recovery model but...shrinking every day log file is not an option.

Any advice from tour experience?

The Chadinator

1974 Posts

Posted - 03/09/2013 :  18:52:30  Show Profile  Visit chadmat's Homepage  Reply with Quote
The rebuilding indexes will definitly use a lot of log. Why did you decrease to 1 MB? What are your autogrowth settings? You can switch to bulk logged recovery before you do the index mainteance, and then back to full afterwards.

Go to Top of Page
  Previous Topic Topic Next 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.02 seconds. Powered By: Snitz Forums 2000