Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a non Mission Critial DB 9am-5pm SQL Server database that I have set up to do nightly full backups and log backups every 30 minutes during business hours. The database is in full recovery and normally I have no reason to truncate/shrink logs unless I do some heavy maintenance. Log backups manage the size with no issue. However I have not been at this client for several weeks and upon inspection I noticed that the log had grown to about 10 times the size of the .mdf file. I poked around backups had been running and I had not gotten any severity error alerts (SQL mail). I attempted to put DB in simple recovery and shrink the log, this was no good. I precede to try a log backup and I got:

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

Restart SQL Server rinse repeat same thing ...

I said ??? Replication is not nor ever has been set up on this DB or database /server ??? So the log backups have not been flushing the .ldf. So I did a couple hours of research and I found:

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/5445/Log-file-is-not-truncated-inspite-of-regular-log-backup

http://www.eggheadcafe.com/software/aspnet/30708322/the-log-was-not-truncated-because-records-at-the-beginning-of-the-log-are-pending-replication.aspx

seems to be some kind of poorly documented bug ??

The solution seems to have been to run exec sp_repldone, more precisley

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

This procedure can be used in emergency situations to allow truncation of the transaction log when transactions pending replication are present. Using this procedure prevents Microsoft SQL Server 2000 from replicating the database until the database is unpublished and republished. ~ MSDN

When I do that I get the following

Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1 Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.

Which makes sense Because the DB has never been published for replication.

I have several questions:

A) First and foremost is, WTF is going on ? What is causeing this, I am interested in knowing the why here ? Is this genuinley a bug or is there some aspect of the backup that is not functioning properly that cause's the DB to mimick a replicated state ? Someone please edify me on this.

B) Second ... Do I really have to publish / replicate this DB to exec this SP to fix this ??? Sounds crazy or is there some T-SQL that I can put it in a published state exec the proc and be on my way ...

C) Third, if I do indeed have to publish this database to exec the SP to release this unneeded mis replicated/intended log , to get my .ldf file and backup back on track. How do I publish the database without an online host that it is asking for ??? I don't generally do this kind of database administration and need some guidance.

Sorry if this is too verbose but just voicing the question helps me clarify it ...

Thank you in advance for your help

share|improve this question
    
@marc_s thankyou –  user796466 Jun 15 '11 at 5:05
    
I'm disappointed that I can't find an explanation for this. For anyone with a similar issue you do have to set up replication on the database and execute sp_repldone ... I have yet to figure out what caused this ? If anyone could shed some light I would appreciate it. –  user796466 Jun 17 '11 at 1:10
add comment

migrated from stackoverflow.com Jun 15 '11 at 5:32

This question came from our site for professional and enthusiast programmers.

5 Answers

We had this problem pretty much exactly. Replication is NOT even installed (SQL 2008) yet in sys.databases, one of our dbs was suddenly and for no reason that I can find, set as being replicated.

We had to do horrible things to recover the db because the log file grew so rapidly and (of course) normal processes for flushing the log did not work.

It was a bit before I realized that replication setting was the problem. By then, the log file was full. Alarmingly, even once we were able to recover the db, that replication setting was STILL set.

This was what removed that setting:

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

share|improve this answer
add comment

Is CDC enabled for database, CDC and Replication use same technology.

1 Run select DATABASEPROPERTY('','ISPublished') -- Should return 0

  1. Check if CDC is enabled for database. Go to Database and select * from sys.dm_repl_traninfo -- This should be empty

3 DBCC loginfo --Look for status column with values 2

  1. DBCC OPENTRAN -- To check if there are some long running transactions.

  2. Even all of these do not help, then we may have to enable replication and e

share|improve this answer
    
Query 1) (repy_traninfo) returns no rows and 2) returns "Replicated Transaction Information: Oldest distributed LSN : (0:0:0) Oldest non-distributed LSN : (129587:143465:1)" –  user796466 Jun 15 '11 at 13:15
add comment

I had the same problem

I did SELECT name, log_reuse_wait_desc FROM sys.databases

and it told me that the cause for no emptying the log was REPLICATION

I tried

sp_removedbreplication youdbname

and that fixed it

share|improve this answer
    
i also had a replication lock on the database and this resolved the issue for me –  BoyMars May 25 '12 at 15:08
add comment

Check to see if the database had CDC (change data capture) or other type of log reader server enabled. This can often cause the exact same issue. I had a production database with CDC enabled that I copied to a development server and kept running into this issue. I have also had CDC enabled production servers that had this error when the CDC agent job had not run recently. The CDC agent job normally runs continuously, but if it fails it will stop and not start again unless you have changed the settings.

Hope this helps.

share|improve this answer
add comment

I had this exact same problem, I found a simpler solution:

  • Detach the database
  • Delete the log file
  • reattach it by using the CREATE DATABASE statement specifying FOR ATTACH_REBUILD_LOG

(note, this is on SQL 2k8)

share|improve this answer
1  
Other versions of SQL server allow a re-attach and rebuild the log. I think sql 2000 is a stored proc, sp_attach_single_file_db. Rebuilding the log isn't something you want to do unless it is REALLY necessary though. –  pipTheGeek Oct 19 '11 at 20:39
    
This is a quick approach but could put the database in a state that can not be opened and would require a restore to fix, which would probably get you back to the original problem. –  Jason Cumberland Nov 19 '11 at 6:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.