log_reuse_wait_desc = replication, transaction log won't stop growing

Question

Hello,

We are using MS SQL Server 2005, version 9.00.3042.00.  Our recovery model is set to SIMPLE.  We do two different snapshot publications a day in the middle of the night, and the replication agents stop when they are done, I checked. 

There are no open transactions on the database in question, although there are around 20 on tempdb.  The transactions on tempdb when reading from sys.dm_tran_database_transactions all have:
database_transaction_begin_time = NULL
database_transaction_type = 2  (read-only)
database_transaction_state = 3  (The transaction has been initialized but has not generated any log records.)
database_transaction_status = 0
database_transaction_status2 = 256

This is the result of an OPENTRAN command run on the database in question March 6, 2008 at 1:40 PM.
Oldest active transaction:
    SPID (server process ID): 81
    UID (user ID) : -1
    Name          : INSERT
    LSN           : (999:138204:2)
    Start time    : Mar  6 2008  1:34:47:827PM
    SID           : 0x88d52e4051a71143adee5dc7b6619f8a

Replicated Transaction Information:
        Oldest distributed LSN     : (890:2091888:1)
        Oldest non-distributed LSN : (896:2784855:1)

This is the problem:
The transaction log won't stop growing.  The log_reuse_wait_desc in sys.databases for this database says REPLICATION, but the replication agents are not running.  I know that it worked fine for several days after the replication was set up, when the log_reuse_wait_desc said NOTHING, but I don't know why it changed to REPLICATION.  When I run the shrinkfile command, it runs, but the file size remains the same. 

Everything I've read says that with a simple recovery model the transaction log should essentially take care of itself, or I should be able to shrink it with dbcc shrinkfile, but that's not happening, so what's the next step?

Any help would be appreciated.

Have a good day.
Dale Buchanan

Answer 1

The fact that you have non-distributed LSN in the log is the problem. I have seen this once before not sure why we dont unmark the transaction as replicated. We will investigate this internally. You can execute the following command to unmark the transaction as replicated

 

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

 

At this point you should be able to truncate the log.

Answer 2

Thanks, that did the trick.  I set the script as a daily job to run your command and then shrinkfile.

Have a good day.
Dale Buchanan

Answer 3

Hi i have the same problem and i don´t see this as a solution rather a workaround, but the applicationpeople breathing my neck for a explanation and possible a solution. So are there a explanation and solution at this point.

 

Answer 4

Hi,

"The fact that you have non-distributed LSN in the log is the problem. I have seen this once before not sure why we dont unmark the transaction as replicated. We will investigate this internally. You can execute the following command to unmark the transaction as replicated"

Of course it's a workaround.  I spent two days following steps on other posts and it didn't help me.  The message above indicates that Microsoft itself didn't know about the problem, which means it is a bug, which means you have to use a workaround until they produce an update to fix it.  This is much simpler and less resource-intensive than the other workaround I encountered, which suggested that you should back up the database, then detach it, then get rid of the log, then reattach the database.

Have a good day.
Dale

Answer 5

I have the same problem and I tried to run EXEC sp_repldone @xactid=NULL,@xact_segno=NULL,@numtrans=0,@time=0,@reset=1 but it says database is not published.

 

Answer 6

I, also, am getting this error message.  I suspect that the database I am seeing this issue with was a backup, move and restore of a replicated database.

 

Until a fix arrives, I plan on detaching the DB, killing the log file and reattaching.

Answer 7

Has this been identified as a problem within the replication framework and if so, apart from the workaround suggested in prev posts, is a resolution available ? 
 

Answer 8

Hi,

We are facing a similar problem. Our situation is as follows:

We run a DB in full recovery model. Every 15 minutes we backup the log. At midnight we backup the complete DB. There is neither replication nor mirroring configured for any DB on the server.

Truncating the log does not work, and sys.databases states REPLICATION as the reason. However, executing sp_repldone does not work either, as no replication has been configured. Meanwhile we are running into severe disk space problems due to the log size.

So far I do not dare "killing" the log by detaching the DB renaming the log file and re-attaching the DB, as I do not know if I have to fear any severe side effects.

Has anyone a hint what I could do?

Many thanks and my best wishes for a Happy New Year,
Dietmar.

---

Best regards, Dietmar.

Answer 9

Create transactional replication on the database. just create a new publication with one table and then delete the publication.

You may also want to try executing sp_repldone when you have created the publication.

--Sateesh

Answer 10

The fact that you have non-distributed LSN in the log is the problem. I have seen this once before not sure why we dont unmark the transaction as replicated. We will investigate this internally. You can execute the following command to unmark the transaction as replicated

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

At this point you should be able to truncate the log.

So is this an official bug with Microsoft? Or is there a more current resolution?
I'm in a very similar situation as the original poster.  DB is in Simple recovery mode.  It has one snapshot publication, no transactional ones.  Percent of transaction log file used is growing.  Once it reaches ~90% full, the transaction log file will grow in size (auto-growth is set at 100 MB), then the percent of transaction log file used will again climb to ~90%, log file grows again, repeat.  Nightly full backups are taken, but the % of log file used never goes down, log file continues to grow.   Log_Reuse_Wait_Desc in sys.databases is "Replication".  Dbcc opentran returns this:

Transaction information for database 'XXXX'.

Replicated Transaction Information:
        Oldest distributed LSN     : (139564:680:6)
        Oldest non-distributed LSN : (139564:839:1)

Manually running sp_repldone will temporarily fix the situation - log file % used goes to near 0 and transaction log file can be shrunk.  But I am quickly returned to the situation described above.  Is this still the only option?  In testing various other suggestions, I did notice that creating a transactional publication does result in the log file not growing, presumably because the Log Reader Agent is running sp_repldone.  But if I only have a snapshot publication and in simple mode, why are transactions staying in the transaction log file?  I shouldn't need to manually run sp_repldone (as was provided as the workaround), or create a transactional publication so that a LogReaderAgent will run that SP.  Has anything regarding this issue changed since it was initially brought up in March 2008?

---

Steve LaRochelle

Answer 11

this worked for me! This problem kept reoccurring every few months.

Also an excellent article found here

http://blogs.msdn.com/sqlserverfaq/archive/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication.aspx

As a long term fix its says to turn off "Replicate DDL changes" option on the publication. This should not be needed for Snapshot replication anyway so its easily done. Just need to wait and see now if this works as a long term solution.

...until MS comes up with a proper hotfix that is

Answer 12

I got same problem as  you did, please try following solution, this work on my case

SELECT name, log_reuse_wait_desc FROM sys.databases
--if the log_reuse_wait_desc  is replication then remove it

EXEC sp_removedbreplication YourDatabaseName

SELECT name, log_reuse_wait_desc FROM sys.databases
 

-- log_reuse_wait_desc  on database should be changed to "nothing" now. if yes do following steps

ALTER DATABASE YourDatabaseName SET RECOVERY SIMPLE

 

DBCC SHRINKFILE (N'Your Logical Log Name', 0, TRUNCATEONLY)

 --Now My log file size drop from 18GB to 0MB

 
--after that you should reset you database recovery model to full if you need.

good luck

xyzone

 

Answer 13

could this problem cause my database backup.bak file to grow large?

Is the database backup trying to backup the transactions in the log file?

Answer 14

I have been looking for days for this answer and finally found it.  The only change I had to make was to set recovery simple BEFORE EXEC sp_removedbreplication YourDatabaseName to get the results you described.  I also had to run DBCC SHRINKFILE on the log file to make it actually shrink.  (DBCC SHRINKFILE ('Audit_Log,2)) 

Answer 15

The underline issue appears even if you have SIMPLE recovery model in place.

 

Is anyone aware of the actual FIX for this bug? A bug not fixed in 4years, I don’t believe it.

 

In my system, the transaction logs grows to 81gb (free drive space +30gb) and replication just stalls. In addition, replication indicates latency and log reader shows read activities.

The workaround (EXEC sp_repldone) works just fine (though I’ve not checked if any data loss).

Answer 16

Hi all,

I have tried the follwoing

EXEC sp_removedbreplication YourDatabaseName

SELECT name, log_reuse_wait_desc FROM sys.databases 
 

-- log_reuse_wait_desc  on database should be changed to "nothing" now. if yes do following steps

ALTER DATABASE YourDatabaseName SET RECOVERY SIMPLE

 

DBCC SHRINKFILE (N'Your Logical Log Name', 0, TRUNCATEONLY)

 --Now My log file size drop from 18GB to 0MB

but  it doen't work to me. Still my log file size is 63 GB. I need to take backup the db and restore it to new location. Since log file is too large, i am not able to restore the bak file in new server.

Can any one help on this ?

---

Thanks, Karikalan N

Answer 17

Try this

alter database YourDatabaseName set recovery simple

GO

alter database YourDatabaseName set recovery Full

GO

DBCC SHRINKFILE (N'Your Logical Log Name', 0, TRUNCATEONLY

If this does not work do this in your publication database.

dbcc opentran

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 18

if replication is running the  execute the below query

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

then execute

DBCC SHIRINKFILE('DBname_log',0)

as you are using SQL Server 2005 you can use truncateonly to truncate the log file

---

Ramesh Babu Vavilla MCTS,MSBI