How to Compress Database and Transaction Log in SQL Server

👤 Naveen Sharma | Modified: May 8th, 2023|SQL | 6 Minutes Reading

Many Microsoft SQL Server administrators have experienced the problem of significantly increasing the physical size of the database and transaction log files and, of course, they would like to somehow reduce this size in order not to take any action related to increasing free space on the server. hard drive. The way to reduce the physical size of the database file and transaction log in SQL Server is – this is compression .

What is Compression in SQL Server?

Compression is the process of removing unused space in database and transaction log files.

The physical size of the database files grows with time, this is due to the addition of data, but when they are deleted, the physical size of the files remains the same, but in these files there appears a logical unused space, which can be deleted.

The greatest effect of compression is achieved when the compression operation is performed after the operation of deleting tables from the database or deleting data from the tables.

You should distinguish the transaction log compression procedure from the transaction log truncation procedure. Compression is a reduction in the physical size of the log by eliminating unused space, and truncation is freeing up space in the logical log for reuse ( i.e., unused space is created ) by the transaction log while the size of the physical file is not reduced.

Transaction log truncation occurs automatically:

In a simple recovery model, after reaching a checkpoint, which may occur, for example, after creating a BACKUP database, when the CHECKPOINT statement is explicitly executed, or when the size of the logical transaction log is 70 percent full, in all these cases, the inactive part of the log is automatically cleared i.e. his truncation;
In the full recovery model or in the incompletely logged recovery model – after creating a backup of the log, provided that a checkpoint has been reached since the last backup of the log was created.

If you use the full recovery model or the incompletely logged recovery model and your transaction log files are too large, then most likely you have not done a BACKUP ( backup ) transaction log for a long time . In this case, you need to do the BACKUP transaction log first, and then perform the transaction log compression, which we will look at just below.

It is also possible that the size of transaction log in SQL Server files is too large ( both with a simple and full recovery model ) due to the delay of the truncation procedure, i.e. the size of the journal consists mainly of the active part of the journal, and the active part cannot be truncated, therefore the physical size of the journal increases. The delay of the truncation procedure is influenced by such factors as: active long-running transactions, some scenarios for displaying mirror databases and transaction logs, some scenarios for transactional replication and transaction logs, and truncation of the log is not possible during backup and restore operations. In this case, you need to eliminate the causes of the delay, then do the truncation ( for example, for the full recovery model of the BACKUP log), and then compression to an acceptable size.

Usually, if transaction logs or database backups are created on a regular basis at regular intervals ( with a simple recovery model ), transaction log files do not grow and transaction logs do not overflow.

How to Compress Database in MS SQL Server?

You can compress database and transaction log files using the Management Studio GUI and Transact-SQL statements: DBCC SHRINKDATABASE and DBCC SHRINKFILE . It is also possible to configure the database for automatic compression by setting the AUTO_SHRINK database parameter to ON.

Note! I will consider database compression using the example of Microsoft SQL Server 2016 Express .

Compress Database using Management Studio

We start Management Studio and open the “ Databases ” object in the object browser . Then we right-click on the database that needs to be compressed, then select “ Tasks -> Compress -> Database (or Files, if, for example, you need to compress only the transaction log) ”. I choose “ Database ” for example .

As a result, you will open the “ Database compression ” window , in which you, by the way, can observe the size of the database, as well as the available free space that can be deleted ( ie, compressed ). Click ” OK “.

After some time, depending on the size of the database, the compression will be completed.

Compress Database using the SHRINKDATABASE and SHRINKFILE instructions.

In MS SQL Server, two SHRINKDATABASE and SHRINKFILE statements are available for compressing the database and transaction log files.

DBCC SHRINKDATABASE is a command to compress the database;
DBCC SHRINKFILE — With this command, you can compress some database files ( for example, just the transaction log ).

In order to perform database compression ( for example, TestBase ), just like we did a little earlier in Management Studio, execute the following instruction.

DBCC SHRINKDATABASE (N'TestBase ')

SHRINKDATABASE has the following parameters:

database_name or database_id is the name or identifier of the database to be compressed. If you specify a value of 0, the current database will be used;
target_percent – percentage free space that should remain in the database after compression;
NOTRUNCATE — Compresses data in files by moving distributed pages from the end of the file to the unallocated pages at the beginning of the file. If this parameter is specified, the physical file size does not change;
TRUNCATEONLY – frees up all free space at the end of the file to the operating system, but does not move pages within the file. The data file is reduced only to the last selected extent. If this parameter is specified, the target_percent parameter is not processed;
WITH NO_INFOMSGS – suppresses all informational messages with severities from 0 to 10.

Syntax SHRINKDATABASE

  DBCC SHRINKDATABASE   
  (database_name | database_id | 0   
       [target_percent]   
       [, {NOTRUNCATE | TRUNCATEONLY}]   
  )  
  [WITH NO_INFOMSGS]

In order to compress only the transaction log in SQL Server, you can use the SHRINKFILE statement , for example.

DBCC SHRINKFILE (N'TestBase_log ')

In this case, we will compress the log file ( TestBase_log is the name of the transaction log file ), to its initial value, i.e. to default value. To compress a file to a certain size, specify the size in megabytes by the second parameter. For example, with the following instruction we will reduce the size of the transaction log file to 5 megabytes.

  DBCC SHRINKFILE (N'TestBase_log ', 5)

Also note that if you specify a size smaller than what is required to store data in the file, the file will not be compressed to that size. For example, suppose if you specify 5 megabytes, and 7 megabytes are required to store data in a file, the file will only be compressed to 7 megabytes.

SHRINKFILE also has NOTRUNCATE and TRUNCATEONLY parameters.

Syntax SHRINKFILE

  DBCC SHRINKFILE   
  (  
      {file_name | file_id}   
      {[, EMPTYFILE]   
      | [[, target_size] [, {NOTRUNCATE | TRUNCATEONLY}]]  
      }  
  )  
  [WITH NO_INFOMSGS]

Conclusion

The blog covers the brief introduction on Compression in SQL Server. Also it covers two different ways on how to compress database files and shrink transaction log file in SQL Server.