question

Bobsql-8788 avatar image
0 Votes"
Bobsql-8788 asked ·

Transaction log is full 9002 error

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

sql-server-general
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Shashank-Singh avatar image
1 Vote"
Shashank-Singh answered ·

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


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered ·

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.





· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Is there a proper way of capturing sql stmts which are causing the log file to grow abnormally ?

0 Votes 0 ·

In this case it was the session that was left orphaned that prevented the log from being truncated. But had there been no activity in the database, the log would not have grown. Thus, it was a combination of statements.

1 Vote 1 ·

Unless you have specific script that would not be possible, you need to proactive. If you wait the information may be lost. You have default trace and extended event that can let you know.


0 Votes 0 ·
CarrinWu-MSFT avatar image
1 Vote"
CarrinWu-MSFT answered ·

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.


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Bobsql-8788 avatar image
0 Votes"
Bobsql-8788 answered ·

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.

74765-image.png



image.png (149.7 KiB)
· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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


1 Vote 1 ·
0 Votes 0 ·