Troubleshooting the full transaction log problem

One my database’s log file is increasing continuously. Database size is 5 GB while log file has exceeded 50 GB.

Now to resolve the issue the first thing I need to know is why the log file is increasing?

SQL Server provide us the exact information in sys.databases system catalog view. Execute the below statement and you would get the answer in “log_reuse_wait_desc” column.

SELECT log_reuse_wait_desc, * FROM sys.databases

 

All the possible reasons and there corrective actions are as following:

log_reuse_wait value	log_reuse_wait_desc value	Description
1	CHECKPOINT	In the database execute the command CHECKPOINT
2	LOG_BACKUP	Take two log backups. TRUNCATE_ONLY can be used if not need later for restore purpose.
3	ACTIVE_BACKUP_OR_RESTORE	A data backup or a restore is in progress (all recovery models). Wait until the data backup or restore process completes.
4	ACTIVE_TRANSACTION	A transaction is running. Wait until the active transaction completes. If transaction is deferred then remove that.
5	DATABASE_MIRRORING	If database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database (full recovery model only). Resolve the mirroring issue.
6	REPLICATION	If transactions relevant to the publications are still undelivered to the distribution database. Resolve the replication issue.
7	DATABASE_SNAPSHOT_CREATION	A database snapshot is being created (all recovery models). Wait until the snapshot completes.
8	LOG_SCAN	A log scan is occurring. This is a routine. Wait until scan completes.