Database Replication : "log_reuse_wait_desc" shows REPLICATION

Question

Dear Team,

The log file of a standalone SQL 2005 database is growing hugely. I use the sql query as below and found the column "log_reuse_wait_desc" = REPLICATION

select name, log_reuse_wait_desc from sys.databases;

To know what is the replication about, in SSMS, I expandded the "Replication ->Local publication" and " Replication ->Local Subscription" but found no objects existing inside that. So i believe replication is not enabled on the SQL Server.

To confirm it again, I opened the replication monitor but I dont see anything in the left or the right pane.

In the left pane, when I try to add a Add Publisher by (Replication Monitor -> My Publishers -> Add Publisher -> Add SQL Server Publisher) and connect to the current server, it throws an error like below.

'Server 'ABCDEFGH' is neither a Publisher nor a Distributor, or you do not have permission to access replication functionality on this server.'

As I am sysadmin on the SQL server, so I believe, this is not a permission issue and the error confirms that the server is neither a publisher nor a Distributor.

So here, my question is why 'log_reuse_wait_desc' is showing as REPLICATION where as no replication is enabled on the server. What to do to figure out what is the replication all about, analyse it, disable/ delete /stop the replication. I dont see any option in SSMS.

The log is growing hugely and is currently 150 GB. Please need your help.

Thanks,

---

Thanks

Answer 1

Hi,

Here is the solution or explanation you were looking for.

http://www.sqlservercentral.com/Forums/Topic695034-357-1.aspx

---

Regards JakDBA Please Mark this as Answered if this solves your issue or satisfied with the response.

Answer 2

It might be orphaned replication metadata from a database restore.

Do this:

USE master
EXEC sp_removedbreplication
	@dbname = 'myDB'
GO

Then backup, shrink, backup the transaction log.

---

Brandon Williams (blog | linkedin)

Answer 3

Hi,

Are you using CDC (Change Data Capture) at all? It could also be this as CDC uses replication in the background and this is a very common reason for seeing replication when you are not using replication.

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 4

Dear Sean,

This is SQL 2005. So I will rule out the possibility of CDC. Thanks for your suggestion and time Sean. I am checking the other 2 suggestions.

Regards,

---

Thanks

Answer 5

Thanks, I have been checking that tip. I believe someone has tried to setup a replication and removed it improperly and this has created the issue.

As Brandon has suggested, I will remove the replication using the sp.

Thanks for your time! 

Regards,

---

Thanks

Answer 6

Thanks Brandon for the tip,

Also, I need to check when this unwanted replication was setup, when it was removed and if possible who did that. Can you please guide me.

Regards,

---

Thanks

Answer 7

You can't find that info out reliably after the fact.  You might want to look into some auditing methods.

---

Brandon Williams (blog | linkedin)