SQL Server Replication Queries
Replication
Replication Monitor
The MSreplication_monitordata
table in distribution
database contains cached data used by Replication Monitor. So you will not get the real time details on latency, performance, etc.
Replication alerts
Undistributed commands
The number of pending commands that are yet to be applied to the subscriber by distribution agent for a transactional publication. It also provides a rough estimate of how much time it takes to process them.
Undistributed commands for each table
If you want to find undelivered commands for each table in a publication, you can get the detail from MSdistribution_status
view from distribution database.
Find replication errors in error log.
Parameter | Value |
---|---|
Parameter 1 | 0 = Current error log, 1 = Previous error log |
Parameter 2 | 1 = SQL Server, 2 = SQL Server Agent |
Parameter 3 | Search keyword to filter the log |
Parameter 4 | Additional Search keyword to filter the log |
Agent History
Get details about snapshot agent history
Get details about log reader agent history
Get details about distribution agent history
Agents Detail
Get details about snapshot agents
Get details about log reader agents
Get details about distribution agents
Get details about merge agents
Get details about Q-reader agents
Get details about the agent profiles
Publisher Detail
Get the publisher database details
Subscriber Detail
Get details about subscriber
Get subscription details
Get subscriber schedule details
—
How to regenerate the sp_MS custom stored procedures in SQL Server
If you are adding a subscription using “initialize with LSN” option when the log reader agent is stopped, the auto generated stored procs will not be generated. You can use the below script to generate it.
This script will generate the below stored procedures for each of the table article that are replicated.
- sp_MSins*
- sp_MSupd*
- sp_MSdel*
Once you execute, you will get the stored procedure definitions. You can just copy that and execute it in subscriber database to create the stored procedures. The generated stored procedure’s schema is based on the publisher database’s schema.
Sample Output: