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
 General SQL Server Forums
 New to SQL Server Programming
 Question about a management plan
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

V.V.
Starting Member

30 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 working...it 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?

chadmat
The Chadinator

USA
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.

-Chad
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.14 seconds. Powered By: Snitz Forums 2000