Defragmenting a database?

[katbear]

What is the "best" way to defragment a database? I suspect one of our databases is fragmented as it has been on autoshrink (yuck).

Anyways, I was thinking that I could defragment by making sure an clustered indexe exists on all the tables, then running index rebuild or defrag on the index (I don't know which, I need to look this up in bol again).

Does this sound reasonable? Or is there a better way?

Thanks

 

[ptheriault]

your data file or .mdf is always going to have some fragmentation on disk because of auto growing or shrinking. What version of SQL are you running? If it's 2005 you will run the ALTER INDEX command to fix index fragmentation. If it's 2000 you will run DBCC DBREINDEX. But both of these will not will not correct the fragmentation of your .mdf file. If you really wanted all your data in one contiguous file you could create another data file and rebuild all your clustered indexes in the new data file.

- Paul
- If at first you don't succeed, find out if the loser gets anything.