August 23, 2011 at 6:31 am
I have a replication setup with a push subscription which was working ok until recently. There were network errors which stopped everything for a few days but when they were resolved I ran a snapshot to get everything back in sync as the subscription had expired. I started the distribution agent after the snapshot had been run and the data was all fine. But even though the distribution agent is defined to run as -continuous it now does a custom shutdown after I start it (either manually or via the job that is defined to start it). I can't see where this shutdown instruction is coming from. I have put output logging on to try to trace it. I am manually having to start the distribution agent to keep things up to data at present.
There are 3 steps in the job that starts the agent but none of them tell the agent to stop after running. A snippet from the output is below - this was captured after running the job to start the agent. You can see the custom shutdown in the trace.
Anyone any idea where the shutdown is defined?
Last transaction timestamp: 0x00001652000017d7000400000000
Transaction seqno: 0x00001652000017d70004
Command Id: 2
Partial: 0
Type: 69
Command: [8/23/2011 11:54:43 AM]APP052\MYINSTANCE.mydb: exec dbo.sp_table_validation @table = 'scRbpAction', @expected_rowcount = 0, @rowcount_only = 1, @full_or_fast = 2, @shutdown_agent = 1
exec dbo.sp_table_validation @table = 'scRbpCondition', @expected_rowcount = 0, @rowcount_only = 1, @full_or_fast = 2, @shutdown_agent = 1
Last transaction timestamp: 0x00001652000017d7000400000000
Transaction seqno: 0x00001652000017d90004
Command Id: 1
Partial: 0
Type: 68
Command: APP052\MYINSTANCE.mydb
[8/23/2011 11:54:43 AM]APP052\MYINSTANCE.mydb: update MSreplication_subscriptions set transaction_timestamp = ?, "time" = ? where publisher = ? and publisher_db = ? and independent_agent = 0 and subscription_type = 0
Table 'scRbpAction' passed rowcount (0) validation. Rowcount method 1 used (0 = Full, 1 = Fast).
Repl Agent Status: 8
[8/23/2011 11:54:44 AM]MAIN052.distribution: {call sp_MSadd_distribution_history(3, 8, ?, ?, 0, 0, 0.00, 0x00, 1, ?, 2, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 0,
Transaction Seqno = 00001652000017d5000400000000, Command ID = 2
Message: Subscriber 'APP052\MYINSTANCE' subscription to article 'scRbpAction' in publication 'mydbTrans' passed data validation.[8/23/2011 11:54:44 AM]MAIN052.distribution: {call sp_MSadd_repl_alert(3, 3, 0, 20575, ?, 2, N'MAIN052', N'mydb', N'APP052\MYINSTANCE.mydb, N'mydb', ?)}
The commit timestamp value is: 0x00001652000017d5000400000000
Replication: agent custom shutdown
[8/23/2011 11:54:44 AM]APP052\MYINSTANCE.mydb: if exists (select * from sysobjects where name = 'syspublications') if exists( select * from syspublications where repl_freq = 0) exec sp_replsetoriginator ?, ?
Repl Agent Status: 9
[8/23/2011 11:54:44 AM]MAIN052.distribution: {call sp_MSadd_distribution_history(3, 9, ?, ?, 0, 0, 0.00, 0x00, 1, ?, 0, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 0,
Transaction Seqno = 0000000000000000000000000000, Command ID = 0
Message: Replication-Replication Distribution Subsystem: agent MAIN052-mydb-APP052\MYINSTANCE-3: Replication: agent custom shutdown.[8/23/2011 11:54:44 AM]MAIN052.distribution: {call sp_MSadd_repl_alert(3, 3, 0, 14158, ?, 0, N'MAIN052', N'mydb', N'APP052\MYINSTANCE', N'mydb', ?)}
The process was successfully stopped.
Repl Agent Status: 2
[8/23/2011 11:54:44 AM]MAIN052.distribution: {call sp_MSadd_distribution_history(3, 2, ?, ?, 1, 2, 6.39, 0x00, 1, ?, 2, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 0,
Transaction Seqno = 00001652000017d5000400000000, Command ID = 2
Message: Replication-Replication Distribution Subsystem: agent MAIN052-mydb-APP052\MYINSTANCE-3 succeeded. The process was successfully stopped.[8/23/2011 11:54:44 AM]MAIN052.distribution: {call sp_MSadd_repl_alert(3, 3, 0, 14150, ?, 2, N'MAIN052', N'mydb', N'APP052\MYINSTANCE', N'mydb', ?)}
August 23, 2011 at 7:46 am
Is there any dead lock or something happening on the Distributor? It can cause the distribution agent to shut itself down.
-Roy
August 23, 2011 at 8:12 am
There doesn't appear to anything holding up the distribution database. It's very odd.
In fact I just got the user to add some more transactions that needed to be replicated and started the distribution agent manually again and it went through the usual startup and stop cycle. The user said the data hadn't reached them (even though I could see some transactions had been distributed from the log). So I started the agent manually again - more transactions were sent across but this time the agent has stayed running, i.e. no shutdown.
Weird!
August 23, 2011 at 8:34 am
You do not see anything on the SQL Server error log?
-Roy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply