Snapshot Agent timeout

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Gray - 18 Apr 2007 10:54 GMT

Hi,

SQL Server 2000 on Windows 2003 server.

We have been replicating between databases on the same server using
snapshot replication for some time without any problems. Last night
however the "Replication Distributor" part of the procedure which runs
this command..

-Publisher TANDOORI
-PublisherDB [XPS-2-SQL]
-Publication [XPS2SQL-BAP]
-Distributor [TANDOORI]
-SubscriptionType 1
-Subscriber [TANDOORI]
-SubscriberSecurityMode 0
-SubscriberLogin [sa]
-SubscriberEncryptedPassword [???]
-SubscriberDB [XPSBAP]

... has started to fail with a timeout expired error.

Category:ODBC
Source: TANDOORI
Number: S1T00
Message: Timeout expired

The only thing that has changed is that a new table has been added to
the publisher database [XPS-2-SQL] but this is not one of the
replicated articles. Seems a bit of a coincidence that after the
metadata changed the snapshot replication stopped working.

Not sure if it's relevent but all of our replication commands are run
as part of a SQL agent job.

Has anyone got any thoughts or suggestions they can share? Also can
anyone point me in the right direction for the entire syntax of the
-Publisher command and any examples of how to set the timeout.

Thanks in advance,
Dave

Reply to this Message

Paul Ibison - 18 Apr 2007 13:16 GMT

Hi David,
the title of your message mentions snapshot timeouts, but the text refers to
the distribution agent - presumably it is the latter? You can increase the
timeout using
the -QueryTimeout setting (value in seconds). You might also want to enable
logging to determine which article there is an issue with.
Rgds,
Paul Ibison

Reply to this Message

David Gray - 18 Apr 2007 13:19 GMT

Created a tempoary agent profile with an increased QueryTimeOut.
Ran the job again and the error message was...

"The process is running and is waiting for a response from one of the
backend connections"

Rebooted the server and all worked ok.

Any idea what the "backend connection" might have been?

Dave

>Hi,
>
[quoted text clipped - 37 lines]
>Thanks in advance,
>Dave

Reply to this Message

Paul Ibison - 18 Apr 2007 13:34 GMT

The backend connection is referring to the subscriber. This is a standard
message and is only informational - I get it when the indexes are applied
during synchronization. To find out what it is actually doing, I use DBCC
INPUTBUFFER on the subscriber.
Rgds,
Paul Ibison

Reply to this Message

David Gray - 18 Apr 2007 17:20 GMT

Thanks for the info.

>The backend connection is referring to the subscriber. This is a standard
>message and is only informational - I get it when the indexes are applied
>during synchronization. To find out what it is actually doing, I use DBCC
>INPUTBUFFER on the subscriber.
>Rgds,
>Paul Ibison

Reply to this Message

Piku - 26 Apr 2007 15:40 GMT

Hi David

I am having the same issue...Timeout Expired
U said that
" To find out what it is actually doing, I use DBCC
INPUTBUFFER on the subscriber ".

But if I don't know the "blockid" how can I use the inputbuffer
statement...?? to find out which process was blocking the replication and is
there anyway I can find out the HISTORY of blocked ids...Please reply soon.

Thanks in Advance.

Piku.

> Thanks for the info.
>
[quoted text clipped - 4 lines]
> >Rgds,
> >Paul Ibison

Reply to this Message