Quick Tips While Compressing Database in SQL Server

Database compression feature is one of the feature which helps in improving SQL performance by reducing the database storage. SQL Server consumes more CPU resources when it get compressed. Here we will discuss some recommendations and important points while compressing database:

1. 1. Database compression can cause index fragmentation and slow down the database. Therefore, it is too often not recommended to perform database compression
   2. It is better to compress the database before the operation of rebuilding indexes, i.e. after compression, run the rebuild index procedure
   3. It is better not to set the AUTO_SHRINK database parameter ( automatic compression ) to ON, but leave it as default, i.e. in OFF, unless of course you have enough serious reasons for it
   4. The SHRINKDATABASE statement does not allow reducing the size of the database to a size that is smaller than the initial one, i.e. minimum. However, the SHRINKFILE instruction can do this (the second parameter is a size smaller than the minimum ). The minimum database size is the size that is specified when the database was created or explicitly set by a database resize operation, such as DBCC SHRINKFILE or ALTER DATABASE. For example, if the database was created with a size of 10 megabytes, then increased to 100 megabytes, it can be compressed with SHRINKDATABASE only to the initial 10 megabytes, even if all data has been deleted from the database
   5. You cannot compress the database and transaction log files when the backup process is in progress. And vice versa, it is impossible to create backup copies of the database and transaction log while their compression process is in progress
   6. Executing DBCC SHRINKDATABASE without specifying the NOTRUNCATE or TRUNCATEONLY parameters is equivalent to executing DBCC SHRINKDATABASE with the NOTRUNCATE parameter after executing the DBCC SHRINKDATABASE instruction with the TRUNCATEONLY parameter
   7. In the process of compressing a database, users can work in it ( i.e., it is not necessary to transfer the database to single-user mode )
   8. At any time you can interrupt the process of performing SHRINKDATABASE and SHRINKFILE operations, while all the work done is saved
   9. Before starting the compression procedure, check whether there is free space in the database files for deletion, i.e. is it possible to compress files at all by running the following query ( it will show in megabytes how much you can reduce the database files ).

  SELECT Name AS NameFile,
  	   size / 128.0 - CAST (FILEPROPERTY (name, 'SpaceUsed') AS INT) /128.0 AS AvailableSpaceInMB

FROM sys.database_files;

1. To perform the database compression procedure, you must be a member of the sysadmin server role group or the db_owner database role
2. Compressing database files and transaction logs is a rather resource-intensive process that requires a certain amount of time ( depending on file size ), so this procedure needs to be planned and generally performed only when absolutely necessary ( for example, the size of the database and the log became too large and more than half single file takes up unused space ).