July 15, 2011 at 1:52 pm
in trn log space, do I need space to accomidate the total size of ALL indexes, or total size of the LARGEST index in order to run a dbcc dbreindex operation against the clustered index of a table?
July 15, 2011 at 1:55 pm
what recovery model is your database using?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 15, 2011 at 1:57 pm
Perry Whittle (7/15/2011)
what recovery model is your database using?
by default, full.
if I absolutely MUST switch to simple, its not the end of the world. but I'd like to avoid it.
I have about 192gb of TOTAL index space, spread across a bunch of indexes, that are in the 20-35gb range. and a 150gb log drive.
yea or nea?
edit, a colleague and I are debating which total of free space we need, and if theres a quick answer someone has on the top of their head, i'd greatly appreciate it! 🙂 and the googles arent giving me quite the answer I need
July 15, 2011 at 2:06 pm
LAW1143 (7/15/2011)
if I absolutely MUST switch to simple
You dont need to and indeed you shouldn't do either. You can however switch to Bulk Logged recovery for the index build and back to Full afterwards, but there are implications for that around point in time restores
LAW1143 (7/15/2011)
Do avoid it, do yourself a favour
LAW1143 (7/15/2011)
I have about 192gb of TOTAL index space, spread across a bunch of indexes, that are in the 20-35gb range. and a 150gb log drive.
yea or nea?
edit, a colleague and I are debating which total of free space we need, and if theres a quick answer someone has on the top of their head, i'd greatly appreciate it! 🙂 and the googles arent giving me quite the answer I need
There are a number of fine scripts around that will intelligently rebuild or reorganise an index. I find Michelle Uffords the best. Google for SQLFool and obtain a copy of her script
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 15, 2011 at 2:22 pm
theres alot of lines of code there! 😮 more than I'll have a chance to digest for a while!
we've got a table thats been largely purged, and to recover all of the space, help with system perf, etc we need to rebuild the clustered index.
that puts me back to the original question... in calculating the log space requirement for a clustered index rebuild, do I need to have log space for the sum of all indexes, or space of the largest index available?
BTW, thanks for the replies! I'll look more closely at teh sqlfool scripts early next week!
July 15, 2011 at 3:38 pm
Depends. Which are you going to rebuild.
If you plan on rebuilding all of your indexes between two log backups, then budget for at least 120% the total size for all indexes for log space. If you're just going to rebuild one, then budget at least 120% the size of the one index.
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, 2011 at 3:46 pm
LAW1143 (7/15/2011)
that puts me back to the original question... in calculating the log space requirement for a clustered index rebuild, do I need to have log space for the sum of all indexes, or space of the largest index available?
What are the sizes of the current indexes
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 15, 2011 at 4:50 pm
There are 6 or 7 of them, all in the 25 to 40gb range.
Rebuilding the clustered index will hit all non clustered indices, but I'm not sure if it needs all that space at once or not...
July 15, 2011 at 8:53 pm
LAW1143 (7/15/2011)
There are 6 or 7 of them, all in the 25 to 40gb range.Rebuilding the clustered index will hit all non clustered indices.
Generally, no
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 16, 2011 at 3:47 am
LAW1143 (7/15/2011)
Rebuilding the clustered index will hit all non clustered indices
No it won't. Rebuilding the cluster only rebuilds the cluster. Doesn't touch the non-clustered indexes.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply