0

I have setup transaction Replication PULL type between SQL servers. But, my distribution cleanup job is not removing any data from MS_replCommands and repltransaction tables.

I have set Immediate_Snyc and allow_anonymous to 0.

Distribution Job Detail:

Query: EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72

JOB result: Executed as user: NT SERVICE\SQLSERVERAGENT. Removed 0 replicated transactions consisting of 0 statements in 0 seconds (0 rows/sec). [SQLSTATE 01000] (Message 21010). The step succeeded.

note: When I have set Immediate_Snyc to 1 and tried then it worked, but why not with 0 as on other server I have set 0 and it's working.

Please help me.

Dhaval Lakhani
  • 29
  • 5

1 Answers1

0

Strange- the expected behaviour is that if immediate_sync is "true", then the distribution database will hold transaction data for the set maximum retention period, so that the current, and any new subscribers can get the baseline snapshot + transactions necessary to "catch up". You'd expect the distribution database to hold data for the max retention period (72 hours in your case).

If it's set to "false" then any new subscribers will need a new snapshot, however any distributed commands will be cleared from the distribution database by the cleanup job.

Double check that all your subscribers are receiving transactions, and do you have anonymous subscriptions enabled?

TheFlymo
  • 21
  • 2