Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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...
