Transaction Log is full

Question

Hello,

what are the options if one has a full transaction log, can we create a transaction backup in the Azure portal? As far as we figured out neither are the sql statements BACKUP LOG or TRUNCATE available in SSMS.

It is also not possible to shrink the log file (DBCC SHRINKFILE ([log], 8);), as we are getting the following error

"The transaction log for database **** is full due to 'REPLICATION'"

such that no update commands are possible.

Thanks in advance
Asri

Answer 1

If Change Data Capture is enabled, that log full may mean that CDC is presenting errors. Please execute a SELECT on DMV (management view) sys.dm_cdc_errors. Try to resolve the errors shown by that DMV. If you are unable to solve the errors, create a support ticket or proceed to stop data capture at the table level (if you can identify one table giving errors) or at the database level.

Answer 2

Hi @Asri Ferati ,

Welcome to Microsoft Q&A!

The transaction log for database is full due to 'REPLICATION

You should see log_reuse_wait_desc displayed as Replication. Please read this article Log Reuse Waits Explained: REPLICATION: https://sqlity.net/en/1846/log-reuse-waits-explained-replication/

If you configured replication in your environment, DBCC OPENTRAN can give you the information about active open transactions. To see which database still needs to be replicated use: DBCC loginfo.
You can reset this by first turning the Reader agent off and you also can turn the whole SQL Server Agent off instead. Run that query on the database for which you want to fix the replication issue:
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time= 0, @reset = 1
Close the connection where you executed that query and restart SQL Server Agent (or just the Reader Agent). You should be all set to shrink your db now.

If you have enabled CDC, please refer to this article: https://mitchellpearson.com/2015/01/26/the-transaction-log-for-database-is-full-due-to-replication-replication-not-enabled-cdc/

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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 3

Hi,

To discover what is preventing log truncation please execute the following query:

SELECT [name], log_reuse_wait_desc FROM sys.databases;  

If you get "NOTHING" then all is fine

In your case, you will probably get something which indicate the issue.

If you get "CHECKPOINT" or "LOG BACKUP" or "ACTIVE BACKUP OR RESTORE" or "AVAILABILITY_REPLICA", then you have nothing to do directly but trying to use bigger tier or contact the Azure team (open ticket) since these issues related to the instance level and you are using a database level service.

If you get "ACTIVE TRANSACTION" then you probably have a long-running or blocked transaction. Kill these or wait for them to finish is usually the solution.

If you get "REPLICATION" then you probably have issue with change data capture (CDC) feature. You should query sys.dm_cdc_errors for more information.

Answer 4

@Seeya Xi-MSFT Thanks for your reply but unfortunately we had to delete the whole database and use a backup since we could not solve the problem and we did not have more time to figure out how to repair the database.

All our researches were not helpful, we could not execute the suggested operations since it was often the case that it was not supported by Azure.

The problem that our transaction log was full most probably occurred because we were sending JSON-Files to the database via stored procedures for Data Migration.

I found it confusing that there were no warnings on the azure platform that we might get in trouble because of our decreasing transaction log space.