[dba-SQLServer] index fragmentation and shrink

jwcolby jwcolby at colbyconsulting.com
Mon Mar 29 10:08:43 CDT 2010


I have a table which is read-only.  This table is about 40 GB but is in a database which is about 
180 gb due to initial processing.  I have a clustered index on the table on the PKID (int auto 
increment).

I have been trying to figure out how to shrink the database without destroying the index 
(fragmentation). Given that I NEVER write to this table the normal "leave the empty space alone 
'cause you will need it later anyway" just doesn't apply to me, and I need to recover the space.

When I study how to do this, what I am reading is to create a new file group, set it as primary, and 
then basically recreate the table out there.

So I created a new filegroup and set it as primary.
Created an empty table out there (checked with sp_help).
Created the clustered index on the new table
Started the copy from the old table to the new.

Since the original table was already sorted on the PKID (clustered index on the PKID) my take is 
that it will read the records from the original in sorted order, write them out to the new table in 
sorted order, building the clustered index on it as it does so.

Will the new file in the new filegroup be as small as possible now?  Will it still have significant 
"empty space" due to "processing" of the index?

IOW am I going to succeed in getting at LEAST a file with only a small "empty space" overhead?

-- 
John W. Colby
www.ColbyConsulting.com



More information about the dba-SQLServer mailing list