Skip to main content
Share Your Experience: Take the 2024 Developer Survey
16 votes
Accepted

Length of LOB data (2135980) to be replicated exceeds configured maximum 65536

This configuration can be changed using Management Studio by right-clicking the server -> Properties -> Advanced tab -> Miscellaneous -> Max Text Replication Size. Or you may want to try : EXEC sys....
Dragos Miron's user avatar
12 votes
Accepted

How to fix a messed up replication on MS SQL Server

TLDR: It appears that disabling and re-enabling replication probably fixed the issue: exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'false' exec ...
TallGuy's user avatar
  • 371
8 votes

How to fix a messed up replication on MS SQL Server

I had a mess with replication and solved it with this DECLARE @subscriptionDB AS sysname SET @subscriptionDB = N'DBName' -- Remove replication objects from a subscription database (if necessary). ...
Guillermo Zooby's user avatar
8 votes
Accepted

Transactional replication latency issue

I had to finally call Microsoft support and just a simple command called DBCC LOG INFO on publisher revealed a possible root cause. I saw more than 8600 VLFs! and this was the cause of that ...
Pat's user avatar
  • 337
8 votes

Unable to truncate transaction log, log_reuse_wait_desc - AVAILABILITY_REPLICA

If you do this: SELECT * FROM sys.databases And the log_reuse_wait_desc shows AVAILABILITY_REPLICA, that means SQL Server is waiting to send log data to one of your Always On Availability Group ...
Brent Ozar's user avatar
8 votes

Sql Server : How to query list of subscriptions and Articles (for a given publication) from Publisher

To get subscription list from a publisher database, you can run below tsql : use publisherDB; select db_name() PublisherDB , sp.name as PublisherName , sa.name as TableName , UPPER(srv.srvname) ...
Kin Shah's user avatar
  • 62.3k
7 votes
Accepted

is it possible to alter the primary key of a table (from non clustered to clustered) in the subscriber only and not break the replication?

No, It is not possible. when the table is involved in replication it does not allow you to drop the primary key. below is an example as how I have changed the primary key of a replicated table: ...
Marcello Miorelli's user avatar
7 votes
Accepted

Can not drop table due to replication error, though table is not being published

TL;DR: This error can occur if you restore (or move) a database that was previously replicated to a server of a lower version and/or patch level and then re-enable replication. See below for solutions ...
DATEFROMFARTS's user avatar
7 votes
Accepted

Domain Account or Windows account for Transactional Replication

Short Answer: Nothing at all is wrong with a domain account. When all the computers participating are in the same domain, go that way. Easier to manage and maintain and secure. Your confusion here ...
Mike Walsh's user avatar
  • 18.2k
6 votes

Transactional replication latency issue

Will changing isolation level to Read Committed Snapshot Isolation (RCSI) help here? This is not a straight forward change and it comes with additional tempdb penalty. I would not suggest you to ...
Kin Shah's user avatar
  • 62.3k
6 votes
Accepted

The Distributor has not been installed correctly. Could not enable database for publishing

I think I have nailed it, what I did is very simple A call to the procedure sp_adddistpublisher was missing. Configures a Publisher to use a specified distribution database. This stored ...
Marcello Miorelli's user avatar
5 votes

is it possible to alter the primary key of a table (from non clustered to clustered) in the subscriber only and not break the replication?

Add the following checks before starting to deploy Marcello's scripts: Make sure that immediate_sync and allow_anonymous are set to FALSE (0) to avoid the creation of the full snapshot and not just ...
Cozzaro Nero's user avatar
5 votes
Accepted

SQL Server - Linked Server error - Could not connect to server XYZ because

The linked server was also used for replication. The order of Subscribe and Create linked server was the issue. Reproduce the error: 1. Drop the subscription 2. Drop the linked server 3. ...
Santhoshkumar KB's user avatar
5 votes
Accepted

Can I use only 1 user for SQL Server replication?

If you use 1 user, you typically end up giving that user more access than it needs. By having the example use 4 different users, it helps demonstrate the good practice of using dedicated accounts for ...
Rob Farley's user avatar
  • 16.2k
5 votes
Accepted

Database Compatibility Requirement for Transactional Replication

Does the subscriber "within two version requirement" for transactional replication from this MSDN article apply to the SQL Server Instance, Database Compatibility Level, or Both? Yes. No. No. ...
David Browne - Microsoft's user avatar
5 votes

Where is located sp_msreplcheck_publish stored procedure?

If is hidden from general view but, if you connect using a Dedicated Administrator Connection, you can see it in master via: USE [master]; GO SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys....
Aaron Bertrand's user avatar
5 votes
Accepted

Why does sp_replmonitorhelpsubscription not work without parameters?

Checking the code reveals that : -- validate @publication_type -- if (@publisher_db is NULL or @publication is NULL) and @publication_type is NULL begin raiserror(20587, ...
Kin Shah's user avatar
  • 62.3k
5 votes
Accepted

Changing collation of SQL Database in Production environment to minimize downtime

I just tried this and it worked in Transactional Replication: Server Level Collation is Finnish_Swedish_CI_AS Database Level Collation of both sides of the databases are the same which is ...
Murali Dhar Darshan's user avatar
5 votes
Accepted

Running sp_repldone on a secondary node of an Availability Group with @reset = 1 not working

For background, I'm assuming this is a custom CDC solution, and the regular CDC jobs aren't running to populate the change tables and run sp_repldone, and so you must run sp_repldone to enable the log ...
David Browne - Microsoft's user avatar
4 votes

Replication Monitor Information using T-SQL

You can get information you need by querying the dbo.MSReplication_monitordata table from a distribution database with this script, for all publications: USE distribution SELECT status AS ...
Mspaja's user avatar
  • 219
4 votes

MSDTC and its relation to SQL Server

MSDTC provides transaction management services for applications. It can act as a regular transaction manager or an XA-compliant resource manager. SQL Server is the well known app that uses DTC but it ...
SQLmojoe's user avatar
  • 1,433
4 votes
Accepted

Can Records be Replicated in an Availability Group Out of Sequence?

At the time of execution, 5 records that meet the criteria are committed on the primary, but another 3, with slightly earlier timestamps than the first 5 (but within the criteria range) are still in ...
Sean Gallardy's user avatar
4 votes

How can I determine the last synchronization time for a SQL Server replication subscription?

From the subscriber side, you should be able to run EXEC dbo.sp_helppullsubscription to get some level of detail. As an example: use [SomeDB] EXEC dbo.sp_helppullsubscription @publisher = N'...
Hannah Vernon's user avatar
  • 70.1k
4 votes
Accepted

Replication on Always On

You can't have two Distribution databases. Put the distribution database in a Failover Cluster Instance that way it is highly available. Maybe one day MS will allow the distribution database to be in ...
Tara Kizer's user avatar
  • 5,966
4 votes
Accepted

Availability Group of SQL Servers 2012, 2012 and 2016 possible?

1) --> What are potential problems if we add SQL Server 2016 as a third node to existing Availability Group that has two SQL Servers 2012? Once you failover(or failover happens) to 2016, you wont be ...
Kin Shah's user avatar
  • 62.3k
4 votes
Accepted

Syncronizing process difference between replication and alwayson high availability

Transactional Replication uses the TDS protocol and applies changes using SQL. The connections are established from the Distributor for Push Subscriptions, and from the Subscriber for Pull ...
David Browne - Microsoft's user avatar
4 votes
Accepted

How to pause the subscriber end of transactional replication

Replication runs using "Agents"--essentially external executables that do the work. For transactional Replication, you have three agents: Snapshot Agent - this is the process who does the snapshot to ...
AMtwo's user avatar
  • 16.2k
4 votes

SQL Server 2014 AOAG + Replication?

IN SQL Server 2014 and earlier, the replication jobs will not continue to work after failover if you put the Distribution Database in an AG. If you upgrade to SQL Server 2016 or 2017, this becomes a ...
David Browne - Microsoft's user avatar
4 votes

DBCC SHRINKFILE on the distribution database

It is ok to shink the datafile, there WON'T be any data loss. The "issues" when shrinking could be performance, during the rellocation of data pages. Also if you know that your DF will grow again ...
dbamex's user avatar
  • 472
4 votes

Failover strategy for SQL Server 2016 Standard Edition

Separate from your Replication solution, could use a Failover Cluster Instance or Basic Availability Group for HA of your publisher. Or, if you have a stomach for obscure replication solutions, you ...
David Browne - Microsoft's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible