Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In this article, I will describe a few of the most known types and solutions of this error.
Possible Errors
- Transaction Log is Full Due To LOG_BACKUP
- Transaction Log is Full Due To ACTIVE TRANSACTION
- Transaction Log is Full Due To REPLICATION
You can see why the Transaction Log file is FULL with the following script. I will explain the reasons and solutions of the three error I mentioned above.
1 | select log_reuse_wait_desc from sys.databases where name='your_db_name' |
Transaction Log is Full Due To LOG_BACKUP
If the transaction log is Full due to log_backup, it indicates that you used full recovery model, and do not backup transaction log regularly. To resolve this issue, you must backup your transaction log and then shrink. You can read the following articles for detailed information. Do not forget that you must backup transaction log file regularly when you use Full Recovery Model.
“Database Recovery Models in SQL Server“,
“How To Shrink the Transaction Log”
Transaction Log is Full Due To ACTIVE_TRANSACTION
I have experienced this problem in the Availability Group’s primary database. This is one of the rare problems I have experienced in the Availability Group until today.
When this error occurs, the Transaction Log file was FULL even though a Transaction log is backed up.
ACTIVE_TRANSACTION means that there is an active transaction in the database. Because of this, SQL Server can not truncate the transaction log file. To find out if this is really the case, you should run the following query and find out whether there is a long running query. If you have a long-running query, you can kill the query by talking to the application developer. I suggest that you do not kill the query without the application developer’s knowledge.
1 2 3 4 5 | SELECT sp.spid,st.text,sp.status,sp.login_time,sp.last_batch, sp.hostname,sp.loginame FROM sys.sysprocesses sp CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) st WHERE open_tran = 1 and sp.dbid=DB_ID('your_db_name') order by login_time asc |
If the reason of the problem is ACTIVE_TRANSACTION and if there is no active transaction in the database and your database is include in an availability group, then follow the steps below to solve your problem.
When I first encountered this problem, I solved the problem by removing the database from AG and taking it back to AG. But the problem has repeated itself several times. Because the database size is also large, I did not want to remove it from the AG each time.
Then, I tried a method like below and it worked.
First, we click the Suspend Data Movement in the primary database by right-clicking on the relevant database as follows.
Then back up the transaction log and click the Resume Data Movement by right-clicking on the relevant database in the primary node as follows.
After doing this, the ACTIVE_TRANSACTION problem still persists for a while. When you go to the Secondary database and look at the sync status, you see “Not Synchronizing / In Recovery” as follows.
When you open the log file of the Secondary database, you will see that the database is trying to recover itself as follows.
When the recovery process you see in the screenshot above is complete, run the following script in the primary database.
1 | select log_reuse_wait_desc from sys.databases where name='your_db_name' |
If the result is still ACTIVE_TRANSACTION, this means that your secondary database is still behind.
Right click on the AG and click on the SHOW Dashboard, then click Add / Remove Columns from the area marked in the screenshot below to select Last Hardened Time and Last Commit Time.
Last Hardened Time shows when the last log record was sent from primary database to secondary database. When I looked at it, it was up-to-date.
Last Commit Time was almost 2.5 days behind. Since the Dashboard screen is automatically updated, you’ll see Last Commit Time progressing continuously. After a while, the Last Commit Time will catch up to date and the secondary database will switch to the Synchronized state and your problem will fixed.
When you run the query again, the result will now appear as NOTHING and the transaction log file will be shrinkable. You can complete the process by shrinking the transaction file. To shrink the transaction log file, you can use the article “How To Shrink SQL Server Transaction Log“.
Transaction Log is Full Due To REPLICATION
I wrote an article about this topic named “SQL Server Does Not Truncate the Transaction Log After Log Backup and Can Not Shrink the Transaction Log“. You can find detailed explanation and solution in this article.
