SQL Server database shrinking

Limitations and Limitations

• The shrinking database cannot be less than the minimum size of the database.   The minimum size is the size specified when the database was originally created, or the last time you use a file size change operation (such as DBCC Shrinkfile) Sets the explicit size.   For example, if the database was originally created with a size of ten MB and later grew to The database can only be shrunk to a minimum of ten MB, even if all the data for the database has been deleted.

•  

• when encountering xvelocity memory-optimized Columnstore indexes, DBCC shrinkdatabase Will fail.   The work done before the Columnstore index is encountered will succeed, so the database may be smaller.   to complete DBCC SHRINKDATABASE, perform DBCC Disable all Columnstore indexes before shrinkdatabase, and then regenerate the Columnstore index.

Recommended

• To view the current amount of available (unallocated) space in the database. For more information, see Display Data and Log Space information for a Database

• When you plan to shrink a database, consider the following information:

  • Performing a shrink operation is most effective when performing operations that result in many unused space, such as truncating tables or deleting table operations.

  • Most databases require some free space to be used for routine daily operations. If you shrink the database repeatedly and notice that the database size becomes larger, the shrinking space is required for normal operations. In this case, shrinking the database repeatedly is a meaningless operation.

  • Shrinking does not preserve the fragmented state of indexes in the database, and often increases fragmentation to some extent. This is another reason not to shrink the database repeatedly.

  • Do not set the Auto_shrink database option to on unless you have a specific requirement.

SecurityPermissions

Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

SQL Server database shrinking