How to shrink the database log file size

The database log file (*.LDF) is getting larger and bigger, what to do?

Shrink it. Shrinking log files is really not easy, it's also about the recovery model of the database.

One, the "Simple Recovery Model" when the log shrinkage

1. Truncate the log

When the recovery model for a database is "simple", the log files are truncated in the following cases:
(1) Full backup

(2) Check Point encountered (checkpoint)

2. Manually shrink the log file

When logs are truncated, the internal space of the log files is marked as "reusable", so the log files do not need to grow continuously. Manual shrink operations can be referred to later in this article for instructions.

Second, the "Full recovery model" when the log shrinkage

Under the full recovery model, the log is not affected by a full backup of the data or a checkpoint. As a result, log files may not be truncated, that is, they cannot be reused. As a result, log files will continue to grow, even to the limit of hard disk space. At this point, even if you manually shrink the log files, the file space is not reduced because the space is occupied.

Note: The bulk-logged recovery model is consistent with the log processing method of the full recovery model.

1. Confirm the Recovery model

The following is a recovery model that uses SQL Server Management Studio to check the database.

2. Full backup

A full backup must be done before the log backup.

Note: do not tick the "Copy backup only" option. Copy-only backup does not affect the log.

3. Transaction log Backups

After you have made a full backup, you can make a transaction log backup at any time.

The default option is to truncate the transaction log after the backup.

Note : There are a number of situations that prevent log truncation, and no backup of the transaction log is the most common case.

4. Manually shrink the log file

After a log backup, the log is truncated and a large amount of space is marked as "reusable." In general, you can reduce the log file by doing a "shrink file" operation to remove the "reusable" space from the log file.

Iii. Unofficial methods of operation

1. Temporarily switch to Simple recovery mode

Temporarily change to Simple recovery mode, then make a full backup, or run checkpoint to perform checkpoints. When logs are truncated, the log files are shrunk, and then the full recovery model is changed back.

Note : After changing back to the full recovery model, be sure to do a transaction log backup again.

2. With NO_LOG option

In older versions, you can use BACKUP LOG with no_log, or with TRUNCATE_ONLY. However, SQL Server 2008 has removed these options.

3, after separation and then attach

Detach the database, and then delete the log file. Then reattach the database, SQL Server warns that the log file is missing, and if this warning is omitted, an empty log file is automatically created.