Troubleshooting for “The transaction log for database ‘database1’ is full due to ‘REPLICATION'”

Problem

I have a dev/test server B on which one database [database1] was restored from the same name database on production server A. [database1] on server A is the published database in a transnational replication. The recovery mode is simple for [database1] on both servers.

Recently I have received an alert from server B:

“Title: SQL Server Alert System: ‘server B – Sev:017 – INNSUFIFIENT RESOURCES’ occurred on \\server B 

DESCRIPTION:   The transaction log for database ‘database1’ is full due to ‘REPLICATION’.”

Solution

First I checked the transaction log file size and available disk space, the log is 200GB, and the drive is almost full. The recovery mode of [database1] is simple, which means the transaction log should be truncated automatically.  However, some factors may delay the truction, they are explained in the online book:                           https://msdn.microsoft.com/en-CA/library/ms190925.aspx#FactorsThatDelayTruncation

Run this script (script1) to get the factor:

SELECT [name]
,[database_id]
,[log_reuse_wait_desc]
FROM [sys].[databases]
where name = ‘database1’

The result is

sample1

The factor delayed the truncation is REPLICATION. But there is no replication setup on this server at all. I verified that by running this script:

SELECT [is_published]
,[is_subscribed]
,[is_cdc_enabled]
FROM [sys].[databases]
where name = ‘database1’

sample2

Then I checked the transaction log file usage by running the command: “DBCC SQLPERF(logspace)”. The result of “log Space Used(%)” is more than 99%, which means it can’t be shrunk to a reasonable size.

I tried stored procedure sp_removedbreplication:  

exec sp_removedbreplication ‘database1’

It did not remove the replication flag on database. Restoring from a published database may just bring partial information of the replication, enough for SQL Server not to truncate the tranction log, not enough for sp_removedbreplication to find and remove it.

So the solution is to add the database to publication, then remove it. Right click Replication in SSMS, then click Publisher Properties. Click Publication Databases in the Publisher Properties window, and check the Transactional button besides [database1], then OK.

sample3

sample4

Ran script1 again and found the value of [log_reuse_wait_desc] changed to “NOTHING”, which means nothing will delay the truncation of transaction log. Ran “DBCC SQLPERF(logspace)” again, “log Space Used(%)” is 0.09. Then you can just shrink the transaction log file and solve the problem. The last step is to go back to Publisher Properties, and uncheck the Transactional button besides [database1].

Advertisements

Published by

Zhihua Wang

Zhihua Wang is a SQL Server Database Administrator.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s