September 26, 2011 at 3:03 pm
In one of my subplans of a maintence plan,
I have 3 tasks in the plan:
One is check database integrity, then rebuild index, then update statistics taks.
Above is excuted in order in the the sub plan, they are in one job schedule.
By looking at the history, the job failed while reindexing makes the transaction log full, so the job failed. I want to fix this by doing more transaction log backups during the reindexing process. But it's hard for me to know how many times should backup , and what exact time to do the backup,
How can I solve this problem?
Thanks
September 26, 2011 at 4:08 pm
September 26, 2011 at 4:10 pm
there are a number of options, you may have to experiment to see what works best for you.
Just increase the log file to a size that can handle the reindex.
do a log backup just before the reindex (with an execute job task)
set up an alert to backup the log if it reaches a certain percentage full
write your own reindex job that includes log backups as part of the code.
Break up the reindex job to do different indexes at different times.
---------------------------------------------------------------------
September 26, 2011 at 4:27 pm
george sibbald (9/26/2011)
there are a number of options, you may have to experiment to see what works best for you.Just increase the log file to a size that can handle the reindex.
do a log backup just before the reindex (with an execute job task)
set up an alert to backup the log if it reaches a certain percentage full
write your own reindex job that includes log backups as part of the code.
Break up the reindex job to do different indexes at different times.
I just checked the log file intial size is 4.8 gb, restricted to 5 gb.
Anytime when not reindex, the log file is 98% free space
Is that the restricted to 5 gb is a problem, then what size should I set it to?
Thanks
September 26, 2011 at 4:31 pm
How much free space have you got on the drive that you can afford to be used by the log file?
Whats the size and space used in the data file?
---------------------------------------------------------------------
September 26, 2011 at 4:36 pm
george sibbald (9/26/2011)
How much free space have you got on the drive that you can afford to be used by the log file?Whats the size and space used in the data file?
I checke the log drive has 80 gb free space.
Data file: initial size 9.5 gb, autogrowth 500mb, restricted growth to 10 gb.
Log file: initial size 4.8 gb, autogrowth 250 MB, restricted growth to 5 gb.
Thanks
September 26, 2011 at 4:51 pm
On those sizes I would say you are safe to set the log to unrestricted growth, set growth to 1GB, backup the log, run the reindex, let the log grow, whatever size it reaches is the size you want your log file.
---------------------------------------------------------------------
September 26, 2011 at 4:56 pm
Thanks.
September 26, 2011 at 6:34 pm
And...
Please consider not using built-in maintenance plans for your reindexing...
Ir reindexes EVERYTHING whether it needs it or not... There are plenty of good scripts out there you can put in a job that queries fragmentation levels first and then decides what objects need to be reorg'ed or rebuilt.
This will save you time, reduce server load and rollback space as well.
[font="Verdana"]-- Wally
-- Reluctant DBA[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply