Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Sql 2008 server replication error - Distributor to Subscriber status is waiting for server response

Answered Sql 2008 server replication error - Distributor to Subscriber status is waiting for server response

  • Sunday, December 09, 2012 6:18 AM
     
     
    Sign In to Vote
    0

    Hi,

    i tried to replicate our sql 2008 production database which is 19 gb in size in to the remote server which in our DRM site. i used the publication type as "Transactional" and the publisher and the distributor on same server ( distribution location is in different partition and have shared with required permissions).  later i configured the subscriber with " pull " and continues options(given the alternate location as snapshot location which was the above mentioned shared location). snapshot generated successfully and bulk copying from distributor to subscriber was running. while copying from distributor to subscriber, it was not responding because there is a  one huge file which is named as "ADMFILES_27.bcp" and size is 18 Gb. while copying this file from distributor to subscriber, a  message was there in replication monitor " waiting for server response" more than 3 days. finally replication was failed. i tried several times but ended with same error. 

    appreciate if anybody can give me a solution for above matter .

    Thanks,

    Nishan.

     

All Replies

  • Monday, December 10, 2012 6:06 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    I've found this error to be transitory most of the time.

    It usually occurs when bcp'ing a large table during the snapshot process and clears up when it finishes.

    You can run Profiler to see exactly what is going on.  You can also look at Resource Monitor and see if the subscription database is being written to at that time.

    There query is likely timing out when bcp'ing ADMFILES_27.bcp.  Try increasing the Distribution Agent parameter -QueryTimeout to a higher value.  The default is 1800 seconds.  Try bumping this up to 10800 seconds and see if that helps.

    Brandon Williams (blog | linkedin)

     
  • Monday, December 10, 2012 6:25 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    18 Gigs is a large file. You really should be looking at initializing a subscription from a backup for files of this size.

    http://msdn.microsoft.com/en-us/library/ms147834.aspx

    As Brandon points out the "waiting" statement means that the replication subsystem has not received any messages back from the agent in some time. Querytimeout can help, so can setting the heartbeat level to something larger - 

    USE master 
exec sp_changedistributor_property 
    @property = N'heartbeat_interval', 
    @value = 60;
GO
    Where I have set it to 1 hour.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941