22 votes
Accepted

Replication Monitor Information using T-SQL

After doing some online search,I found what you needed,hope my answer helps you. Answer 1 Note: It Only works with transactional and transactional peer to peer replication T-SQL script which ...
  • 1,447
14 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....
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
Accepted

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 ...
  • 41.6k
7 votes
Accepted

Moving Transaction Data To Another Database For Reporting Purpose

Since you have this requirement, They do not want to DELETE data from this secondary reporting database, if data gets deleted from the live database AlwaysON gets eliminated. I would suggest you ...
  • 61.4k
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: ...
7 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). ...
7 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 ...
  • 327
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 ...
  • 17.9k
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.1k
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 ...
  • 61.4k
6 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) ...
  • 61.4k
5 votes

Live Partial Table / column replication

You could also solve this with a trigger that populates the copy of the table on insert/update/delete. It wasn't clear in the question that these tables are actually on different servers, and that the ...
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 ...
  • 61.4k
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 ...
  • 13.8k
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 ...
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. ...
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 ...
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. ...
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, ...
  • 61.4k
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
Accepted

The process is running and is waiting for a response from the server - transactional replication

The Query Timeout message you are seeing is your Distribution Agent timing out, not your Log Reader Agent. You need to increase your Distribution Agent profile Query Timeout parameter value to ...
4 votes
Accepted

Replication Madness

Please don't. You don't want replication. It's a data distribution technology: it's not suitable for HA. Just to name one of the many shortcomings of using replication for HA, you won't have automatic ...
4 votes

Replication Snapshot folder not cleaning up

The MSDN Blog "SQL Server SSIS and Replication" has a post from a few years ago. http://blogs.msdn.com/b/mangeshd/archive/2009/05/20/replication-snapshot-folder-cleanup.aspx How these old snapshot ...
  • 13.8k
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
Accepted

missing indexes on distributor database

Replication normally works and I have not encountered any issues where I have to create an additional index on the distribution database. (In my company we use replication a lot for doing data ...
  • 61.4k
4 votes
Accepted

Two-way replication between SQL Server 2008 R2 and SQL Server 2008 R2 SP3

Those aren't different versions just different service packs. Replicating between 2008 and 2008 R2 would better fit that definition of replication between two specific versions. So The way I ...
  • 9,508
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 ...
  • 61.4k
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-...
  • 13.8k

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