Archive for the ‘Replication’ Category

Could not delete the subscription at Subscriber

December 31, 2013 Leave a comment

Issue: My server “SVR1” runs a SQL Server with a transactional publication. On the second server “SVR2” I had a subscription.
Now the SVR2 does not exist anymore and I want to delete the replication on SVR1. (in the SQL Management Studio, the subscription is still visible)

But if I want to delete the SVR2-subscription on the SVR1, I get the error message 20032 “Could not delete the subscription at Subscriber ‘SVR2’ in database ‘ICSDB’.”How can I delete this subscription (and afterwards also the publication)

I’m using SQL Server 2008 R2 on Windows Server 2008R2


Right click on the publication; go to properties then to subscriptions. Click Delete. This is the EM way. For the QA way, generate the delete statement from EM.

For future use, I was having the same problems described above – but I also had database snapshots on the database that I was trying to remove replication from but that was not mentioned anywhere in the error messages.

Reference Link:

Once I removed my database snapshots, the following script cleans things up well:

Example came from here:

DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB as sysname;
SET @distributionDB = N’distribution’;
SET @publisher = N’S-ESN-SQL1\SQL1′;
SET @publicationDB = N’AdventureWorks2008R2′;

— Disable the publication database.
USE [AdventureWorks2008R2]
EXEC sp_removedbreplication @publicationDB;

— Remove the registration of the local Publisher at the Distributor.
USE master
EXEC sp_dropdistpublisher @publisher;

— Delete the distribution database.
EXEC sp_dropdistributiondb @distributionDB;

— Remove the local server as a Distributor.
EXEC sp_dropdistributor;

Categories: Replication

The easiest way to recover of your replication

December 31, 2013 Leave a comment

Issue: User has database replication from one server to another over a rather thin link. They are out of sync so he would prefer to re-snapshot. Unfortunately the link is thin and performing a snapshot would incur huge transfer delays. He would not like to perform a snapshot but create a backup that can be hand carried. He would like to do ASAP on exiting replication model


Once you have replication configured, you should be able to re-create it easily in case of a disaster or if you want to deploy it to a client’s location. In Enterprise Manager, the easiest way to generate a SQL Script for replication is to right-click on the publication in the Replication group and selects Generate SQL Script. You are asked if you’d like to create a script for the removal of replication or the creation of replication. The script does the following:
• Set up the database for replication
• Create the publication
• Assign the appropriate permissions to the publication
• Add articles to the publication
• Create the subscription

If a disaster occurs on your publisher or subscriber servers, the easiest way to recover is to use the following steps:

1. Unsubscribe from the publisher.
2. Restore the most recent backup or backups.
3. Subscribe to the publisher.
4. Resynchronize the system.

Categories: Replication

Handle performance issue if SQL server 2008 database is involved in Marge Replication

If database is involved in replication then we have to troubleshoot the performance issue in a little bit different way. We can improve the Merge queries and it will improve the overall system performance for synchronizing data. Identify high IO or high CPU SPs then explore how to tune. Here I have posted the article along with query which will be definitely help out to tune the query

Use Replication Monitor to get overall view of Replication performance. Add –OUTPUT to Merge Agent to identify which step (stored procedure) in the Merge processes is failing or taking longest time.


Merge Replication automatically generates ReplMerge.log file which is ON by default. The minimal default logging may provide enough detail to resolve issue without needing to collect verbose log or Profiler trace.

C:\Program Files\Microsoft SQL Server\90\COM\replmerge.log

sys.dm_exec_query_stats can be used to examine query plans for the Merge Replication created SPs used to evaluate Publisher updates and to move data throughout the Merge topology. Looks for high CPU and high IO queries.

–Find TOP Top LogicalReaders for Merge Replication

–Examine Query Plans for SCANS

–Update STATS and/or REINDEX to see if better query plan is generated


st.text, qp.query_plan,

(qs.total_logical_reads/qs.execution_count) as avg_logical_reads,

(qs.total_logical_writes/qs.execution_count) as avg_logical_writes,

(qs.total_physical_reads/qs.execution_count) as avg_phys_reads,


FROM sys.dm_exec_query_stats as qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp

WHERE st.text like ‘%MSmerge%’

– WHERE st.text like ‘%sp_mergemetadataretentioncleanup%’

– WHERE st.text like ‘%sp_MSenumgenerations90%’

– WHERE st.text like ‘%sp_MSmakegeneration%’

ORDER BY qs.total_logical_reads DESC

Need to review the below system tables:

Often unexpected Synchronization performance can be contributed to unexpected high data volume. After all Merge is just queries against SQL table in order to determine which data to upload/download. Examine Merge system tables for counts 10 million (for example).

SELECT COUNT(*) FROM MSmerge_contents (nolock)

SELECT COUNT(*) FROM MSmerge_tombstone (nolock)

SELECT COUNT(*) FROM MSmerge_genhistory (nolock)

SELECT COUNT(*) FROM MSmerge_partition_groups (nolock)

SELECT COUNT(*) FROM MSmerge_current_partition_mappings (nolock)

SELECT COUNT(*) FROM MSmerge_past_partition_mappings (nolock)

Note: Try updating STATS or REINDEX on key Merge system tables then see if a better query plan (lower IO and CPU) is generated by Query Optimizer.

Replication History Tables

High sync duration could be result of unexpected high data volume. Use query below to see which date had mass amount of changes.

SELECT COUNT(*) ‘generation count’, sum(changecount) ‘change count’,

datepart(dd,coldate) ‘day of month’, datepart(hh,coldate) ‘hour of day’

FROM MSmerge_genhistory (nolock)

group by datepart(dd,coldate), datepart(hh,coldate)