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 |
|
Westley
Posting Yak Master
229 Posts |
 Posted - 2006-02-10 : 02:42:38
|
| Hi all, Just something I need to confirm, when we do a index rebuild via dbcc dbreindex (or other commands), I believe that will actually take the space on the log where it should not increase the data size isn't it? Is there any case where it will increase the data size during the reindex? I remember I read it somewhere that it said it will only take use of the log space, thats why its recommended to have at least 1/3 of the data size in log. Any ideas?Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-10 : 03:50:12
|
| "Is there any case where it will increase the data size during the reindex?"My understanding is that DBREINDEX will "move" all the index blocks to a new part of the database, and then release the original. For a clustered index this presumably includes all the data too ... if that's correct you'll get a hit on the MDF as well as the LDFOTOH DEFRAG makes it changes in-situ.Kristen |
 |
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2006-02-10 : 04:41:15
|
| Kristen, So that means only on the clustered index? and non-clustered will not have any impact on mdf at all? and the hit on mdf should be on a per table bases, as once the clustered index re-builded, it will release the original? I though when you rebuild the clustered index, the table become inaccessable, thats because the clsutered index doesn't "exist" anymore and the data is being re-order, if in your case, we should be still able to access the table? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-10 : 06:11:58
|
| "So that means only on the clustered index?"No, it applies to all indexes (recreate the whole thing to a "fresh" area of the MDF before dropping the original), but the impact is greater if its a clustered index ('coz the table data is copied too). [That's my understanding ...]"the table become inaccessable"Any DBCC REINDEX will lock the underlying data for the duration of the rebuild. However, that is not the case with DBCC DEFRAG [which can also be aborted part way through and any defragging thus far will be preserved, whereas aborting a DBCC REINDEX will cause all its work to be rolled back]Kristen |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2006-02-12 : 22:11:36
|
| Thanks Kristen, I think I confused with the clustered index creation, where the non-clustered index will not lock the table, yeah, you are right, it will have the same impact as it will create the new index in MDF before it drops the original during index rebuild :) Not enough sleep, my mind is not thinking right :P |
|
|
|
|
|
|
|
|
Size on index rebuild