2

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?

  • >>>Suddenly the size of the database jumped by 100Gb <<< Was it data size that increased or it was log to grow? – sepupic Jan 29 '18 at 9:24
  • @sepupic none of it: I have now Size=129Gb; Log space: 10.4Gb; Data space: 1.71Gb; unallocated space: 117Gb. Hence it is the unallocated space that jumped by 100Gb – RockScience Jan 29 '18 at 9:44
  • I meant data file size or log size and there cannot be the answer "none of it". It's your data file space. – sepupic Jan 29 '18 at 9:47
2
  • 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

  • thank you for the link to the BLOG. I might try SHRINK with TRUNCATEONLY, which my be enough in my case! – RockScience Jan 29 '18 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.