Transaction log is full 9002 error

Question

Hi SQLTeam,

Today we have been receiving continuous log full 9002 alerts. The drive is completely full with 10mb free space.
Identified an open transaction and associated sql statement which is been run from past 2 days and in sleeping mode doing nothing.
The database is in FULL recovery models and transaction log backup is scheduled every 1 hour, db size is around 3TB. Log file grown to 1TB.

Here are some clarifications I am looking for :
When transaction log is full,

1) What happens for the incoming transactions for that particular database?
2) Are there any side affects when always on and log shipping is configured for this particular database ?

Thanks,
Bob

Answer 1

Are there any side affects when always on and log shipping is configured for this particular database

For logshipping no assuming it is running properly. For AG yes. Now this largely depends on AG configuration, number of replicas and bandwidth and load on bandwidth between primary and secondary replica. For AG it is said you are only as fast as your slowest replica. So if everything is fast and your one of replica is slow it will slow the data flow hence causing log file to grow fast on primary.

EDIT:From your follow up question

But question is, it was showing up AVAILABILITY_REPLICA" log_reuse_description for almost more than 5 hours.

2 Things

1. Large transaction of primary which is being sent to secondary and their is network latency
2. A blocked REDO on secondary.

My hunch is 2nd option. A large REDO or blocked REDO will not allow the REDO to complete instantaneously and may affect incoming transactions. See below thread

log-truncation-issue-in-alwayson-ag

Answer 2

1) What happens for the incoming transactions for that particular database?

Every transaction is written to log file first and if the log file is full, the transaction fails immediately.

2) Are there any side affects when always on and log shipping is configured for this particular database ?

If the log shipping don't work properly, the log file will continue to grow.

Answer 3

Hi @Bob sql ,

• What happens for the incoming transactions for that particular database?
  The transaction log is a wrap-around file. New log records are added at the end of the logical log and expand toward the end of the physical log. The new log records will fail to write into transaction log if the files are full. Please refer to SQL Server Transaction Log Architecture and Management Guide to get more information
• Are there any side affects when always on and log shipping is configured for this particular database ?
  Log shipping will not truncate transaction log, it simply marks the portions of the log that have been backed up as available for re-use. So the log file will still continue to grow if the log shipping don't work properly.

--If you would like to locate the reason for the large increase in transaction log, please use below T-SQL:
--1.Check the current usage of log and database states

DBCC SQLPERF(LOGSPACE)   
GO   
SELECT name, recovery_model_desc, log_reuse_wait,log_reuse_wait_desc    
FROM sys.databases   
GO  

--2.Check the Active Log

DBCC OPENTRAN   
GO   
SELECT st.text,t2.*   
 FROM sys.dm_exec_sessions AS t2, sys.dm_exec_connections AS t1   
CROSS APPLY sys.dm_exec_sql_text (t1.most_recent_sql_handle) AS st        
WHERE t1.session_id = t2.session_id  
AND t1.session_id >50  

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi Carren,

The log_reuse_wait_desc was initially showing "Active_Transaction". Then we had to check with the application team and saw some of the spids are running over 4 days with sleeping status and open_tran = 1. So, we had to kill those spids. Then it showed up LOG_BACKUP , we have taken the log backup, then shows "AVAILABILITY_REPLICA".

I checked the AG Dashboard on all the 2 replicas and same it was all green. Meaning everything was in Sync. I even checked by add/remove columns in the AG Dashboard to check to see if any sendqueue is there or not. I dont see any lag.

But question is, it was showing up AVAILABILITY_REPLICA" log_reuse_description for almost more than 5 hours. I have no clue what as going on. Is there a way to tell if some sync is happening ? In AG Dashboard it is all green and not delay. Don't know what was exactly happening during that 5 hours. is there a dmv or query to check if anything is pending or something was happening during those 5 hours ? We didnt see any errorlogs though. Eventually, we had to failover to the other replica to check to see any issue. Even then it was showing 'AVAILABILITY_REPLICA' for more than 40 mins and then it allowed us to SHRINK the log file to release some space to OS.

The log drive is separate 2TB disk. usually the log size used to be 350GB , all of a sudden it has grown to 2TB filling entire disk space.