SQL2008R2 shrinking database problems-log files are not small

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

Shrink it. Shrinking the operation of the log file is really not easy, but also with the database recovery model.

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

1. Truncate the log

When the recovery model for a database is "simple", the log file is 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. For an explanation of the log file space status, see http://jimshu.blog.51cto.com/3171847/1174474

You can manually shrink the log files by following the instructions later in this article.

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.

Even in the simple recovery model, you can directly modify the "Initial size" of the log file to a small number (the system automatically shrinks the log file to the lowest possible value).

Note : After changing back to the full recovery model, be sure to make a full backup immediately.

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. Note : Some data may be lost, see the risk of missing log files and Countermeasures http://jimshu.blog.51cto.com/3171847/1341289

SQL2008R2 Shrinking database problems-log files are not small