defragment SQL server database

Question

I have deleted some tables on a SQL server. Suddenly the size of the database jumped by 100Gb (whereas the deleted tables were taking 10% of that..).

I have read good and bad things about SHRINK hence to test I restored a copy of this database to another server and SHRINKed it: the massive unallocated space was released. However I am reading that SHRINK may cause the database to be fragmented:
- How can I avoid that,
- Or is there an easy way to defragment, for instance, if I restore a database, will it be defragmented again? Or will it have same fragmentation as the original db?

Answer 1

• How can I avoid that

The only way to avoid this is by not shrinking that is why shrinking is such a evil operation. But there are situations where conditions demand a shrink to be done to release excess space and for that case you have no option but to do it. You would then have to run defragmentation of the indexes. Paul Randal in This Blog showed alternate way (read the whole blog this is exactly what you may need).

Further I would also suggest you to read Mitigating Index Fragmentation

The method I like to recommend is as follows:

1. Create a new filegroup
2. Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same time
3. Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

This may not always be possible in all environments but this is good alternate of shrinking.

Or is there a way to defragment, for instance, if I restore a database, will it be defragmented again? Or will it have same fragmentation as the original db?

If you restore a database which has index defragmnated you do not need to run index defragmentation again after the restore. Restore creates exact copy of the database