July 15, 2008 at 7:33 am
Hi, I have set up a maintenance plan for some databases that will do Re-index once a week nightly at 10 pm. But I am not sure what to do after the Reindex. Essentially, I want to truncate the logs because I am somehow worried if it will grow large. What is the best thing to do for this?
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
July 15, 2008 at 7:45 am
Reindex alone should be fine. Reindex updates the statistics so there's no need to do that as a separate step
Don't shrink your databases
Don't truncate your logs. Either set up a proper log backup schedule or set the database into simple recovery mode and forget about the logs.
Truncating logs breaks the log chain (if you're in full recovery and running log backups) and means that you can't restore to a point past that truncation unless you take another full/diff backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2008 at 7:55 am
In my case, I am taking a fullbackup at 8pm. I don't have other backup plans(simple, bulk logged).This fullbackup will be done at about 9.30pm. Backups are taken everyday and the schedule don't change. But I am doing the Reindex thru maintenance plan every Saturday at 10 pm.
Your answer was good enough for my question before, but just wanted to know if I am still safe in my case here(fullbackup)...
What exactly will happen to the logs after my Reindex is done?
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
July 15, 2008 at 7:56 am
What recovery model is your database in?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2008 at 8:02 am
Simple
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
July 15, 2008 at 8:29 am
If you are in simple mode then your logs will be truncated at each checkpoint, and space used by committed transactions marked as free.
However, you cannot recover work beyond your full backups. So at 5pm if your database fails, all the work done that day is gone when you restore your 9:30pm backup from the previous night.
If you want to recover up to 5pm, or 4:59, you need to be in full recovery mode and perform log backups on a regular basis.
July 15, 2008 at 8:38 am
Sorry. My recovery model is FullRecovery mode. Got distracted by another thread.
So, am I still safe with the current plan?
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
July 15, 2008 at 9:23 am
Well, this question regarding Reindex has struck something important for to reconsider about my databases. I am taking a fullbackup no matter what. I do have full recovery mode. But, if I set it to simple recovery mode, I will benefit from the log space, but at the same time will be not be able to recover to the latest point of time. What should I do about this to take full advantage of both? Is there any workaround?
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
July 15, 2008 at 9:32 am
SQL King (7/15/2008)
Well, this question regarding Reindex has struck something important for to reconsider about my databases. I am taking a fullbackup no matter what. I do have full recovery mode. But, if I set it to simple recovery mode, I will benefit from the log space, but at the same time will be not be able to recover to the latest point of time. What should I do about this to take full advantage of both? Is there any workaround?
With the database setup in full recovery model, you need to add to your maintenance frequent log backups. How often you run this will ultimately be determined by your business requirements, but I would recommend backing up the transaction log every hour.
Once you have that set up, monitor the size of the transaction log. Allow the transaction log to grow as large as it needs to be to handle your daily transactions and the weekly reindexing. Once this has been done, leave it alone. Don't shrink it, don't truncate - just backup every hour.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2008 at 11:24 am
Thanks for all these answers above. Not only I have made myself clear, but I have reconfigured the way my database was set to now. I do have a full backup, simple recovery(to take care of the logs) and I am taking separate log backup. Then I am running the Rebuild index task from the maintenance plan. Thanks all!!!
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
July 15, 2008 at 12:47 pm
SQL King (7/15/2008)
Thanks for all these answers above. Not only I have made myself clear, but I have reconfigured the way my database was set to now. I do have a full backup, simple recovery(to take care of the logs) and I am taking separate log backup. Then I am running the Rebuild index task from the maintenance plan. Thanks all!!!
You may want to check again. You cannot do a transaction log backup if you are in simple recovery mode.
Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2008 at 12:59 pm
Damn!! Thanks for that.
Well, i guess I have to stick with the old plan where I have Full Backup and full recovery. But I am definately taking the transactional log and maybe consider differential with the team lead. I am just thinking another alternative for my reindex to not grow big logs
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
July 16, 2008 at 12:29 am
SQL King (7/15/2008)
Damn!! Thanks for that.Well, i guess I have to stick with the old plan where I have Full Backup and full recovery. But I am definately taking the transactional log and maybe consider differential with the team lead. I am just thinking another alternative for my reindex to not grow big logs
If you're in full recovery, you need transaction log backups. Make sure they do run successfully.
As for the reindex, in full recovery, they are fully logged operations. If you switch to bulk logged recovery before doing the rebuild and back to full afterwards, the index rebuilds will be minimally logged, so you won't see that much logging.
Just be sure to read up on bulk logged and its implications for recovery during bulk operations
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2008 at 9:48 am
That's a very sweet alternative. It just works fine and with no large logs anymore. I am sure I am okay right now. Thanks for giving me all the thoughts.:)
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply