Need to shrink database after drop-ing big table

[laserovic]

Hi all,

My database has 220GB, but there is 75Gb free space (in mdf) - we dropped one big table and the space won't be used soon.
How to do it without fragmenting indexes ? I have tried it with a copy of it, but DBCC SHRINKFILE defragments most of indexes , after reindex still a lot of them are much more fragmented then now.

It is OLTP, daily usage from 6 a.m. to 6 p.m., so it is possible to do it after working hours.

I have some ideas like
a) move tables to another filegroup, shrink the file and move back or
b) copy tables, then indexes and other structures to another database and rename after, but this is really a lot of work.

I always avioded shrinking database, but now i need it at the moment and there must be a way how to do that.

[rmiao]

Have clustered index in all tables? Can't defrag table without it. What you should do is rebuild all indices, shrink file (leave some free space in the file) then reindewx again befause shrinking file will cause fragmentation.

[laserovic]

Most of them have clustered index (but in case of the largest it is not PK). I have a script which based on level of fragmentation defragment those indexes where necessary.

[rmiao]

Defrag index doesn't address table external fragmentation.