I have a database that used to be replicated. The transaction log is very large and upon inspection I have determined that there are log entries waiting to be replicated. Seeing as how I do not have replication enabled is there any way that I can truncate these entries without detaching the database and recreating the tlog from scratch? Taking the db offline is a last resort option.

Replication is not even enabled on the new server. This database was created using a backup copy from a server that had replication enabled. The server that it is on now does not use replication. I am not sure what else I need to do to this database to make it think it is no longer replicated.

Any thoughts?

sp_dboption comes back with no replication configured.

Also when I run: (Which I found from another post)

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

I receive the following results:

Server: Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
The database is not published.

Yes,

backup log NLG_INBOX with truncate_only

returns the following:

"The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log
Reader Agent is running or use sp_repldone to mark transactions as distributed."

I seem to be in a catch 22.

sp_repltrans also produces the same results:

Server: Msg 18752, Level 16, State 1, Procedure sp_repltrans, Line 1
The database is not published.

Here is the output from sp_dboption:


auto create statistics
auto update statistics

SQL 2000 SP3a

Try this as your last option...

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1 -- on that database

this would reset all log entries for transactions pending for replication.

After this change the property of you database to 'truncate log on check point' open query analyzer issue command checkpoint on that database.

now use dbcc shrinkfile or your EM to reduce the log file size.

Why don't you backup your database and restore it with an empty log?

Kalman Toth, MCDBA
http://www.sqlusa.com/datawarehouse/

Have you tried?:

EXEC sp_removedbreplication 'dbName'

You should run this on any/all databases that were either subscribers or publishers.

I had exactly this problem today (came across your post googling for an answer).

I found a solution - Detach the database, rename the LDF, and then Attach again. It will alert you to the fact that there's no log file, and will create a fresh one for you.

After doing this, I ran DBCC OPENTRAN, which previously showed heaps of unreplicated transactions, and it now shows none, even after some data updates. And the log file has shrunk from 1Gb to 700k.

Peter

I had this same exact problem. The Detach / Attach solution worked perfectly. Thank you so much.

Hunter

Do the following



Run sp_replicationdboption 'database name','publish',true'

then run sp_repldone with the following parameters

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

then unpublish the database

sp_replicationdboption 'database name','publish','false'



Run

dbcc traceon(3604)

dbcc opentran --- against the database to ensure there are no replicated transactions.



At this point you should be able to truncate the log