Replication Data Collection Scripts
The Microsoft SQL Server Support team uses various scripts to collect Replication and Performance data from customer’s SQL Servers. These scripts extract data into either text files or into temporary databases which are then backed up and sent to Microsoft SQL Engineer for analysis. Below are links to some of these common data collection scripts.
Publisher/Distributor/Subscriber Metadata
This link contains 3 TSQL scripts to collect Publisher, Distributor and Subscriber replication metadata. These scripts each create a “MS_pub/dis/sub” database, select key replication tables into that database then backup the database to the C:\ drive. As needed, you can change the backup location at the end of the script.
MS_PublisherMetadata.sql
MS_DistributorMetadata.sql
MS_SubscriberMetadata.sql
Distributor Metadata
The attached script written by Jonathan Clark, Microsoft SQL Server Escalation Services team, creates a stored procedure called RSP_Get_Repl_Info which can then be executed to pull key replication data. The output can be directed to a text file.
Merge Replication Filter Logic
These scripts are used by the SQL Server Replication Support team to help analyze the Filter Logic used in Merge Replication. The first lists all defined Merge Subset and Join filters. The first listing shows published tables and filter logic. The second script analyzes the filters to ensure appropriate indexes are created to support these Filters. If you’re seeing performance problems with Merge Replication, begin investigating why the tables used in Join filter is missing an index.
TSQL Scripts to Analyze Merge Replication Filter Logic – Part 1
TSQL Scripts to Analyze Merge Replication Filter Logic – Part 2
