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 |
|
V.V.
Starting Member
32 Posts |
 Posted - 2013-02-28 : 07:57:28
|
| 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
1974 Posts |
Posted - 2013-03-09 : 18:52:30
|
| 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 |
 |
|
|
|
|
|
Question about a management plan