| Author |
Topic |
|
Richard498
Starting Member
5 Posts |
 Posted - 2012-02-16 : 13:24:08
|
| 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
5072 Posts |
Posted - 2012-02-16 : 14:15:26
|
| 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+ |
 |
|
|
Richard498
Starting Member
5 Posts |
Posted - 2012-02-16 : 16:16:02
|
| 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. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-17 : 07:13:52
|
| 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. |
|
|
|
Richard498
Starting Member
5 Posts |
Posted - 2012-02-19 : 16:30:44
|
| 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? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-20 : 10:31:15
|
| Yeah, not getting distributed prevents 'em from getting removed. You need to find out why CDC is blocking distribution. |
|
|
|
Richard498
Starting Member
5 Posts |
Posted - 2012-02-20 : 10:36:26
|
| But they are getting distributed. Replication is working fine. The size of the database just keeps growing, not the row count. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-20 : 10:45:23
|
| Are changes making it to the change tables? Are ETLs blocking change data from getting written? Read through this? And this? |
|
|
|
Richard498
Starting Member
5 Posts |
Posted - 2012-02-20 : 11:01:26
|
| 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. |
|
|
|
|
Distribution Growing Ghost Cleanup running for day