Post reply

SQL Server Distribution Synchronisation Error

  • July 23, 2014 at 8:02 am

    #288878

    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!

  • July 23, 2014 at 11:37 am

    #1731870

    The errors are largely from SQL Agent. They are saying you can not start a job that is currently running. It is nothing to worry over.

  • July 24, 2014 at 4:09 am

    #1732048

    Hey there!

    Sorry again, completely new to SQL Server structure in general, when checking inside of the Northwind Database on the Test-2012 server it seems to have done no initial sync of data.

    Would there be a reason that this could have happened and the transactional replication still working?

    Thanks again for the help!

  • July 24, 2014 at 7:09 am

    #1732093

    Check to see if the snapshot job has run. If you right click on the replication in Management Studio, you should see an option for View Snapshot Agent Status.

  • July 24, 2014 at 8:32 am

    #1732111

    Couldn't find the exact option but launched Replication Monitor and went to the agents tab and found this

    Total list of the error

    Error messages:

    Message: Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.

    Command Text: sp_MSactivate_auto_sub

    Parameters: @publication = Northwind

    @article = %

    @status = initiated

    Stack: at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)

    at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout)

    at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.SetPublisherTranSequenceNumViaAutoSub(PublicationActivationState publicationActivationState, SqlConnection connection)

    at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.ActivateCSSPublicationAndSetTranSequenceNums(PublicationActivationState publicationActivationState, SqlConnection connection)

    at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.ConcurrentPreArticleFilesGenerationTransaction(SqlConnection connection)

    at Microsoft.SqlServer.Replication.RetryableSqlServerTransactionManager.ExecuteTransaction(Boolean bLeaveTransactionOpen)

    at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.DoConcurrentPreArticleFilesGenerationProcessing()

    at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.DoPreArticleFilesGenerationProcessing()

    at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()

    at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()

    at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 3933)

    Get help: http://help/3933

    Server WIN-91KB167NSAK, Level 16, State 1, Procedure sp_MSrepl_changesubstatus, Line 1249

    Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction. (Source: MSSQLServer, Error number: 3933)

    Get help: http://help/3933%5B/code%5D

    Thanks again for any continued help! It's really appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply