The Cause and solution of the full transaction log in SQL Server

The Cause and solution of the full transaction log in SQL Server

Error Description: The transaction log of the database is full. To find out why the space in the log cannot be reused, see the log_reuse_wait_desc column in sys. databases.

First, introduce the concept of transaction logs (from Baidu encyclopedia ):

Transaction logs are files separated from database files. It stores all changes made to the database and records all insert, update, delete, commit, rollback, and database mode changes. Transaction logs are also called rollback logs or redo logs.

Transaction logs are an important component for backup and recovery. They are also required to use SQL Remote or [Copy proxy] To copy data.

By default, transaction logs are used for all databases. Transaction logs are optional. However, unless you do not use them for special reasons, you should always use them. Running a database with transaction logs can provide enhanced fault protection, better performance, and data replication.

Cause of exception:

A. uncommitted transactions

B. Very large transactions

C. Operation: dbcc dbreindex and CREATE INDEX

D. When restoring from the transaction log backup

E. Client applications do not process all results

F. query times out before the transaction Log is extended. you receive a false "Log Full" error message.

G. uncopied transactions

Solution:

1. Release disk space (applicable to cainiao );

2. Move the database to a disk with sufficient memory (same principle );

3. Clear logs: dump transaction database name WITH NO_LOG;

4. truncate transaction logs: backup log database name WITH NO_LOG;

Problems are the opportunities for us to make progress. These are some solutions for searching on the Internet and hope to help you!