Some common reasons why the transaction log cannot be reused automatically

Managing SQL Server databases may sometimes be challenging especially when it comes to dealing with transaction log file internal space usage. More specifically, today I am going to talk about SQL Server wait types that may prevent SQL Server from automatically reusing transaction log internal space and therefore resulting in running out of space and affecting detrimentally on the availability of the database. For instance, if the recovery model of the database is either Full or Bulk-Logged then you may see a LOG_BACKUP wait type while the next backup log is waited to be run. Consequently, if those databases do not have Log Backups tasks, their transaction log files will be growing without control and when they take all the disk space available the databases will stop working until the internal space of the transaction logs is truncated by executing log backups manually.

Furthermore, even using SIMPLE recovery model the transaction log file might not be truncated automatically because of open transactions, that is why it is essential to execute a COMMIT explicitly and not to use IMPLICIT transactions. Being cognisant of this fact, I am sold on the idea of having total control of the scope of transactions so as to avoiding these issues. Another reason why the transaction log file internal space may not be reused is due to CHECKPOINT wait type when the database has AUTOMATIC CHECKPOINT disable. In this case it is needed to execute a CHECKPOINT command manually. I heartedly recommend not disabling AUTOMATIC CHECKPOINT for any database. 

Additionally, with the arrival of SQL Server AlwaysOn, the HADR_SYNC_COMMIT wait type arrived as well. This wait type will be found while an AlwaysOn Availability Group secondary replica is trying to apply or is applying transaction log records of this database to a corresponding secondary databases. However, this wait type might cause a major problem if it is unusually long because the AlwaysOn Availability Group is not working properly as a result of either network issues or at last one secondary replica is slow in log hardening. Thus, it is of paramount importance to ensure Always On Availability Group as a whole is working smoothly and as expected, otherwise there will be no way to truncate the transaction log file and it will indeed risk the availability of the database. Using this code, we can check the wait types for all databases and particularly the HADR_SYNC_COMMIT wait type:

select session_id, status,command,blocking_session_id, wait_type, wait_time, last_wait_type 
from sys.dm_exec_requests where session_id>=50
Go
select wait_type, waiting_tasks_count, wait_time_ms
from sys.dm_os_wait_stats 
and wait_type = 'HADR_SYNC_COMMIT'

Here I share with you the complete list of wait types available in many modern SQL Server engines:
0 = Nothing
1 = Checkpoint (When a database uses a recovery model and has a memory-optimized data filegroup, you should expect to see the log_reuse_wait column indicate checkpoint or xtp_checkpoint.) Applies to SQL Server 2008 through SQL Server 2017
2 = Log Backup. Applies to SQL Server 2008 through SQL Server 2017
3 = Active backup or restore. Applies to SQL Server 2008 through SQL Server 2017
4 = Active transaction Applies to SQL Server 2008 through SQL Server 2017
5 = Database mirroring. Applies to SQL Server 2008 through SQL Server 2017
6 = Replication. Applies to SQL Server 2008 through SQL Server 2017
7 = Database snapshot creation. Applies to SQL Server 2008 through SQL Server 2017
8 = Log scan Applies to
9 = An Always On Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database. Applies to SQL Server 2012 through SQL Server 2017. In earlier versions of SQL Server, 9 = Other (Transient).
10 = For internal use only Applies to SQL Server 2012 through SQL Server 2017
11 = For internal use only Applies to SQL Server 2012 through SQL Server 2017
12 = For internal use only Applies to SQL Server 2012 through SQL Server 2017
13 = Oldest page Applies to SQL Server 2012 through SQL Server 2017
14 = Other Applies to SQL Server 2012 through SQL Server 2017
16 = XTP_CHECKPOINT (When a database uses a recovery model and has a memory-optimized data filegroup, you should expect to see the log_reuse_wait column indicate checkpoint or xtp_checkpoint.) Applies to SQL Server 2014 through SQL Server 2017
More info: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql

That is all for now, let me know any remarks you may have. Stay tuned.