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?
Best Answer
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
This may not always be possible in all environments but this is good alternate of shrinking.
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