In a SQL Server environment, each database file can be compressed by deleting unused pages. Although the Database Engine optimizes the allocation of disk space, sometimes there are situations where files no longer need the amount they were previously allocated. The program provides for the compression of database files both manually and automatically after a certain time.
Instructions
Step 1
For automatic compression, there is an AUTO_SHRINK database in the environment, the parameter of which can be set to ON. With this database on the system, the Database Engine will automatically shrink any SQL that has free space. Parameters are configured using the ALTER DATABASE statement, which is initially set to OFF. All automatic compression operations take place in the background and do not affect user actions in the database.
Step 2
SQL Server databases are manually compressed using the DBCC SHRINKDATABASE (DBCC SHRINKFILE) statement. If the selected instruction cannot reserve space in the log file, an informational message is displayed indicating the action required to free disk space.
Step 3
With DBCC SHRINKDATABASE, you cannot shrink the database to a size that is smaller than the original size. If the database was created with a size of 10MB, and then it expanded to 50MB, it will be possible to compress it only to 10MB, even if all data is deleted.
Step 4
With DBCC SHRINKFILE it is possible to compress individual files to sizes that are clearly smaller than the initial size. However, each database file will have to be compressed separately.
Step 5
When these instructions are used, the transaction logs are automatically shrunk to the requested size. The greatest effect of compression is achieved only if it is performed after an operation that creates a lot of unnecessary space (for example, dropping a table).
