April 24, 2009 at 9:20 am
Have an annoying issue I need to solve ASAP!
I have a sql 2005 box pulling from a sql 2000 transactional publication.
The data is taken into 2 databases. Lets call them DB1 and DB2.
Now DB1 has been pulling these commands across fine for the last month or two. I have just added in DB2 (that is fully sync'd) and this is setup with the same settings and security.
However, when I run this agent it just executes for ages before timing out! VERY ANNOYING. I can't seem to work out why!
I have raised the timeout to something silly. But it timesout anyway.
Last few lines of the output where it gets stuck shows as -
2009-04-24 15:11:35.003 OLE DB Subscriber 'xxxxx: {call sp_MSensure_single_instance(xxxxxxxx', 10)}
2009-04-24 15:11:35.003 Initializing
2009-04-24 15:11:35.034 OLE DB Subscriber 'xxxxxx': exec sp_MSset_subscription_properties @publisher = N'xxxx',@publisher_db = N'xxxxxxxx, @publication = N'xxxxxxx', @subscription_type = 1, @allow_subscription_copy = 0, @queue_id = N'', @update_mode = 0, @attach_version = 0x3c76ee4f6e772c40835816ca0fcfa7f7, @queue_server = N''
2009-04-24 15:11:35.034 OLE DB Distributor 'xxxxxx': exec sp_MSreset_subscription_seqno 58, 1
2009-04-24 15:11:35.221 OLE DB Distributor 'xxxxxx': {call sp_MSget_subscription_guid(58)}
2009-04-24 15:16:35.219 The process is running and is waiting for a response from the server.
April 24, 2009 at 12:21 pm
"The process is running and is waiting for a response from the server."
That message usually happens when there is blocking involved.
You should check your server for blocking/locking issues.
* Noel
April 25, 2009 at 3:52 am
I checked for locks on the subscriber and publication and there are none. Meanwhile i can kick off the other distribution agent collecting from the same publication and it flies through!
April 27, 2009 at 4:58 am
For anyone interested I solved this.
Seems there was too much data trying to replicate (not sure why yet as there should only have been a few hundred records).
I lowered the commitbatchthreshold on the subscriber's dist agent to 300 and the records started to filter through!
April 27, 2009 at 8:42 am
I have seen this before and is usually that the Distribution cleanup job gets in the way.
Sometimes I have even disable it for a while to let subscribers catch up
and then re-enable it to go through removing the old replicated transactions.
* Noel
April 27, 2009 at 8:44 am
By the way when I said "get in the way" I meant "LOCKING/BLOCKING" in distribution database.
* Noel
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply