March 10, 2020 at 12:24 pm
Dear Team,
I am currently on SQL server 2014 R2 and I have been trying to replicate my LIVE database to a report server using a remote Distributor.
Currently, i am getting the error as shown below:
TITLE: New Publication Wizard
------------------------------
SQL Server could not create publication 'XXDB_PUB'.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
Changed database context to 'XXDB'. (Microsoft SQL Server, Error: 3933)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.5000&EvtSrc=MSSQLServer&EvtID=3933&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
I have tried solution in https://www.sqlservercentral.com/forums/topic/transactional-replication-error-creating-new-publication to no avail.
I also, want to state that i created a linkedserver on the publisher server to connect to the distributor. Do I really need this Linked server.
Please, can someone assist.
Thanks
March 10, 2020 at 4:17 pm
are you using availability groups?
I've seen this before while testing
MVDBA
March 11, 2020 at 9:04 am
Thanks Mike,
We are not using Availability Group.
And also, we are on SQL Server 2014 Service Pack 2
March 11, 2020 at 9:17 am
ok - have you tried using scripting
sp_addpublication
sp_addarticle
sp_addsubscription
see if it's an issue on the UI
script it from live and run it bit by bit. replication code is not difficult - once you have your distributor set up
MVDBA
March 11, 2020 at 9:36 am
Thanks Mike
The error showed again while running sp_addpublication.
Pls see error and script below:
Msg 3933, Level 16, State 1, Procedure sp_MSrepl_addpublication, Line 1401
Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
use [XXDB]
exec sp_addpublication @publication = N'XXDB_PUB', @description = N'Transactional publication of database ''XXDB'' from Publisher ''ServerName''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
March 11, 2020 at 12:18 pm
Hello All,
Please, does anyone have suggestion
March 11, 2020 at 12:28 pm
have you run a dbcc opentran against the database to see if there is an open uncommitted transaction?
also triggers on the tables you want to replicate ?- if so do they update a linked server?
these can both stop you creating a publication (2nd one more likely to stop you adding an article)
it really does look like you have a named transaction that has not been committed or rolled back -
IF @TranCounter > 0
-- Procedure called when there is
-- an active transaction.
-- Create a savepoint to be able
-- to roll back only the work done
-- in the procedure if there is an
-- error.
SAVE TRANSACTION ProcedureSave;
what is your transaction log like? full?, growing ?
MVDBA
March 11, 2020 at 2:07 pm
Dear Mike,
Thanks for your response.
This is a Production environment and transactions are constantly coming in. I can also see some open transaction
Transaction information for database ' '.
Oldest active transaction:
SPID (server process ID): 79
UID (user ID) : -1
Name : implicit_transaction
LSN : (7405:37954:1)
Start time : Mar 11 2020 2:50:58:403PM
SID : 0x01e21b2a396c7a377265ffd704
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Do you recon that I should stop all transactions from coming in before creating the replication
March 11, 2020 at 2:23 pm
what time zone are you in ? - just to understand start time : Mar 11 2020 2:50:58:403PM
since it is currently 14:17 in the uk - maybe the better question is how old is that transaction?
as for stopping all transactions, I've never come across this issue before so I can't say either yes or no. but i'm guessing the minimum basic steps are commit/kill all transactions, then create the publication while no-one else can connect and see what happens....
keep us informed
MVDBA
March 11, 2020 at 3:00 pm
Thanks,
The timezone is GMT+1 and the transaction was recent as at the time i did the opentran.
Just to mention the Publisher DB where I am trying to create the publication has mirroring currently on it.
March 11, 2020 at 3:13 pm
In your first post, you said you tried the solution in the link - there are two different solutions listed. One being setting remote proc trans = 0 and the other being running the process as sa. Which one did you try? And if you set the remote proc trans to 0 did you do this on the server with the distributor as well as the publisher?
The transaction save point is in sp_MSrepl_addpublication which is called by sp_addpublication.
Sue
March 11, 2020 at 3:24 pm
Dear Sue,
Thanks for your response.
The configuration "remote proc trans = 0" was ran on both distribution and Publisher server. However, i did not run the process as 'sa' user but the user I am running it on has the sysadmin priviledge.
Must I run the process as sa. sa is a default account which may be flag by RISK team.
March 11, 2020 at 3:27 pm
It has mirroring on it? that explains it the mirror will be using transactions to send to the secondary
why are you using replication and mirroring on the same server?
MVDBA
March 11, 2020 at 3:36 pm
Dear Sue,
Thanks for your response.
The configuration "remote proc trans = 0" was ran on both distribution and Publisher server. However, i did not run the process as 'sa' user but the user I am running it on has the sysadmin priviledge.
Must I run the process as sa. sa is a default account which may be flag by RISK team.
I would try it if you are able to even if you are running as a sysadmin. I was just looking at sp_MSrepl_addpublication (not the best code) and it looks like it could be failing when creating the log reader agent. I've seen some other posts with similar errors to yours where it was an issue with the job owner and they got around it by running the process as sa. Sa would own the job and not the person running the commands. So in those cases, it makes some sense. I've seen a couple others where they ran SSMS as administrator - another option you may want to try. If you want to look at the code for sp_MSrepl_addpublication, you have to check it using the DAC. And then you deal with the truncated results so one of the easier ways is to just dump the results from object_definition to an outfile using sqlcmd with the y0 (y and zero) options. So something like which would connect on with the dedicated admin connection:
sqlcmd -S YourServername -q "select object_definition(object_id('sys.sp_MSrepl_addpublication'))" -y0 -o C:\temp\SPOut.txt -A
Sue
March 13, 2020 at 9:44 am
Dear Mike / Sue,
I apologise I didnt come back early, I was actually simulating base on Sue's advise.
I started from the basics to see what might be wrong, so we started from the network level since the distributor and the publisher are on different boxes. We realise with tcpdump that the publisher was connecting using a default port (which is not the right port) to the distributing.
We simulated this on test and we were able to work around it for now.
However, I am not sure why the publisher will be connecting to the distributor on a default port.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply