Rebuilding the clustered index

Question

Rebuilding the clustered index

Krasavita

SSC-Insane

Points: 23138
More actions
September 25, 2008 at 11:36 am

#193586

What is the best way to rebuilding the clustered index? Is this cleaning up the space on the server?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

VBeachcomber SSC Veteran Points: 253 More actions · Answer 1

I would change the recovery mode to 'Bulk logged' and then run dbcc dbreindex and then change back the recovery mode to Full

this way you don't log those trn's and you log file doesn't grow and you don't have to shrink your log file size.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

You shouldn't be using DBCC commands for index operations in 2005.

As for the question at hand, what are you going for here? What is the best way to rebuild a clustered index .... during the day? off hours? least amount of contention? Running on enterprise? Based on fragmentation? What is your goal? If there are no considerations, than simply:

ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD

Vivien Xing SSChampion Points: 12297 More actions · Answer 3

Worth a read: Online Indexing Operations in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/onlineindex.mspx

Need to estimate the space for reindex, etc...

Krasavita SSC-Insane Points: 23138 More actions · Answer 4

We always have people in the system,but I think would do on evening hours when less traffic. Thank you

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

yulichka (9/26/2008)
We always have people in the system,but I think would do on evening hours when less traffic. Thank you

Are you running on enterprise? If so, you can rebuild online.

Krasavita SSC-Insane Points: 23138 More actions · Answer 6

Sorry to ask you a stupid question, how do I know if I am running on enterprise?Thank you

Adam Bean One Orange Chip Points: 26631 More actions · Answer 7

yulichka (9/26/2008)
Sorry to ask you a stupid question, how do I know if I am running on enterprise?Thank you

SELECT SERVERPROPERTY('Edition')

Krasavita SSC-Insane Points: 23138 More actions · Answer 8

We are on Microsoft SQL Server Standard Edition (64-bit).Thank you