When a transaction log fills, something is stopping SQL Server from reusing log space. Find that cause first, because shrinking the log does not fix a full log.

Step 1: Find why the log can’t be reused

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = N'YourDB';

Step 2: Check log size and usage

USE [YourDB];
SELECT total_log_size_in_bytes / 1048576.0 AS total_mb,
       used_log_space_in_bytes / 1048576.0 AS used_mb,
       used_log_space_in_percent
FROM sys.dm_db_log_space_usage;

For every database at once: DBCC SQLPERF(LOGSPACE);

Step 3: Check log file growth settings

USE [YourDB];
SELECT name, physical_name,
       CAST(size AS bigint) * 8 / 1024 AS size_mb,
       CASE WHEN max_size = -1 THEN 'Unlimited'
            ELSE CAST(CAST(max_size AS bigint) * 8 / 1024 AS varchar(20)) END AS max_size_mb,
       CASE WHEN is_percent_growth = 1 THEN CAST(growth AS varchar(10)) + '%'
            ELSE CAST(CAST(growth AS bigint) * 8 / 1024 AS varchar(20)) + ' MB' END AS growth
FROM sys.database_files
WHERE type_desc = 'LOG';

Step 4: Check the last log backup

SELECT TOP 1 backup_finish_date
FROM msdb.dbo.backupset
WHERE database_name = N'YourDB' AND type = 'L'
ORDER BY backup_finish_date DESC;

Step 5: Find open transactions holding the log

USE [YourDB];
DBCC OPENTRAN;

SELECT s.session_id, s.login_name, s.host_name, s.program_name,
       dt.database_transaction_begin_time,
       dt.database_transaction_log_bytes_used / 1048576.0 AS log_mb_used
FROM sys.dm_tran_database_transactions dt
JOIN sys.dm_tran_session_transactions st ON dt.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
WHERE dt.database_id = DB_ID(N'YourDB')
ORDER BY dt.database_transaction_begin_time;

Step 6: Check the Availability Group (if log_reuse_wait_desc is AVAILABILITY_REPLICA)

SELECT ar.replica_server_name, drs.synchronization_state_desc,
       drs.is_suspended, drs.suspend_reason_desc,
       drs.log_send_queue_size, drs.redo_queue_size, drs.last_hardened_time
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
WHERE drs.database_id = DB_ID(N'YourDB');

What to do for each log_reuse_wait_desc value

• LOG_BACKUP: take a log backup. Check that log backup jobs are running and succeeding.
• ACTIVE_TRANSACTION: identify the session in Step 5, then commit it or kill it, with the owner’s agreement.
• AVAILABILITY_REPLICA: a secondary is behind or suspended. Fix the replica before anything else.
• REPLICATION: check the Log Reader Agent is running.
• ACTIVE_BACKUP_OR_RESTORE: wait for the backup or restore to finish.
• CHECKPOINT: run CHECKPOINT; in the database.
• NOTHING: the log can be reused now. If the disk itself is full, deal with disk space.

Never, without approval

• Shrinking the log as a fix.
• Switching to SIMPLE recovery. It breaks the log backup chain, and a full backup is needed afterwards.
• Emergency only: adding a second log file on another volume. Rollback: remove it with ALTER DATABASE … REMOVE FILE once it’s no longer in use.

From my SQL Server DBA handbook.