Transactions not getting replicated to the subscribers The process is running and is waiting for a response from the server

Question

I was getting Query Timeout expired.  I put -Output in Distribution Agent and increased the Query timeout.  Now all I see in the output is  "The process is running and is waiting for a response from the server."  While the number of commands in Distribution database waiting to be applied to the subscribers is increasing.  I don't want to reinitialize the subscription because it is huge and we are only replicating some tables so initialize from backup won't work.  Thanks for your help!

Answer 1

How many undistributed commands do you have?

The message "The process is running and is waiting for a response from the server." indicates that the agent is currently processing commands and is transitory.  It will clear up when the agent finishes with its current batch.

---

Brandon Williams (blog | linkedin)

Answer 2

232450292  

Answer 3

Even after increasing the Query TimeOut, Distribution Agent runs but doesn't do anything.  Once it reaches the higher limit of Query TimeOut it Times Out.  Nothing in Output except "The process is running and is waiting for a response from the server."  As painful as it is I think I will have to reinitialize.

Answer 4

This means that your replication agent is busy and the query has not sent a status message back to the replication subsystem yet. Use profiler to see if you can determine what the agent is doing, or use sp_who2 to find the spid and then use dbcc inputbuffer(spid) to determine what is currently being applied.

It is entirely possibly that the agent is applying a long running command which may take a long time to apply.

The best thing to do is wait it out.

---

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

Answer 5

Running the profiler confirms that this Distribution Agent is completely missing in Action while other Distribution Agents from smaller publications are trudging along.   

Answer 6

OK, now you have to find out what it is doing, query sys.sysprocesses to find the spid and then use dbcc inputbuffer(spid) to find out what it is hung on.

---

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

Answer 7

I did what you suggested.  All I get is 'begin tran'

Answer 8

Looks  like you may have an orphaned transaction. Can you check to see is this spid is getting any cpu or io?

---

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

Answer 9

CPUTime    DiskIO
63              0

Answer 10

After Initializing from backup and adding push subscription, I am getting the same message again "The process is running and is waiting for a response from the server." It times out again.  On the subscriber distribution Agent is in sleep mode with CPU of 63 and Disk IO of 0.  I tried creating pull subscription but I was getting "Agent message code 14080. The remote server " " does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers."  Even though the Distribution Job owner is SA on both on publisher\distributor and subscriber.  

Answer 11

Can you verify the Distribution Agent process account is a member of the PAL?

---

Brandon Williams (blog | linkedin)

Answer 12

Yes it is.  I verified it by scripting the publication

exec sp_grant_publication_access @publication = N'PublicationName', @login = N'Distribution Agent  Account Id'

Answer 13

The error "Agent message code 14080. The remote server " " does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers." is most likely a permissions issue.  Also verify the Distribution Agent has read permissions on the snapshot share.

You'll want to enable verbose agent logging to capture additional information.

---

Brandon Williams (blog | linkedin)

Answer 14

Ok figured out this "Agent message code 14080. The remote server " " does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers."  I was using fully qualified domain name so changed it to the name specified under sp_helpdistpublisher.  Now I am getting this issue.  This User is an sa on this server.  When I login to the subscriber server using this userid, I can open the subsciberDb without any issue.

2012-03-14 20:15:40.328 User-specified agent parameter values:
            -Publisher PublisherDBServerName1
            -PublisherDB PublisherDBName
            -Publication PublicationName
            -Distributor PublisherDBServerName1
            -SubscriptionType 1
            -Subscriber SubscriberServerName
            -SubscriberSecurityMode 1
            -SubscriberDB PublicationName
            -Continuous
            -XJOBID 0xEA5237421EF4F44D84FD740F487CCD7F
            -XJOBNAME PublisherDBServerName1-PublisherDBName-PublicationName-SubscriberServerName-PublicationName-1119FEF0-CDA5-49EB-B24E-3D8D9CFEBE53
            -XSTEPID 1
            -XSUBSYSTEM Distribution
            -XSERVER SubscriberServerName
            -XCMDLINE 0
            -XCancelEventHandle 0000000000000624
            -XParentProcessHandle 0000000000000670
2012-03-14 20:15:40.328 Startup Delay: 4910 (msecs)
2012-03-14 20:15:45.250 Connecting to Subscriber 'SubscriberServerName'
2012-03-14 20:15:45.297 Agent message code 20052. The process could not access database 'SubscriberDBName' on server 'SubscriberServerName'.
2012-03-14 20:15:45.297 Category:NULL
Source:  Microsoft SQL Server Native Client 10.0
Number:  4060
Message: Cannot open database "SubscriberDBName" requested by the login. The login failed.
2012-03-14 20:15:45.297 Category:NULL
Source:  Microsoft SQL Server Native Client 10.0
Number:  18456
Message: Login failed for user 'Domain\DistributionAgentOwnerId'.

Answer 15

Can you verify that the Distribution Agent process account is a member of the db_owner fixed database role in the subscription database?

---

Brandon Williams (blog | linkedin)

Answer 16

Yes it is.

Answer 17

I only receive this error when the Distribution Agent process account is not mapped to the subscription database.

2012-03-14 21:14:31.151 Agent message code 20052. The process could not access database 'TestSubDB1' on server 'P47-THUNDERBOLT'. 2012-03-14 21:14:31.153 Category:NULL Source: Microsoft SQL Server Native Client 10.0 Number: 4060 Message: Cannot open database "TestSubDB1" requested by the login. The login failed.

---

Brandon Williams (blog | linkedin)

Answer 18

I am relieved to report that I finally got this replication working.  SQL Server Installation was corrupt, that is why I was getting the weird login failure.  After reinstalling SQL Server everything worked with Pull subscription.  Just inherited this new environment which was being run by System Administrators so it is in need of some TLC.

Answer 19

Thank You Brandon and Hillary for all the help!