question

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.

Hi @Bobsql-8788,

• 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.

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.