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....
- 876
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 ...
- 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).
...
- 181
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 ...
- 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 ...
- 42.7k
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) ...
- 62.1k
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:
...
- 15.8k
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 ...
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 ...
- 18.1k
6
votes
Accepted
Replication taking too much time between requests
What could/should we do?
Discard the assumption of synchronous replication and redesign your application accordingly. Replication was never designed for this. Latency of hours can happen and you ...
- 51.7k
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 ...
- 62.1k
5
votes
Accepted
Will replication commands build up if the distribution agent is stopped?
Yes, the transactional replication data will build up while waiting for distribution to start again. Of course, if not tracked it is possible that a long delayed set of transactional replication data ...
- 14k
5
votes
Creating a read only replica of SQL Server database for reports
I don't need a real time sync as mirroring does it, also I don't need any king of cluster solution, just read only syncing copy of my PROD database.
As Mike mentioned, T-Rep is not free. You have to ...
- 62.1k
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 ...
- 71
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. ...
- 591
5
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 ...
- 15.8k
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 ...
- 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.
...
- 45.9k
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....
- 180k
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, ...
- 62.1k
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 ...
4
votes
Execute a script after transactional replication setup completes
I need assistance to execute/run automatically below .SQL file/Query
automatically at subscriber once publication is done
You should use sp_addscriptexec tsql to run sql script on all subscribers ...
- 62.1k
4
votes
Accepted
how to find the name of the distributor server and publisher server from the subscriber server/subscriber database
Is there a way I can find the name of the publisher and distributor
(server and database), from the subscriber?
For push subscriptions from the subscriber you can query MSsubscription_agents to get ...
- 3,144
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 ...
- 219
4
votes
Replication taking too much time between requests
Why do you expect nearly instant replication? That is not in the definition of replication. See the SQL Server Replication page. It says in part: "remember that replication is an asynchronous data-...
- 14k
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 ...
- 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 ...
- 30k
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'...
- 69.7k
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 ...
- 5,926
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 ...
- 62.1k
Only top scored, non community-wiki answers of a minimum length are eligible