SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Replication (2005)
 Distribution Growing Ghost Cleanup running for day
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Richard498
Starting Member

5 Posts

Posted - 02/16/2012 :  13:24:08  Show Profile  Reply with Quote
We are replicating data from SQL Server A to SQL Server B using ETL Server C for the data transformation. Locks are occurring between the software on Server C and Distribution on Server A. The Distribution database has been growing and Ghost Cleanup has been running continuously for days.

I rebooted all three servers on Sunday and distribution shrunk down to 500 mg. Ghost cleanup has been running on Server A since it was rebooted and distribution is back up to 40 gigs.

If I stop the CDC process on Server C, the locks go away, but our data obviously isn't being replicated to Server B. I have a feeling Ghost Cleanup is having a problem since there are only a couple hundred rows in msrepl_commands. @@Trancount returned 0. Anybody know why Ghost Cleanup is still running or have any thoughts on what else the problem could be? Thanks.

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 02/16/2012 :  14:15:26  Show Profile  Visit russell's Homepage  Reply with Quote
I don't understand. What does ETL have to do with it?

CDC and Transactional Replication should be able to co-exist and starting/stopping CDC shouldn't affect replication.

Is Server C a dedicated distributor? What else is going on on that box? How many cores on C?

Also, what versions and editions are all 3 servers? You posted this in SQL 2005, but unless I'm very much mistaken, CDC is 2008+

Edited by - russell on 02/16/2012 14:17:54
Go to Top of Page

Richard498
Starting Member

5 Posts

Posted - 02/16/2012 :  16:16:02  Show Profile  Reply with Quote
Yes, everything was find until about a week ago. The blocking I'm seeing is the data services engine blocking the repl-LogReader. I'm sure that problem will ago away when I fix the real issue.

I figured Ghost Cleanup running and distribution database being so big was causing the blocking somehow. Server C is a dedicator distributor. It's the only thing it does. It has 16 cores. Sorry, you're right, C is Server 2008, but A and B are 2005 so I posted here since A is where the problem is.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 02/17/2012 :  07:13:52  Show Profile  Visit russell's Homepage  Reply with Quote
Make sure the capture job for CDC was deleted (or ar least disabled).

If that isn't it, run a trace for sp_replcmds. It shouldn't be called from anywhere other than the log reader agent job.

The bloated distribution database is a symptom, not a cause.
Go to Top of Page

Richard498
Starting Member

5 Posts

Posted - 02/19/2012 :  16:30:44  Show Profile  Reply with Quote
I can't seem to find anything in the trace. I'm curious why the msrepl_Commands size is so large while there are always very few records. Seeing Ghost Cleanup has been running, that makes me think there's something preventing it from deleting the actual rows marked for deletion?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 02/20/2012 :  10:31:15  Show Profile  Visit russell's Homepage  Reply with Quote
Yeah, not getting distributed prevents 'em from getting removed. You need to find out why CDC is blocking distribution.
Go to Top of Page

Richard498
Starting Member

5 Posts

Posted - 02/20/2012 :  10:36:26  Show Profile  Reply with Quote
But they are getting distributed. Replication is working fine. The size of the database just keeps growing, not the row count.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 02/20/2012 :  10:45:23  Show Profile  Visit russell's Homepage  Reply with Quote
Are changes making it to the change tables? Are ETLs blocking change data from getting written? Read through this? And this?

Edited by - russell on 02/20/2012 10:46:47
Go to Top of Page

Richard498
Starting Member

5 Posts

Posted - 02/20/2012 :  11:01:26  Show Profile  Reply with Quote
Yes, the changes are making it to the change tables. I've been checking row counts of the source and target tables and everything is working fine. It's almost like the records are being marked for deletion after they are written, but then aren't being physically deleted.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.24 seconds. Powered By: Snitz Forums 2000