Why you cannot perform DBCC SHRINKDATABASE frequently

Reasons to perform DBCC SHRINKDATABASE frequently

First, we need to understand that not all the space in the data file is used, but only some unused space: including deleted data, unused space for the automatic growth of the file, and some pieces of space that cannot be used, which can be obtained by sp_sapceused. After the DBCC SHRINKDATABASE is executed, the allocation page is moved from the end of the file to the unallocated page at the front of the file, and then compressed, and only if Truncateonlya is executed will the space be released to the operations department for understanding DBCC The shrinkage principle of shrinkdatabase Let's look at a few more questions:

1.DBCC Shrinkdatabase shrink to defragment the database file?

No! The DBCC shrinkdatabase only shrinks space and does nothing to deal with it, and the database files can only be fragmented.

2. Does DBCC shrinkdatabase shrink the database faster?

No! DBCC Shrinkdatabase does not perform the process of collating indexes after shrinking, so there will be more fragmentation of the index and slower execution speed.

3. Why do I keep indexing every few days, but the fragments of the index still produce quickly?

For the second article, it is estimated that you executed the DBCC SHRINKDATABASE after performing the index collation.

4. When do I use DBCC SHRINKDATABASE?

Performing a shrink operation is most efficient and produces less fragmentation only if a lot of unused space operations, such as truncating a table or deleting a table operation, are produced.

Conclusion:

DBCC shrinkdatabase are not not to be used, but rather to be used sparingly, especially if they are not used frequently, because it increases the degree of fragmentation of the database.

Comments:

A:truncateonly reclaims all available space at the end of the file to the operating system. However, Truncateonly does not perform any page movement within the file. The specified file is only shrunk to the most recently allocated area. If specified with Truncateonly, the target_percent is ignored.