SQL Server Performance Forum – Threads Archive

Transaction Log problem

I restored a SQL server 2000 (SP3) database that is being replicated from a new sql server instance. Replication was broken from this new instance and the database is currently in full recovery model. I was trying to backup the log using: backup log [mydatabase] with truncate_only But I am getting below error:
"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’ve done everything to manually remove any replication info such as:
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1 sp_removedbreplication ‘[mydatabase]’ and other system sp. Any idea on how can I get rid of the transaction log? I am thinking of shifting from full to simple, create a full backup of my database and delete the transaction log afterwards. Is this a good idea? Jon M

you could try detaching the database then re-attach it but use sp_attach_single_file_db to get SQL Server to create a nice new replication-free log. Tom Pullen
DBA, Oxfam GB

Are you using ‘Sync with backup’ option on your replication databases?
You can deploy what Tom referred and if you’re deploying SIMPLE recovery model then ensure full database backup are scheduled frequently. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks. I detached/re-attached and it worked. Jon M

Do you think detaching attaching this way, will work fine, even if I have multiple datafiles. Will specifying the first MDF file enough ?

You must specify all qualifying files such as .MDF & .NDF in order to reattch the database.
But in this case check if you’re using ‘Sync with backup option’ then try to avoid the backup clash between replication process. Ensure transaction log is sized with proper settings. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>