Determine the replication agents’ status
Insight on replication agent status can be collected manually or programmatically. This article will consider both cases, and also when a replication agents’ status will be determined in relation with all publications, or a particular one.
Review the replication agents’ status in Replication Monitor (SQL Server Management Studio)
Expand the Replication and Local publications folders in Object Explorer, right-click on a desired publication, and choose Launch Replication Monitor option:
The Replication Monitor is a SQL Server Replication native module, which provides necessary information on vital aspects of the replication (e.g. publication and subscription). Along with other features like adding publisher, filtering various information, replication agents’ configuration and maintenance, it also continuously monitors the status of replication agents for particular or for all publications.
The dialog initially shows the information screen, at the first attempt (not shown). The selected branch in the picture above represents the publisher, and on the right, there are information about particular publication or publications, like status, is it in synchronizing process and other.
To review the replication agents’ status, choose the Agents tab in active view, and set Maintenance jobs agent type:
In this case, replication agents are not active (Not running status), and to check relevance of the information displayed, look at the last refresh timestamp in the upper-right corner of the dialog.
Actions which can be performed here are starting/stopping the replication agents, and drill into properties of the Replications agents’ checkup job:
If needed, the Replication agents’ checkup properties can be modified as a regular SQL Server job (refer to creating and maintaining job section from this article).
Replication agents’ status values reference
In the Replication Monitor, the agents’ status is descriptive: running or not running.
To identify the status value, use this as a reference:
Get information on replication agents’ status programmatically (T-SQL and PowerShell)
To collect replication agents’ status information, it can be achieved with two programmatic methods:
- Method 1: Querying the dbo.MSReplication_monitordata table from a distribution database, with this T-SQL script:
USE distribution SELECT status AS ReplicationAgentsStatus FROM dbo.MSReplication_monitordata WHERE publication = 'ALL'
A result of this query is a single numeric value (Idle status, in the case above), which represents the replication agents’ status for all publications.
To get information of the agents’ status for the particular publication, modify the where statement like this:
WHERE publication = <name_of_the_publication> --Names of the publications can be queried within publication column in the same table --e.g. SELECT publication FROM dbo.MSReplication_monitordata
Replication agents’ status for particular publication is Succeeded, in this case.
- Method 2: Filtering the Status column from system stored procedure sp_replmonitorhelppublication with this PowerShell script:
#Execute system stored procedure sp_replmonitorhelppublication, against the distribution database #Extract the whole set of results into temp1.txt file Invoke-Sqlcmd -Query "USE distribution EXEC sp_replmonitorhelppublication" -ServerInstance "TIKVICKI" | Out-File d:\temp1.txt #Filtering string which contains information on replication agents' status, and bridge it to another temporary file, temp2.txt Get-Content d:\temp1.txt | where { $_.Contains("status : ") } | out-file d:\temp2.txt #Modify and normalize previously mentioned string #Rename the file into Replication_Agents_Status and append the timestamp (moment of the execution) (Get-Content d:\temp2.txt).replace('status ', 'Replication agents status') | Set-Content d:\Replication_Agents_Status_$(get-date -f yyyy-MM-dd-hh-mm-ss).txt #Remove temporary files Remove-Item d:\temp1.txt -Force Remove-Item d:\temp2.txt -Force
This is how Replication_Agents_Status_(timestamp) should look:
This PowerShell script uses sp_replmonitorhelppublication execution to insert the whole result in the temporary text file. Next step is extracting and modifying the string which contains the information on replication agents’ status and parsing it into another temporary text file, which will become the reference file. Upon every execution of the script, it will rename the reference file into Replication_Agents_Status and append the timestamp. After the process, temporary files will be removed automatically.
Note: Due to the technical limitation of used stored procedure, it is not possible to simply filter the Status column only and return the single value with T-SQL, because of nested stored procedures execution.
Useful resources
February 24, 2017
