January 17, 2011 at 6:52 am
Hi,
We have a 310 gb database. I had scheduled a reorganizing indexes job on the database in the weekend which failed because the log file went upto 137 gb in 45 minutes and then the disk space was full and also my log shippping got out of sync because of this in the morning.
We are planning to increase the disk space which is now at 138 gb to accomodate the log file of the database.
But my question are
1) how would I know how much more space I need for the full completion of the reorganizing job.
2) Also, If we dont have the more disk space, then is it possible to stop the job in between and shrink the log file( to inc disc space) and again restart the job.
3) Is this method possible while running the job manually??
4) If i rebuild the indexes, then also log file grows at this rate??
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
January 17, 2011 at 7:01 am
in 3) I meant if i run the script ..
Regards
Sushant Kumar
MCTS,MCP
January 17, 2011 at 1:14 pm
You may need to rebuild/reord the indexes a table at a time. It is virtually impossible to guess how much log space is required for an entire rebuild without having tested previously. Take a look at Michelle Ufford's incredibly useful Index Rebuild Script. You will find it is used by countelss SSC users.
http://sqlfool.com/2009/06/index-defrag-script-v30/
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
January 18, 2011 at 7:14 am
Is it possible when running the script for rebuilding indexes , that I can shrink the log files in between.
If so, how?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
January 19, 2011 at 3:05 am
If you ran a logfile shrink in between each table reindex I wouldn't expect it to help at all, because all the information you've just put in the log file hasn't yet been committed at that point and so the log file is all in use and won't shrink. It wouldn't help your log shipping issue either, because all those changes *have* to be replicated to the secondary server regardless of how big the log file on the source server is.
January 19, 2011 at 4:14 am
Moreover shrinking will lead to further fragmentation, and also will result in data file growing again. Never ever shrink a DB for performance gain, it never happens. Pls read Paul Randal's blogs on this topic.
January 19, 2011 at 4:40 am
Backup your transaction log inbetween index rebuilds.
January 19, 2011 at 6:08 am
@ homebrew
Are there script for running index rebuilds for some defined percentage of tables in a database...
for ex 25% of database tables at a time.
As my db has 2000 indexes, i cant manually rebuild index for each one.
Moreover, in the night my log shipping doesnt work, so iam planning to perform maintenance at tht time, so when i backup my transaction logs in between index rebuilds, then in morining wen log shipping restarts, will it be in sync automatically ??
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
January 19, 2011 at 6:10 am
also,
there has not been any reorganize/rebuild indexes on this database for 2-3 months.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
January 19, 2011 at 6:53 am
Sushant - did you take a look at Michelle Ufford's Index Rebuild Script I sent you a link? In it she explains how you can set a time limit so that it only runs for two or three hours as opposed to completion. OR, you can set the desired depth of index fragmentation to be rebuilt, thus again only taking a certain amount of time and letting you run a backup in between sets.
http://sqlfool.com/2009/06/index-defrag-script-v30/
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
January 19, 2011 at 7:03 am
@ chris
I tried to run it on dev server but i got errors.
🙁
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
January 19, 2011 at 8:18 am
thank for question
you rebuild index and then you start the job
Rajesh Singh
DBA(HCL Comnet)
+91-0560888360
January 19, 2011 at 8:31 am
@ rajesh
I cant get you
Start which job?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
January 19, 2011 at 9:52 am
Also, you could set your normal t-log backup job to run every 10 minutes during the rebuild time frame.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply