MSG 9002 the transaction log for Database "is full

Today, a friend reported an error in his database. The error message is as follows:

 

MSG 9002, Level 17, state 2, line 4
The transaction log for database ''is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in SYS. Databases

 

This error is very common. The log file is full, but there are many reasons for the full log. How can this problem be found? In fact, this error has already prompted us a lot to query the log_reuse_wait_desc column in SYS. Databases. query SYS. Databases:

 

Select log_reuse_wait_descfromsys.databaseswhere [name] = 'database ';

The value is log_backup, which indicates that you need to run log backup to solve this problem. Therefore, perform a log backup for the database:

 

Backuplog databaseatodisk = 'd: \ MSSQL \ databaseackup. trn ';

After the backup is complete, the problem is solved, and then check log_reuse_wait_desc. The value is "nothing" (currently one or more reusable virtual log files are available .)

 

Below I will list all the values of log_reuse_wait_desc and their corresponding descriptions (factors that may delay log truncation)

 

Log_reuse_wait Value	Log_reuse_wait_desc Value	Description
0	Nothing	There are one or more reusable virtual log files.
1	Checkpoint	Check points have not occurred since the last log truncation, or the log header has not been moved across a virtual log file (all recovery modes ). This is a common cause of log truncation latency. For more information, see vertices and log activity.
2	Log_backup	Log backup is required to forward the log header (only applicable to full recovery mode or large-capacity log recovery mode ). Note: Log backup does not impede truncation. After the log is backed up, the log header is moved forward, and some log spaces may become reusable.
3	Active_backup_or_restore	Data backup or restoration is in progress (all recovery modes ). Data backup is run in the same way as the active transaction. Data backup stops truncation during running. For more information, see the "data backup and restore operations" section after this topic.
4	Active_transaction	The transaction is active (all recovery modes ). · A long-running transaction may exist at the beginning of 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 after this topic. · The transaction is delayed (only applicable to SQL Server 2005 Enterprise Edition and later ). A delayed transaction is a valid active transaction. rollback is blocked because some resources are unavailable. For information about the causes of transaction latency and how to lift them from the delayed State, see delayed transactions.
5	Database_processing ing	The database image is paused, or in high-performance mode, the image database lags behind the master database (only in full recovery mode ). For more information, see the "database image and transaction log" section after this topic.
6	Replication	In the transaction replication process, publishing-related transactions are not passed to the distributed database (only in the full recovery mode ). For more information, see the "transaction replication and transaction log" section after this topic.
7	Database_snapshot_creation	Creating Database snapshot (all recovery modes ). This is a common cause of log truncation latency and is usually the main cause.
8	Log_scan	Log scanning in progress (all recovery modes ). This is a common cause of log truncation latency and is usually the main cause.
9	Other_transient	This value is not used currently.

 

Another common cause of log full is longrunningtransaction. We can see active_transaction after querying SYS. databases, and then according to DBCC
Opentran identifies spids without commit for processing.

 

The following is the result set for running DBCC opentran:

 

Transaction information for database 'master '.

Oldest active transaction:

Spid (server process ID): 52

UID (User ID):-1

Name: user_transaction

LSN: (518: 1576: 1)

Start Time: Jun 1 2004 3: 30: 07: 197pm

Sid: 0x010500000000000515000000a065cf7e784b9b5fe77c87709e611500

DBCC execution completed. If DBCC printed error messages, contact your system administrator.