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

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'

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.

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