Shrinking the SQL Server database

If the size of the database design is too large, the database will still consume a large amount of disk resources even if large amounts of data are deleted from the database. Therefore, the database needs to be shrunk according to the actual needs of the user.

3 Ways to shrink:

1, Auto_shrink

Basic format:

ALTER DATABASE database_name SET auto_shrink on

When this option is set to ON, the database engine automatically shrinks the database with free space.

2. DBCC Shrinkdatabase

Basic format:

DBCC shrinkdatabase (' database_name ', target_percent)

This approach requires manual shrinking of the database size and is more flexible than automating shrinking the database, which shrinks the entire database.

3. DBCC Shrinkfile

Basic format:

DBCC shrinkfile (' file_name ', target_percent)

The command can shrink the specified database file, shrink the file to a size smaller than its initial creation, and reset the current size to its initial creation size. It is worth noting that the shrink statement can only shrink the database to the smallest initial file size that has been specified since the beginning of the database creation, if your database was first created with an initial size of 1024MB, and no one has ever mobilized the initial size. So sorry your data can only shrink to 1024MB, although there may be 1023MB are not actually used ...

Note: It is recommended that you do not use the auto-shrink feature frequently, preferably by disabling auto-shrinking, or by manually shrinking or dispatching the operation, shrinking every time.

Shrinking the SQL Server database