Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automate SQL Server Replication Monitoring


By:   |   Updated: 2016-10-19   |   Comments (13)   |   Related: More > Replication

Problem

In my environment we have a few servers where we have SQL Server replication setup and we need to make sure replication is always in sync, because business users connect to these servers for current up to date data. In this tip I will show a process we put in place to notify us of any issues with SQL Server replication.

Solution

We can automate SQL Server replication monitoring in this step by step process that will send email notifications.  This will be done using a centralized server to collect the data and send out the notification email.

SQL Server Replication Notification via Email

Step 1 - Create Linked Servers

Create a linked server to each distributor that is part of replication.  These should be created on your centralized monitoring server.  Also, test each linked server to make sure you can successfully connect.

Step 2 - Create Table to Store the List of Servers

Create a table to store details of each distributor server which is used for replication.

CREATE TABLE [dbo].[Distributor_Servers]([S[SN] [int] IDENTITY(1,1) NOT NULL,
[DISTRIBUTOR] [sysname] NULL) ON [PRIMARY]

GO

Step 3 - Insert Records into Table

Insert records into this table of all distributors as shown below:

Step 4 - Create Stored Procedure on each Replication Distributor

 Create the below stored procedure on each distributor to pull the replication information.  I recommend creating this in a database specifically for database administration tasks.  I have a database called DBA on each of my instances where I put objects like this.

CREATE PROCEDURE [dbo].[usp_GetReplicationMonitorData][email protected] VARCHAR(5000) OUTPUT
AS
BEGIN

SET NOCOUNT ON
DECLARE @Sqltext NVARCHAR(MAX)


DECLARE @Publisher SYSNAME, @PublisherDB SYSNAME, @Publication SYSNAME, @Distdb SYSNAME, 
@Subscriber SYSNAME, @SubscriberDB SYSNAME

IF OBJECT_ID('tempdb..#REPLMONITORPUBLISHER') IS NOT NULL 
DROP TABLE #REPLMONITORPUBLISHER 

CREATE TABLE #REPLMONITORPUBLISHER
(
SN INT IDENTITY(1,1)
,PUBLISHER SYSNAME NULL
,DISTRIBUTION_DB SYSNAME NULL
,STATUS INT NULL
,WARNING INT NULL
,PUBLICATIONCOUNT INT NULL
,RETURNSTAMP VARCHAR(100) NULL
)


IF OBJECT_ID('tempdb..#REPLMONITORSUBSCRIPTION') IS NOT NULL 
DROP TABLE #REPLMONITORSUBSCRIPTION 

CREATE TABLE #REPLMONITORSUBSCRIPTION
(
SN INT IDENTITY(1,1)
,PUBLISHER SYSNAME NULL
,DISTRIBUTION_DB SYSNAME NULL
,STATUS INT NULL
,WARNING INT NULL
,SUBSCRIBER SYSNAME NULL
,SUBSCRIBER_DB SYSNAME NULL
,PUBLISHER_DB SYSNAME NULL
,PUBLICATION SYSNAME NULL
,PUBLICATION_TYPE INT NULL
,SUBTYPE INT NULL
,LATENCY INT NULL
,LATENCYTHRESHOLD FLOAT NULL
,AGENTNOTRUNNING INT NULL
,AGENTNOTRUNNINGTHRESHOLD INT NULL
,TIMETOEXPIRATION INT NULL
,EXPIRATIONTHRESHOLD INT NULL
,LAST_DISTSYNC DATETIME NULL
,DISTRIBUTION_AGENTNAME SYSNAME NULL
,MONITORRANKING INT NULL
,DISTRIBUTIONAGENTJOBID BINARY(16) NULL
,DISTRIBUTIONAGENTID INT NULL
,DISTRIBUTIONAGENTPROFILEID INT NULL
,LOGREADERAGENTNAME SYSNAME NULL
)

IF OBJECT_ID('tempdb..#REPLLOGREADERAGENT') IS NOT NULL 
DROP TABLE #REPLLOGREADERAGENT 

CREATE TABLE #REPLLOGREADERAGENT
(
SN INT IDENTITY(1,1)
,DBNAME SYSNAME NULL
,NAME SYSNAME NULL
,STATUS INT NULL
,PUBLISHER SYSNAME NULL
,PUBLISHER_DB SYSNAME NULL
,START_TIME DATETIME NULL
,TIME DATETIME NULL
,DURATION INT NULL
,COMMENTS NVARCHAR(1000)
,DELIVERY_TIME INT NULL
,DELIVERED_TRANSACTIONS INT NULL
,DELIVERED_COMMANDS INT NULL
,AVERAGE_COMMANDS INT NULL
,DELIVERY_RATE INT NULL
,DELIVERY_LATENCY INT NULL
,ERROR_ID INT NULL
,JOB_ID BINARY(16) NULL
,LOCAL_JOB INT NULL
,PROFILE_ID INT NULL
,AGENT_ID INT NULL
,LOCAL_TIMESTAMP BINARY(8) NULL
)

IF OBJECT_ID('tempdb..#REPLDISTRIBUTORAGENT') IS NOT NULL 
DROP TABLE #REPLDISTRIBUTORAGENT 

CREATE TABLE #REPLDISTRIBUTORAGENT
(
SN INT IDENTITY(1,1)
,DBNAME SYSNAME NULL
,NAME SYSNAME NULL
,STATUS INT NULL
,PUBLISHER SYSNAME NULL
,PUBLISHER_DB SYSNAME NULL
,PUBLICATION SYSNAME NULL
,SUBSCRIBER SYSNAME NULL
,SUBSCRIBER_DB SYSNAME NULL
,SUBSCRIPTION_TYPE INT NULL
,START_TIME DATETIME NULL
,TIME DATETIME NULL
,DURATION INT NULL
,COMMENTS NVARCHAR(1000)
,DELIVERY_TIME INT NULL
,DELIVERED_TRANSACTIONS INT NULL
,DELIVERED_COMMANDS INT NULL
,AVERAGE_COMMANDS INT NULL
,DELIVERY_RATE INT NULL
,DELIVERY_LATENCY INT NULL
,ERROR_ID INT NULL
,JOB_ID BINARY(16) NULL
,LOCAL_JOB INT NULL
,PROFILE_ID INT NULL
,AGENT_ID INT NULL
,LOCAL_TIMESTAMP BINARY(8) NULL
,OFFLOAD_ENABLED BIT NULL
,OFFLOAD_SERVER VARCHAR(50)
,SUBSCRIBER_TYPE INT NULL
)

INSERT INTO #REPLMONITORPUBLISHER
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;',
'set fmtonly off exec sp_replmonitorhelppublisher') 

DECLARE CURSORPUBLISHER CURSOR FOR
SELECT PUBLISHER, DISTRIBUTION_DB 
FROM #REPLMONITORPUBLISHER;

OPEN CURSORPUBLISHER

FETCH NEXT FROM CURSORPUBLISHER
INTO @Publisher, @Distdb

WHILE @@FETCH_STATUS = 0
BEGIN

SET @Sqltext = 'SELECT ''' + @Publisher + ''', ''' + @Distdb + ''', STATUS, WARNING, 
SUBSCRIBER, SUBSCRIBER_DB,PUBLISHER_DB, PUBLICATION , PUBLICATION_TYPE, SUBTYPE, 
LATENCY, LATENCYTHRESHOLD, AGENTNOTRUNNING, AGENTNOTRUNNINGTHRESHOLD, 
TIMETOEXPIRATION, EXPIRATIONTHRESHOLD, LAST_DISTSYNC, DISTRIBUTION_AGENTNAME, 
MONITORRANKING, DISTRIBUTIONAGENTJOBID, DISTRIBUTIONAGENTID, 
DISTRIBUTIONAGENTPROFILEID, LOGREADERAGENTNAME FROM 
OPENROWSET (''SQLOLEDB'',''Server=(local);TRUSTED_CONNECTION=YES;'', 
''set fmtonly off EXEC ' + @Distdb + '..sp_replmonitorhelpsubscription @publisher = '''''
+ @Publisher +''''', @publication_type = 0 '') ' 
INSERT INTO #REPLMONITORSUBSCRIPTION (PUBLISHER, DISTRIBUTION_DB, STATUS, WARNING, 
SUBSCRIBER, SUBSCRIBER_DB, PUBLISHER_DB, PUBLICATION ,PUBLICATION_TYPE, SUBTYPE, 
LATENCY, LATENCYTHRESHOLD, AGENTNOTRUNNING, AGENTNOTRUNNINGTHRESHOLD, 
TIMETOEXPIRATION, EXPIRATIONTHRESHOLD, LAST_DISTSYNC, DISTRIBUTION_AGENTNAME, 
MONITORRANKING, DISTRIBUTIONAGENTJOBID, DISTRIBUTIONAGENTID, DISTRIBUTIONAGENTPROFILEID,
LOGREADERAGENTNAME)
EXEC (@Sqltext)

FETCH NEXT FROM CURSORPUBLISHER
INTO @Publisher, @Distdb
END

CLOSE CURSORPUBLISHER;
DEALLOCATE CURSORPUBLISHER;


INSERT INTO #REPLLOGREADERAGENT
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;',
'set fmtonly off EXEC sp_MSenum_replication_agents @type = 2') 

INSERT INTO #REPLDISTRIBUTORAGENT 
SELECT * FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;', 
'set fmtonly off EXEC sp_MSenum_replication_agents @type = 3') 

-- Default latency threshold is 30 sec. 
--It is common to have 1-2 minute Publisher to Subscriber latency in replication. 
-- With default threshold, we are getting frequent latency alerts. 
--Hence changing latency threshold to 120 sec for monitoring solution.

UPDATE #REPLMONITORSUBSCRIPTION SET LATENCYTHRESHOLD = 120

SELECT S.PUBLISHER, S.PUBLISHER_DB, S.PUBLICATION, S.DISTRIBUTION_DB, S.SUBSCRIBER, 
S.SUBSCRIBER_DB, S.LATENCY, 
S.LAST_DISTSYNC,
CASE WHEN (S.LATENCY/S.LATENCYTHRESHOLD)* 100 < 34 THEN 'Excellent' 
WHEN (S.LATENCY/S.LATENCYTHRESHOLD)* 100 < 59 THEN 'Good' 
WHEN (S.LATENCY/S.LATENCYTHRESHOLD)* 100 < 84 
THEN 'Fair' WHEN (S.LATENCY/S.LATENCYTHRESHOLD)* 100 < 99 
THEN 'Poor' ELSE 'Critical' END AS PERFORMANCE,
CASE S.WARNING WHEN 0 THEN 'NA' WHEN 1 THEN 'Expiration' 
WHEN 2 THEN 'Latency' ELSE 'Unknown' END AS WARNING,
CASE L.STATUS WHEN 1 THEN 'Started' WHEN 2 THEN 'Stopped' 
WHEN 3 THEN 'In Progress' WHEN 4 THEN 'Idle' WHEN 5 
THEN 'Retrying' WHEN 6 THEN 'Failed' ELSE 'Unknown' END AS LOGREADERAGENTSTATUS,
CASE D.STATUS WHEN 1 THEN 'Started' WHEN 2 THEN 'Stopped' WHEN 3 THEN 'In Progress' 
WHEN 4 THEN 'Idle' WHEN 5
THEN 'Retrying' WHEN 6 THEN 'Failed' ELSE 'Unknown' END AS DISTRIBUTIONAGENTSTATUS
FROM #REPLMONITORSUBSCRIPTION S 
LEFT OUTER JOIN #REPLLOGREADERAGENT L ON S.LOGREADERAGENTNAME = L.NAME 
AND S.DISTRIBUTION_DB = L.DBNAME
AND S.PUBLISHER = L.PUBLISHER AND S.PUBLISHER_DB = L.PUBLISHER_DB
LEFT OUTER JOIN #REPLDISTRIBUTORAGENT D ON S.DISTRIBUTION_AGENTNAME = D.NAME 
AND S.DISTRIBUTION_DB = L.DBNAME AND S.PUBLISHER = L.PUBLISHER 
AND S.PUBLISHER_DB = L.PUBLISHER_DB 
AND S.PUBLICATION = D.PUBLICATION AND S.SUBSCRIBER = D.SUBSCRIBER 
AND S.SUBSCRIBER_DB = D.SUBSCRIBER_DB

DROP TABLE #REPLMONITORPUBLISHER 
DROP TABLE #REPLMONITORSUBSCRIPTION 
DROP TABLE #REPLLOGREADERAGENT 
DROP TABLE #REPLDISTRIBUTORAGENT 

END


GO

Step 5 - Create Stored Procedure on Centralized Collection Server

Create this replication monitoring stored procedure on the centralized monitoring server to collect replication status information, consolidate the results and send email notification. I recommend creating this in a database specifically for database administration tasks.  I have a database called DBA on each of my instances where I put objects like this.

CREATE PROCEDURE [dbo].[usp_GetReplicationStatus]@mode BIT = 0 
AS
BEGIN


SET NOCOUNT ON
DECLARE @Recipients VARCHAR(275)
DECLARE @MailSubject VARCHAR(275) 
DECLARE @Xml NVARCHAR(MAX)
DECLARE @Mailtext NVARCHAR(MAX)
DECLARE @Server VARCHAR(25) 
DECLARE @Curdate DATETIME
DECLARE @MailString NVARCHAR(MAX)
DECLARE @Note NVARCHAR(1000) 
DECLARE @Sql VARCHAR (200)
DECLARE @Distributor SYSNAME 

SET @Recipients = '[email protected]'

IF OBJECT_ID('tempdb..#REPLICATIONMONITOR') IS NOT NULL 
DROP TABLE #REPLICATIONMONITOR 

CREATE TABLE #REPLICATIONMONITOR
(
SN INT IDENTITY(1,1)
,PUBLISHER SYSNAME NULL
,PUBLISHER_DB SYSNAME NULL
,PUBLICATION SYSNAME NULL
,DISTRIBUTION_DB SYSNAME NULL
,SUBSCRIBER SYSNAME NULL
,SUBSCRIBER_DB SYSNAME NULL
,LATENCY INT NULL
,LAST_DISTSYNC DATETIME NULL
,PERFORMANCE VARCHAR(20) NULL
,WARNING VARCHAR(20) NULL
,LOGREADERAGENTSTATUS VARCHAR(50) NULL
,DISTRIBUTIONAGENTSTATUS VARCHAR(50) NULL
)

DECLARE DistServer CURSOR FOR
SELECT DISTRIBUTOR FROM Distributor_Servers;

OPEN DistServer

FETCH NEXT FROM DistServer
INTO @Distributor

WHILE @@FETCH_STATUS = 0
BEGIN

SET @Sql = @Distributor + '.DBA.dbo.usp_GetReplicationMonitorData'
INSERT INTO #REPLICATIONMONITOR
EXEC (@Sql)

FETCH NEXT FROM DistServer
INTO @Distributor
END

CLOSE DistServer;
DEALLOCATE DistServer; 

SET @Mailtext ='<html>
<style type="text/css">
table.gridtable {
font-family: verdana,arial,sans-serif;
font-size:11px;
color:#000000;
border-width: 1px;
border-color: #666666;
border-collapse: collapse;
}
table.gridtable th {
border-width: 1px;
font-size:10px;
border-style: solid;
border-color: #666666;
}
table.gridtable td {
border-width: 1px;
font-size:10px;
border-style: solid;
border-color: #666666;
}
</style>
<body>
<table class="gridtable"> 
<tr bgcolor = ''''#808080''''>
<th>Publisher</th> <th>Publisher Database</th> <th>Publication</th> 
<th>Distribution Database</th>
<th>Subscriber</th><th>Subscriber Database</th><th>Latency</th>
<th>Performance</th><th>Warning</th><th>LogReader Agent Status</th>
<th>Distributor Agent Status </th><th>Last Distribution Sync</th>
</tr>' 

SET @Note = '<b>Note:</b><br/>
1. LogReader Agent and Distributor Agent status should always be either Idle or In Progress. 
If agent status has different value then verify it manually and rectify the issue.<br/>
2. Performance values are based on latency ratio (Latency/LatencyThreshold): 
Excellent (0-34%), Good (35-59%), Fair (60-84%), Poor (85-99%), Critical (100%+). 
We have used 120 sec as latency threshold.<br/>
3. Warning values: - i) Expiration - subscription not synchronized within retention period
threshold 
ii) Latency - Publisher to Subscriber latency has exceeded threshold iii) NA - No warnings
<br/></body></html>' 

IF @mode = 0 
BEGIN

SET @Xml = CAST(( SELECT 'tr/@bgcolor'= CASE PERFORMANCE WHEN 'Excellent' 
THEN '#90EE90' WHEN 'Good' THEN '#90EE90' WHEN 'Fair' THEN '#FFA500' 
ELSE '#FF4500' END,
td = PUBLISHER, '', td = PUBLISHER_DB , '', td = PUBLICATION, '', 
td = DISTRIBUTION_DB, '', td = SUBSCRIBER, '', 
td = SUBSCRIBER_DB, '', td = LATENCY, '', td = PERFORMANCE, '', 
td = WARNING, '', td = LOGREADERAGENTSTATUS, '', 
td = DISTRIBUTIONAGENTSTATUS, '', td = LAST_DISTSYNC, ''
FROM #REPLICATIONMONITOR ORDER BY PUBLISHER, LATENCY DESC
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) 

SET @Mailtext = @Mailtext + @Xml +'</table><br/>' + @Note 

SET @MailSubject = 'Replication Status Snapshot' 
SET @MailString = 'SQLDBAExperts.msdb.dbo.sp_send_dbmail @profile_name = 
''SQLDBAExpertsmail'', @recipients = ''' + @Recipients + ''', 
@subject = ''' + @MailSubject + ''', @body = ''' + @Mailtext + ''', 
@body_format = ''HTML'' '
EXEC (@MailString)

END
ELSE 
BEGIN 

IF EXISTS(SELECT 1 FROM #REPLICATIONMONITOR WHERE LOGREADERAGENTSTATUS NOT IN
('In Progress', 'Idle') 
OR DISTRIBUTIONAGENTSTATUS NOT IN ('In Progress', 'Idle') OR WARNING = 'Expiration' 
OR PERFORMANCE IN ('Poor', 'Critical'))
BEGIN

SET @Xml = CAST(( SELECT 'tr/@bgcolor' = CASE 1 WHEN 1 THEN '#FF4500' ELSE '#FF4500' END,
td = PUBLISHER, '', td = PUBLISHER_DB , '', td = PUBLICATION, '', td = DISTRIBUTION_DB, '',
td = SUBSCRIBER, '', 
td = SUBSCRIBER_DB, '', td = LATENCY, '', td = PERFORMANCE, '', td = WARNING, '', 
td = LOGREADERAGENTSTATUS, '', 
td = DISTRIBUTIONAGENTSTATUS, '', td = LAST_DISTSYNC, ''
FROM #REPLICATIONMONITOR WHERE LOGREADERAGENTSTATUS NOT IN ('In Progress', 'Idle') 
OR DISTRIBUTIONAGENTSTATUS NOT IN ('In Progress', 'Idle') OR WARNING = 'Expiration' 
OR PERFORMANCE IN ('Poor', 'Critical')
ORDER BY PUBLISHER, LATENCY DESC
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) 

SET @Mailtext = @Mailtext + @Xml +'</table><br/>' + @Note 

SET @MailSubject = 'Replication Error - Please check Status for Publications and 
Subscriptions listed in email' 
SET @MailString = 'SQLDBAExperts.msdb.dbo.sp_send_dbmail @profile_name =
''SQLDBAExpertsmail'', @recipients = ''' + @Recipients + ''', 
@subject = ''' + @MailSubject + ''', @body = ''' + @Mailtext + ''', 
@body_format = ''HTML'' '
EXEC (@MailString)

END


END

DROP TABLE #REPLICATIONMONITOR 

ENEND

Step 6 - Create SQL Agent Job on Centralized Server to Collect Data

Create a replication monitoring SQL Server Agent job on the centralized server to send replication status notifications at a particular interval.

This can be done using this script to create the job:

USE [msdb]GO

/****** Object: Job [Monitor Replication - Shiftwise] 
Script Date: 04/22/2016 03:53:50 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [2  DBA  On-Demand] Script Date: 04/22/2016 03:53:50 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE 
name=N'2  DBA  On-Demand' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', 
@name=N'2  DBA  On-Demand'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name= 
N'Monitor Replication - Shiftwise', 
@enabled=1, 
@notify_level_eventlog=0, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'This job will send Replication status report to DBA team 
for all the Publishers/Subscribers configured.', 
@category_name=N'2 - DBA - On-Demand', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Replication status] Script Date: 04/22/2016 03:53:50 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @[email protected], @step_name=N'Replication status', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'exec usp_GetReplicationStatus 0', 
@database_name=N'DBA', 
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @[email protected], 
@name=N'Replication Status Schedule', 
@enabled=1, 
@freq_type=4, 
@freq_interval=1, 
@freq_subday_type=8, 
@freq_subday_interval=8, 
@freq_relative_interval=0, 
@freq_recurrence_factor=0, 
@active_start_date=20120920, 
@active_end_date=99991231, 
@active_start_time=0, 
@active_end_time=235959, 
@schedule_uid=N'a4b8a45f-5b47-40f1-86e6-3a55dc395052'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Or you can create a new SQL Server Agent Job using the SSMS GUI as shown below:

Name the job:

Add a job step to call the replication monitoring stored procedure:

Add the stored procedure call in the job step:

Schedule the job to run as needed:

Replication Notification Emails

Now that the replication monitoring is in place, you will receive email notification like below based on the scheduled you setup. If there are replication issues, the email will highlight that row RED so it easily stands out.

Next Steps


Last Updated: 2016-10-19


get scripts

next tip button



About the author
MSSQLTips author Atul Gaikwad Atul Gaikwad has over 14+ years of experience with SQL Server. He currently works for one of the leading MNCs in Pune as a Delivery Manager.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, March 01, 2019 - 2:39:05 AM - Tu Back To Top

Great article.

You saved my day :)

Thanks a lot


Tuesday, November 27, 2018 - 9:20:43 PM - Venkat Back To Top

Hi Atul

  We have Replication configured for same server (SQL 2008 R2). Publisher , Distributor and Subscriber are same server. Could you please let me know the steps needs to be followed.


Tuesday, November 27, 2018 - 6:00:15 PM - Mothilal Rajesh Back To Top

*Msg 8164, Level 16, State 1, Procedure usp_GetReplicationMonitorData, Line 404 [Batch Start Line 2]

An INSERT EXEC statement cannot be nested.


Tuesday, November 27, 2018 - 5:48:57 PM - Mothilal Rajesh Back To Top

 Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 0]

The metadata could not be determined because statement 'exec @cmd N'if is_member(N''db_owner'') = 1 or isnull(is_member(N''replmonitor''),0) = 1 set @has_ac' in procedure 'sp_MSrepl_DistributorReplMonitorAccess'  contains dynamic SQL.  Consider using the WITH RESULT SETS clause to explicitly describe the result set.

Not working in SQL 2016 


Wednesday, March 21, 2018 - 12:42:02 PM - krishna Back To Top

Hi Atul,

I am implementing replication monitoring in SQL server 2014 version (named instance ) but when i run step 4 i am getting  below error.

OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Login timeout expired".

Can you please help me how to fix this issue.

 

Thanks,

Krishna

 

 

 

 


Monday, February 19, 2018 - 9:16:08 AM - SQLPRODDBA Back To Top

 Hi Atul,

I would like to thank you for sharing this wonderful script. It works perfectly fine and solved my replication monitoring task through TSQL. 

Great job and thanks once again!


Monday, November 27, 2017 - 4:16:51 PM - sudip Back To Top

 I am getting following error while executing the store proc [dbo].[usp_GetReplicationStatus]

 

 

Msg 8164, Level 16, State 1, Procedure usp_GetReplicationMonitorData, Line 401 [Batch Start Line 19]

An INSERT EXEC statement cannot be nested.

 


Wednesday, November 22, 2017 - 1:23:13 AM - David Back To Top

Great article.

 

I tried to set this up and I get an error on step 4 when trying to add the sp. SQL Server 2016 Is this good on all version?

Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 0]

The metadata could not be determined because statement 'exec @cmd N'if is_member(N''db_owner'') = 1 or isnull(is_member(N''replmonitor''),0) = 1 set @has_ac' in procedure 'sp_MSrepl_DistributorReplMonitorAccess'  contains dynamic SQL.  Consider using the WITH RESULT SETS clause to explicitly describe the result set.

 

 


Friday, July 28, 2017 - 2:33:59 PM - Vijay Back To Top

 Hi Atul,

I am getting below error while executing: Step 4 - Create Stored Procedure on each Replication Distributor

 But all linked servers are working fine.

OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Login timeout expired".

OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".


Friday, June 23, 2017 - 9:44:18 AM - Mastanvali Shaik Back To Top

 

 Hi ,

 

I have recently started using replication in one of my assignments and I thought to do some automation in that.

 

Over the internet I found this fantastic script which is in a standard way. I tried to use this script in my assignment which is having SQL Server 2012 enterprise edition. Unfortunately, I am getting some issues, please check and let us know if anything missed.

 

Problem area as below--

 

SET @Sql = @Distributor + '.DBA.dbo.usp_GetReplicationMonitorData'  ---from second stored procedure
INSERT INTO #REPLICATIONMONITOR
EXEC (@Sql)

 

In above part, if I execute dbo.usp_GetReplicationMonitorData procedure only output coming without any issues. But executing above part, it's not inserting data into the #REPLICATIONMONITOR table which is used to frame output in HTML format to send an email notification.

 

OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Deferred prepare could not be completed.".

 

Msg 8180, Level 16, State 1, Line 1

 

Statement(s) could not be prepared.

 

Msg 102, Level 15, State 1, Line 5

 

Incorrect syntax near ','.

 

Request you to have a look into this.

 

 

 


Thursday, June 22, 2017 - 12:17:46 PM - krishnam.raju Back To Top

 

 Hi,

 

I have used this script to set uo monitoring transactiona replication.

 

I have publisher and distribution on same server and one subscriber.

 

i have craeted linked server on different centralized server and have created a table on seperate database also inserted rows in that.

 

Have created usp_GetReplicationMonitorData on seperate databse on distributor.

 

Have created [dbo].[usp_GetReplicationStatus]@mode BIT = 0 on seperate databse on centralized server.

 

then  created sql job on centralized server.

 

but when I ran the job it gave me below

 

 Incorrect syntax near 'SQLDB'. [SQLSTATE 42000] (Error 102).  The step failed.

 

could you please help me on this

 


Monday, February 06, 2017 - 8:34:19 AM - Atul Gaikwad Back To Top

 

Apologies for the delayed response. You will encounter the issue while running this script on SQL 2012 as FMTOnly is not supported. Please use below script to create stored procedure on SQL 2012 and let me know if you still facing the issue.

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

ALTER PROCEDURE [dbo].[usp_GetReplicationMonitorData]

[email protected] VARCHAR(5000) OUTPUT

AS

BEGIN

 

SET NOCOUNT ON

DECLARE @Sqltext NVARCHAR(MAX)

 

 

DECLARE @Publisher SYSNAME, @PublisherDB SYSNAME, @Publication SYSNAME, @Distdb SYSNAME, @Subscriber SYSNAME, @SubscriberDB SYSNAME

 

IF OBJECT_ID('tempdb..#REPLMONITORPUBLISHER') IS NOT NULL  

    DROP TABLE #REPLMONITORPUBLISHER 

 

CREATE TABLE #REPLMONITORPUBLISHER

(

SN INT IDENTITY(1,1)

,PUBLISHER SYSNAME NULL

,DISTRIBUTION_DB SYSNAME NULL

,STATUS INT NULL

,WARNING INT NULL

,PUBLICATIONCOUNT INT NULL

,RETURNSTAMP VARCHAR(100) NULL

)

 

 

IF OBJECT_ID('tempdb..#REPLMONITORSUBSCRIPTION') IS NOT NULL  

    DROP TABLE #REPLMONITORSUBSCRIPTION 

 

CREATE TABLE #REPLMONITORSUBSCRIPTION

(

SN INT IDENTITY(1,1)

,PUBLISHER SYSNAME NULL

,DISTRIBUTION_DB SYSNAME NULL

,STATUS INT NULL

,WARNING INT NULL

,SUBSCRIBER SYSNAME NULL

,SUBSCRIBER_DB SYSNAME NULL

,PUBLISHER_DB SYSNAME NULL

,PUBLICATION SYSNAME NULL

,PUBLICATION_TYPE INT NULL

,SUBTYPE INT NULL

,LATENCY INT NULL

,LATENCYTHRESHOLD FLOAT NULL

,AGENTNOTRUNNING INT NULL

,AGENTNOTRUNNINGTHRESHOLD INT NULL

,TIMETOEXPIRATION INT NULL

,EXPIRATIONTHRESHOLD INT NULL

,LAST_DISTSYNC DATETIME NULL

,DISTRIBUTION_AGENTNAME  SYSNAME NULL

,MONITORRANKING INT NULL

,DISTRIBUTIONAGENTJOBID BINARY(16) NULL

,DISTRIBUTIONAGENTID INT NULL

,DISTRIBUTIONAGENTPROFILEID INT NULL

,LOGREADERAGENTNAME SYSNAME NULL

)

 

 

IF OBJECT_ID('tempdb..#REPLLOGREADERAGENT') IS NOT NULL  

    DROP TABLE #REPLLOGREADERAGENT 

 

CREATE TABLE #REPLLOGREADERAGENT

(

SN INT IDENTITY(1,1)

,DBNAME SYSNAME NULL

,NAME SYSNAME NULL

,STATUS INT NULL

,PUBLISHER SYSNAME NULL

,PUBLISHER_DB SYSNAME NULL

,START_TIME DATETIME NULL

,TIME DATETIME NULL

,DURATION INT NULL

,COMMENTS NVARCHAR(1000)

,DELIVERY_TIME INT NULL

,DELIVERED_TRANSACTIONS INT NULL

,DELIVERED_COMMANDS INT NULL

,AVERAGE_COMMANDS INT NULL

,DELIVERY_RATE INT NULL

,DELIVERY_LATENCY INT NULL

,ERROR_ID INT NULL

,JOB_ID BINARY(16) NULL

,LOCAL_JOB INT NULL

,PROFILE_ID INT NULL

,AGENT_ID INT NULL

,LOCAL_TIMESTAMP BINARY(8) NULL

)

 

IF OBJECT_ID('tempdb..#REPLDISTRIBUTORAGENT') IS NOT NULL  

    DROP TABLE #REPLDISTRIBUTORAGENT 

 

CREATE TABLE #REPLDISTRIBUTORAGENT

(

SN INT IDENTITY(1,1)

,DBNAME SYSNAME NULL

,NAME SYSNAME NULL

,STATUS INT NULL

,PUBLISHER SYSNAME NULL

,PUBLISHER_DB SYSNAME NULL

,PUBLICATION SYSNAME NULL

,SUBSCRIBER SYSNAME NULL

,SUBSCRIBER_DB SYSNAME NULL

,SUBSCRIPTION_TYPE INT NULL

,START_TIME DATETIME NULL

,TIME DATETIME NULL

,DURATION INT NULL

,COMMENTS NVARCHAR(1000)

,DELIVERY_TIME INT NULL

,DELIVERED_TRANSACTIONS INT NULL

,DELIVERED_COMMANDS INT NULL

,AVERAGE_COMMANDS INT NULL

,DELIVERY_RATE INT NULL

,DELIVERY_LATENCY INT NULL

,ERROR_ID INT NULL

,JOB_ID BINARY(16) NULL

,LOCAL_JOB INT NULL

,PROFILE_ID INT NULL

,AGENT_ID INT NULL

,LOCAL_TIMESTAMP BINARY(8) NULL

,OFFLOAD_ENABLED BIT NULL

,OFFLOAD_SERVER VARCHAR(50)

,SUBSCRIBER_TYPE INT NULL

)

 

INSERT INTO #REPLMONITORPUBLISHER

SELECT * FROM OPENROWSET

('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;',

' Exec sp_replmonitorhelppublisher WITH RESULT SETS 

(

(

Publisher SYSNAME ,

distribution_db SYSNAME,

status INT,

warning INT,

publicationcount INT,

returnstamp VARCHAR(100)

)

)

' )

 

 

DECLARE CURSORPUBLISHER CURSOR FOR

SELECT  PUBLISHER, DISTRIBUTION_DB 

FROM #REPLMONITORPUBLISHER;

 

 

OPEN CURSORPUBLISHER

 

FETCH NEXT FROM CURSORPUBLISHER

INTO @Publisher, @Distdb

 

WHILE @@FETCH_STATUS = 0

BEGIN

 

SET @Sqltext = 'SELECT ''' + @Publisher + ''', ''' + @Distdb + ''', STATUS, WARNING, SUBSCRIBER, SUBSCRIBER_DB,PUBLISHER_DB, 

PUBLICATION ,PUBLICATION_TYPE, SUBTYPE, LATENCY, LATENCYTHRESHOLD,AGENTNOTRUNNING, AGENTNOTRUNNINGTHRESHOLD, TIMETOEXPIRATION,

EXPIRATIONTHRESHOLD, LAST_DISTSYNC, DISTRIBUTION_AGENTNAME, MONITORRANKING, DISTRIBUTIONAGENTJOBID, DISTRIBUTIONAGENTID,

 DISTRIBUTIONAGENTPROFILEID, LOGREADERAGENTNAME 

 FROM OPENROWSET 

 (''SQLOLEDB'',''Server=(local);TRUSTED_CONNECTION=YES;'',

 ''EXEC ' + @Distdb + '.dbo.sp_replmonitorhelpsubscription   @publisher =  '''''+ @Publisher +''''', @publication_type = 0 

 WITH RESULT SETS 

(

(

STATUS INT NULL

,WARNING INT NULL

,SUBSCRIBER SYSNAME NULL

,SUBSCRIBER_DB SYSNAME NULL

,PUBLISHER_DB SYSNAME NULL

,PUBLICATION SYSNAME NULL

,PUBLICATION_TYPE INT NULL

,SUBTYPE INT NULL

,LATENCY INT NULL

,LATENCYTHRESHOLD FLOAT NULL

,AGENTNOTRUNNING INT NULL

,AGENTNOTRUNNINGTHRESHOLD INT NULL

,TIMETOEXPIRATION INT NULL

,EXPIRATIONTHRESHOLD INT NULL

,LAST_DISTSYNC DATETIME NULL

,DISTRIBUTION_AGENTNAME  SYSNAME NULL

,MERGEAGENTNAME SYSNAME NULL

,MERGESUBSCRIPTIONFRIENDLYNAME SYSNAME NULL

,MERGEAGENTLOCATION SYSNAME NULL

,MERGECONNECTIONTYPE SYSNAME NULL

,MERGEPERFORMANCE SYSNAME NULL

,MERGERUNSPEED SYSNAME NULL

,MERGERUNDURATION SYSNAME NULL

,MONITORRANKING INT NULL

,DISTRIBUTIONAGENTJOBID BINARY(16) NULL

,MERGEAGENTJOBID SYSNAME NULL

,DISTRIBUTIONAGENTID INT NULL

,DISTRIBUTIONAGENTPROFILEID INT NULL

,MERGEAGENTID SYSNAME NULL

,MERGEAGENTPROFILEID SYSNAME NULL

,LOGREADERAGENTNAME SYSNAME NULL

,PUBLISHER SYSNAME NULL

)

)

'') '

 

--PRINT (@Sqltext)

INSERT INTO #REPLMONITORSUBSCRIPTION (PUBLISHER, DISTRIBUTION_DB, STATUS, WARNING, SUBSCRIBER, SUBSCRIBER_DB,PUBLISHER_DB, PUBLICATION ,PUBLICATION_TYPE, SUBTYPE, LATENCY, LATENCYTHRESHOLD, 

AGENTNOTRUNNING, AGENTNOTRUNNINGTHRESHOLD, TIMETOEXPIRATION, EXPIRATIONTHRESHOLD, LAST_DISTSYNC, DISTRIBUTION_AGENTNAME, MONITORRANKING, DISTRIBUTIONAGENTJOBID, DISTRIBUTIONAGENTID, DISTRIBUTIONAGENTPROFILEID, LOGREADERAGENTNAME)

EXEC (@Sqltext)

 

FETCH NEXT FROM CURSORPUBLISHER

INTO @Publisher, @Distdb

END

 

CLOSE CURSORPUBLISHER;

DEALLOCATE CURSORPUBLISHER;

 

INSERT INTO #REPLLOGREADERAGENT

SELECT * FROM OPENROWSET 

('SQLOLEDB ','Server=(local);TRUSTED_CONNECTION=YES;', 

'EXEC sp_MSenum_replication_agents  @type = 2  WITH RESULT SETS 

(

(

dbname SYSNAME

,name SYSNAME

,status INT

,PUBLISHER SYSNAME 

,PUBLISHER_DB SYSNAME

,START_TIME DATETIME 

,TIME DATETIME 

,DURATION INT 

,COMMENTS NVARCHAR(1000)

,DELIVERY_TIME INT 

,DELIVERED_TRANSACTIONS INT 

,DELIVERED_COMMANDS INT 

,AVERAGE_COMMANDS INT 

,DELIVERY_RATE INT 

,DELIVERY_LATENCY INT 

,ERROR_ID INT 

,JOB_ID BINARY(16) 

,LOCAL_JOB INT 

,PROFILE_ID INT

,AGENT_ID INT 

,LOCAL_TIMESTAMP BINARY(8)

)

') 

 

 

INSERT INTO #REPLDISTRIBUTORAGENT 

SELECT * FROM OPENROWSET 

('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;', 

'EXEC sp_MSenum_replication_agents @type = 3 WITH RESULT SETS

(

(

DBNAME SYSNAME 

,NAME SYSNAME 

,STATUS INT 

,PUBLISHER SYSNAME 

,PUBLISHER_DB SYSNAME

,PUBLICATION SYSNAME 

,SUBSCRIBER SYSNAME

,SUBSCRIBER_DB SYSNAME 

,SUBSCRIPTION_TYPE INT

,START_TIME DATETIME 

,TIME DATETIME 

,DURATION INT 

,COMMENTS NVARCHAR(1000)

,DELIVERY_TIME INT 

,DELIVERED_TRANSACTIONS INT 

,DELIVERED_COMMANDS INT 

,AVERAGE_COMMANDS INT 

,DELIVERY_RATE INT 

,DELIVERY_LATENCY INT

,ERROR_ID INT 

,JOB_ID BINARY(16) 

,LOCAL_JOB INT 

,PROFILE_ID INT

,AGENT_ID INT 

,LOCAL_TIMESTAMP BINARY(8) 

,OFFLOAD_ENABLED BIT 

,OFFLOAD_SERVER VARCHAR(50)

,SUBSCRIBER_TYPE INT 

)

)

 ') 

 

 

 -- Default latency threshold is 30 sec. It is common to have 1-2 minute Publisher to Subscriber latency in replication. 

-- With default threshold, we are getting frequent latency alerts. Hence as per discussion with Praveen, changing latency threshold to 120 sec for monitoring solution.

 

UPDATE #REPLMONITORSUBSCRIPTION SET LATENCYTHRESHOLD = 120

 

SELECT S.PUBLISHER, S.PUBLISHER_DB, S.PUBLICATION, S.DISTRIBUTION_DB, S.SUBSCRIBER, S.SUBSCRIBER_DB, S.LATENCY, S.LAST_DISTSYNC,

CASE  WHEN (S.LATENCY/S.LATENCYTHRESHOLD)* 100 < 34 THEN 'Excellent'  WHEN (S.LATENCY/S.LATENCYTHRESHOLD)* 100 < 59 THEN 'Good' WHEN (S.LATENCY/S.LATENCYTHRESHOLD)* 100 < 84 THEN 'Fair' WHEN (S.LATENCY/S.LATENCYTHRESHOLD)* 100 < 99 THEN 'Poor' ELSE 'Critical' END AS PERFORMANCE,

CASE S.WARNING  WHEN 0 THEN 'NA' WHEN 1 THEN 'Expiration' WHEN 2 THEN 'Latency' ELSE 'Unknown' END AS WARNING,

CASE L.STATUS WHEN 1 THEN 'Started' WHEN 2 THEN 'Stopped' WHEN 3 THEN 'In Progress' WHEN 4 THEN 'Idle' WHEN 5 THEN 'Retrying' WHEN 6 THEN 'Failed' ELSE 'Unknown' END AS LOGREADERAGENTSTATUS,

CASE D.STATUS WHEN 1 THEN 'Started' WHEN 2 THEN 'Stopped' WHEN 3 THEN 'In Progress' WHEN 4 THEN 'Idle' WHEN 5 THEN 'Retrying' WHEN 6 THEN 'Failed' ELSE 'Unknown' END AS DISTRIBUTIONAGENTSTATUS

FROM #REPLMONITORSUBSCRIPTION S 

LEFT OUTER JOIN #REPLLOGREADERAGENT L ON S.LOGREADERAGENTNAME = L.NAME AND S.DISTRIBUTION_DB = L.DBNAME AND S.PUBLISHER = L.PUBLISHER AND S.PUBLISHER_DB = L.PUBLISHER_DB

LEFT OUTER JOIN #REPLDISTRIBUTORAGENT D ON S.DISTRIBUTION_AGENTNAME = D.NAME AND S.DISTRIBUTION_DB = L.DBNAME 

AND S.PUBLISHER = L.PUBLISHER AND S.PUBLISHER_DB = L.PUBLISHER_DB 

/**Code comment by aslam

--AND S.PUBLICATION = D.PUBLICATION 

Code comment by aslam **/

AND S.SUBSCRIBER = D.SUBSCRIBER AND S.SUBSCRIBER_DB = D.SUBSCRIBER_DB

 

DROP TABLE #REPLMONITORPUBLISHER 

DROP TABLE #REPLMONITORSUBSCRIPTION 

DROP TABLE #REPLLOGREADERAGENT 

DROP TABLE #REPLDISTRIBUTORAGENT 

 

END

 

 

 

 

 

 


Friday, October 28, 2016 - 12:32:56 AM - Eric Back To Top

I received the following error when i tried create the first stored procedure.

 

Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1

 

The metadata could not be determined because statement 'exec @cmd N'if is_member(N''db_owner'') = 1 or isnull(is_member(N''replmonitor''),0) = 1 set @has_ac' in procedure 'sp_MSrepl_DistributorReplMonitorAccess' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

 

 

 


Learn more about SQL Server tools