Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transactional replication fails with no meaningful error message Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 6:58 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 4:19 PM
Points: 975, Visits: 1,589
Hi,

I am trying to setup replication between two clustered instances belonging to the same 3 node cluster. The distributor is a separate instance from publisher and subscriber.

When I re-initialise a publication I get this error message:
The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failed.

Neither job history nor SQL Server logs provide any other meaningful messages.

When I try to run the snapshot from command prompt it just works fine:
"C:\Program Files\Microsoft SQL Server\100\COM\SNAPSHOT.EXE" -Publisher [sqlcluster1\publisher] -PublisherDB [MyDB] -Distributor [sqlcluster2\distributor] -Publication [MyPublication] -DistributorSecurityMode 1

Log reader and distributor agent fail with the same generic message.

SQL Server agent account is a local admin on each cluster node and a member of sysadmin role on each instance.

I am running SQL Server 2008R2 SP2.

Any ideas?

Thanks.



Post #1466015
Posted Thursday, June 20, 2013 9:57 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:21 AM
Points: 1,089, Visits: 1,510
Did you check replication monitor? Seems like you did but didnt specifically mention it.
That or
select * from distribution.dbo.msrepl_errors order by time desc

Also regarding permissions, is the service account different for each instance? The snapshot should be in a full unc path (\\server\drive\) accessible by the subscriber's service account.
Post #1466024
Posted Saturday, June 22, 2013 7:30 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 4:19 PM
Points: 975, Visits: 1,589
Yep, I did check the replication monitor. And this query returns errors which happened two days ago and had been fixed.

Amazingly, the snapshot agent works fine when run from command prompt using the same service account.

All instances are using the same service account. The snapshot folder looks like this:
\\SQLCluster8\SQLRepl



Post #1466460
Posted Saturday, June 22, 2013 7:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:49 AM
Points: 187, Visits: 699
Roust_m (6/20/2013)
The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information.


Locate the SQL-Job in management studio (not within the replication monitor) belonging to the snapshot agent. Have a look at the history of this sql job. There you mostly find the mentioned "previous step" with some (more or less) useful error messages.

Post #1466462
Posted Saturday, June 22, 2013 8:44 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 4:19 PM
Points: 975, Visits: 1,589
WolfgangE (6/22/2013)
Roust_m (6/20/2013)
The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information.


Locate the SQL-Job in management studio (not within the replication monitor) belonging to the snapshot agent. Have a look at the history of this sql job. There you mostly find the mentioned "previous step" with some (more or less) useful error messages.



Did this already, no useful message there.



Post #1466468
Posted Wednesday, June 26, 2013 1:31 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 4:19 PM
Points: 975, Visits: 1,589
This turned out to be the problem:
http://connect.microsoft.com/SQLServer/feedback/details/273892/replication-agents-hang-when-a-host-has-a-large-number-of-agents-running-on-it

Just migrated to the new cluster, did not change the replication architecture. Everything worked on the old one.



Post #1467488
Posted Wednesday, June 26, 2013 1:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:21 AM
Points: 1,089, Visits: 1,510
Interesting, so it's a load issue with the distributor? Did you change to a pull subscription or update the jobs to use cmdexec?
Post #1467490
Posted Wednesday, June 26, 2013 3:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:26 AM
Points: 2,402, Visits: 1,860
Try to capture error through profile.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1467532
Posted Wednesday, June 26, 2013 7:24 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 4:19 PM
Points: 975, Visits: 1,589
Andrew G (6/26/2013)
Interesting, so it's a load issue with the distributor? Did you change to a pull subscription or update the jobs to use cmdexec?


I did cmdexec. Very weird, as our replication is not that big.



Post #1467916
Posted Wednesday, June 26, 2013 7:24 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 4:19 PM
Points: 975, Visits: 1,589
free_mascot (6/26/2013)
Try to capture error through profile.


Which profile?



Post #1467917
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse