SQL Server Distribution Synchronisation Error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

SQL Server Distribution Synchronisation Error

Author

Topic

KittyKatie
Starting Member

3 Posts

Posted - 2014-07-23 : 10:01:02

I have been attempted to setup a transactional replication across two servers.

I am using two separate Virtual Machines. One is running Windows Server 2008 with SQL Server 2008 R2 Standard. One is running Windows Server 2012 with SQL Server 2012.

I have been using a guide from Microsoft's website to setup replication data between two connected servers but have run into some errors which I have been unable to fix through solutions found online.

Below are some screenshots taken from the server in hope of showing required data or information where a blatantly obvious mistake could be seen.

Any and all suggestions are very much appreciated.

- This is the main error I am currently getting when attempting to View Synchronization Status.

- There is a second error I had encountered previously in other iterations of settings. I have been unable to replicate this.

- Here is the information provided at the end of the current Subscription Wizard setup where the original error was produced.

- Showing initial success after wizard has finished.

- Publication Properties from the right click context menu.

- Publication Access List

- Users inside of Northwind database on Subscriber end

If any more information is required for help to be delivered please ask, I'm inexperienced with SQL Server software studios and I am quite unsure what the issue could be.

Thanks for any time and help taken with this question!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-23 : 12:33:41

You need to check the agent job history for the specific error as what you have above will not show it. The job name to check is in your first screenshot. Show us the output of the job step history, specifically the retries.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

KittyKatie
Starting Member

3 Posts

Posted - 2014-07-24 : 05:32:37

Hey! Thanks for the reply.

Sorry again for my complete noobiness, it's the first thing I've ever had to do for SQL Server administration, learning from the base up.

Took a snip of the Log File for all the jobs and I'm unable to see any errors, just ticks, is this just something to do with it running continuously and not allowing me to look at the Sync Status because it's always running? Stabbing at the dark here!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-24 : 12:57:10

The only job that we care about right now is the one that was in a retry status. It will not show as failed until it hits the number of retries, which I believe is a very high number.

Look at the job history for only this job: WIN-91KB167NSAK-Northwind-Northwind-TEST-2012-15

Expand it. We need to see the job STEP history. You'll want to click on one of the retries and post the output of it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

KittyKatie
Starting Member

3 Posts

Posted - 2014-07-24 : 13:56:21

Sorry for wasting your time with useless info, this is litteraly the first thing I've ever had to do with SQL Server so I'm a bit out of my depth.

Checked the Steps information in the Job, expanded the error with only 3 steps (Screenshot)

Output from error Step ID 2

quote:
Date 22/07/2014 11:53:38
Log Job History (WIN-91KB167NSAK-Northwind-Northwind-5)

Step ID 2
Server WIN-91KB167NSAK
Job Name WIN-91KB167NSAK-Northwind-Northwind-5
Step Name Run agent.
Duration 00:00:03
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: WIN-91KB167NSAK\repl_snapshot. The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failed.

Message from the previous step ID 2:

quote:
Date 22/07/2014 11:53:38
Log Job History (WIN-91KB167NSAK-Northwind-Northwind-5)

Step ID 2
Server WIN-91KB167NSAK
Job Name WIN-91KB167NSAK-Northwind-Northwind-5
Step Name Run agent.
Duration 00:00:03
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
2014-07-22 10:53:39.64 Microsoft (R) SQL Server Snapshot Agent
2014-07-22 10:53:39.64 [Assembly Version = 10.0.0.0, File Version = 10.50.1600.1 ((KJ_RTM).100402-1539 )]
2014-07-22 10:53:39.64 Copyright (c) 2008 Microsoft Corporation.
2014-07-22 10:53:39.64 The timestamps prepended to the output lines are expressed in terms of UTC time.
2014-07-22 10:53:39.64 User-specified agent parameter values:
2014-07-22 10:53:39.64 --------------------------------------
2014-07-22 10:53:39.64 -Publisher WIN-91KB167NSAK
2014-07-22 10:53:39.64 -PublisherDB Northwind
2014-07-22 10:53:39.64 -Publication Northwind
2014-07-22 10:53:39.64 -Distributor WIN-91KB167NSAK
2014-07-22 10:53:39.64 -DistributorSecurityMode 1
2014-07-22 10:53:39.64 -XJOBID 0x38B97E029B01894CBCBCF90582891910
2014-07-22 10:53:39.64 --------------------------------------
2014-07-22 10:53:39.64 Connecting to Distributor 'WIN-91KB167NSAK'
2014-07-22 10:53:39.82 Parameter values obtained from agent profile:
2014-07-22 10:53:39.82 ---------------------------------------------
2014-07-22 10:53:39.82 -BcpBatchSize 100000
2014-07-22 10:53:39.82 -HistoryVerboseLevel 2
2014-07-22 10:53:39.82 -LoginTimeout 15
2014-07-22 10:53:39.82 -QueryTimeout 1800
2014-07-22 10:53:39.82 ---------------------------------------------
2014-07-22 10:53:39.84 Connecting to Publisher 'WIN-91KB167NSAK'
2014-07-22 10:53:39.92 Publisher database compatibility level is set to 80.
2014-07-22 10:53:39.92 Retrieving publication and article information from the publisher database 'WIN-91KB167NSAK.Northwind'
2014-07-22 10:53:40.09 [0%] Validating the synchronization view for article 'Order Details'
2014-07-22 10:53:40.46 [0%] The replication agent had encountered an exception.
2014-07-22 10:53:40.46 Source: Replication
2014-07-22 10:53:40.46 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2014-07-22 10:53:40.46 Exception Message: Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
2014-07-22 10:53:40.46 Message Code: 3933
2014-07-22 10:53:40.46

There was no other message after the message code, it cut off there in the output console. And again thank you very much for the time you're taking to help on this probably obvious and stupid issue.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-24 : 14:24:17

I need to see the job step history of this job: WIN-91KB167NSAK-Northwind-Northwind-TEST-2012-15. You showed me a different job.

Why is the compatibility level set to 80 for the database? That's SQL Server 2000.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources