0

What is the best option to recover empty space after running a bulk delete? DBCC Shrinkfile is useful to truncate the space. DBCC Shrinkdb is used to shrink the db files, but it leads to fragmentation. Do we need to follow a sequence to recover the empty space and maintain the performance?

enter image description here

0

1 Answer 1

0

All databases are growing except when certain purge operations are performed, but this growth will resume immediately afterwards.

Also an operation which consists in recovering place in the files to restore it to the disc does not have much interest if it is only to fragment the disc.

On SSDs this is not important. However, purge operations generate significant resource consumption and although they are not blocking, they are in no way guaranteed with respect to the garbage collection target.

Also this kind of operation does not need to be undertaken as long as you have space on your discs, because this operation will be canceled sooner or later by the growth of your base.

In any case, the "SHRINK" of database files is an operation that should be rare and should only be performed in an emergency...

The most important matter about perofmances are not index fragmentation but optimizer's statistics. By the fact a complete rebuild of all indexes do a UPDATE STATISTICS on all stats in FULLSCAN mode...

2
  • Agreed. The database will grow back. However, if we are not regaining or recovering the empty space then what benefit we can have with the BULK delete and archive process?
    – rushabh138
    Commented Mar 22, 2023 at 14:09
  • Absolutly no gain in terms of performances
    – SQLpro
    Commented Mar 22, 2023 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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