Go SQLServer2008 log file cannot shrink processing method

Problem Description
found that the database log file is too large, no matter how many times the contraction execution of SQL statements. Transaction log up to 30+g, and the use of conventional truncation, shrinkage method can not reduce the size of the log physical file, after some search, finally found a solution.

Viewing log information
Execute the following code in Query Analyzer to view the log information: DBCC loginfo (' Database name ')
We see status=0 logs, which represent log files that have been backed up to disk, and status=2 logs are not backed up. When we shrink the log file, the shrinking space is actually status=0 space, if the log physical files can not be reduced, there will be a lot of status=2 to see the record. And then we'll analyze why there are so many status=2 records.

View Log Truncation latency reasons
Active (Active) logs cannot be truncated by shrinking, and there are various reasons why the log truncation delay is due to the fact that the transaction log's physical files cannot be reduced by truncation, shrinking, and the following code can see the reason for the log truncation delay for each database on the instance:

use goSELECT  name, database_id, log_reuse_wait, Log_reuse_wait_desc   from sys.databasesGo

various reasons and explanations are as follows: Log_reuse_wait_desc Value Description
Nothing
There is currently one or more reusable virtual log files.

CHECKPOINT
The checkpoint has not yet occurred since the last log truncation, or the log header has not been moved across a virtual log file (all recovery models).
This is a common cause of log truncation delays. For more information, see the active section of checkpoints and logs.

Log_backup
A log backup is required to forward the head of the log (only for the full or bulk-logged recovery model).
Note: Log backups do not interfere with truncation. After the log backup is completed, the head of the log is moved forward, and some log space may become reusable.

Active_backup_or_restore
Data backup or restore is in progress (all recovery models).
The data backup runs the same way as the active transaction. Data backup will block truncation at run time.

Active_transaction
The transaction is active (all recovery models). A long-running transaction may exist at the beginning of a log backup. In this case, another log backup may be required to free up space. For more information, see the "long-running Active transactions" section later in this topic.
Transactions are deferred (only for SQL Server 2005 Enterprise Edition and later). A "deferred transaction" is a valid active transaction, because some resources are not available and their rollback is blocked. For information about the causes of transaction delays and how to get them out of the deferred state, see deferred transactions.

Database_mirroring
Database mirroring is paused, or in high-performance mode, the mirrored database lags significantly behind the principal database (Full recovery model only).

REPLICATION
During transactional replication, the transactions related to the publication are still not delivered to the distribution database (Full recovery model only). For more information, see the "Transactional Replication and transaction log" section later in this topic.

Database_snapshot_creation
Creating database Snapshot (All recovery models). This is a common cause of log truncation delays and is often the main cause.

Log_scan
A log scan is in progress (all recovery models). This is a common cause of log truncation delays and is often the main cause.

partial solution for delayed log truncation reasons
Log_backup
Back up the log and then do the shrink. BACKUP log [database] with Nolog

REPLICATION
I have encountered this situation two times, but I have not enabled the REPLICATION, it is found that this is a SQLSERVER2008 bug, the solution is to the "REPLICATION" of the database is any table to create a database transaction replication (TRANSACTION REPLICATION), and then delete, after performing the database and log backup, it can be shrunk.

Attached, SQL Server shrink log:

Http://www.cnblogs.com/zhaoguan_wang/p/4949176.html

Go SQLServer2008 log file cannot shrink processing method