SQL replication - Publication deleted, but log file still growing

Question

To preface, I've seen the command to answer this question before, but now I can't find it again, so I'm just looking for the single SQL statement that will solve my problem.

I had two publications on a SQL Server 2000 database at one point, but I've since deleted them. However, my log file is growing, and appears to contain unreplicated transactions, and is growing without end. I've tried this:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

I get a message that "The database is not published" (and since I've deleted the publication, that makes sense). If I try:

backup log dbname with truncate_only

I get the message that there are unreplicated transactions in my log, and it won't truncate.

I've seen this before, where no publications existed, but the database was marked as still participating in replication, and I found a single line script to un-flag the database as a source for replication, which immediately resolved my problem. I can't find it now, though, when I need it again - hopefully one of you can shed some light. Thanks!

Answer 1

Are you looking for:

EXEC sp_replicationdboption 
    @dbname = @publicationDB, 
    @optname = N'merge publish', 
    @value = N'false'

?

Also see sp_dropmergepublication / sp_droppublication.

Answer 2

I was unable to purge this not-yet-replicated data through any supported method, so I had to forcefully rebuild the log file. It's SQL 2000, so there's an unsupported/undocumented SP to do this:

DBCC REBUILD_LOG('DBName','D:\Log folder\Logfile name.ldf')

This will create a new, empty logfile for the database and abandon the old one. Note that this will forcefully truncate the current transactions, so make sure you have a backup. The database needs to be in "Emergency Recovery" mode to use this command, as it will not roll back any in-process transactions that have been partially applied to the data, potentially damaging data integrity.

For full details about the process I used, see post 7 from this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76785