Feeds:
Posts
Comments

Posts Tagged ‘MSdistribution_agents’

Quick one today:

In our lab, quite frequently, we need to cross check the replication distribution agent profile settings between each environment to be sure they are set correctly. Replication Monitor provides a good way to check each agent and its profiles, but when you have many, many distribution agents in each environment, manual check is not fun.

This could be done by collecting all agent profile parameters using T-SQL

--
-- Run this query pointed to the Distributor
--
SELECT    D.id
		, D.name
		, D.publisher_database_id
		, D.publisher_id
		, D.publisher_db
		, D.publication
		, D.subscriber_id
		, D.subscriber_db
		, D.subscription_type
		, D.profile_id
		, D.creation_date
		, D.subscriber_security_mode
		, D.subscriptionstreams

		, CASE	WHEN P.agent_type = 1 THEN 'Snapshot Agent'
				WHEN P.agent_type = 2 THEN 'Log Reader Agent'
				WHEN P.agent_type = 3 THEN 'Distribution Agent'
				WHEN P.agent_type = 4 THEN 'Merge Agent'
				WHEN P.agent_type = 9 THEN 'Queue Reader Agent'
				ELSE 'Unknown'
		  END AS [AgentType]
		, P.profile_name
		, CASE	WHEN P.type = 0 THEN 'System Profile'
				WHEN P.type = 1 THEN 'User Defined Profile'
				ELSE 'UNKNOWN'
		  END AS [ProfileType]

		, PML.parameter_name
		, PM.value AS [ParameterValue_InUse]

		, PML.default_value
		, PML.min_value
		, PML.max_value

FROM dbo.MSdistribution_agents AS D
INNER JOIN msdb.dbo.MSagent_profiles AS P
	ON D.profile_id = P.profile_id
	AND P.agent_type = 3 -- Distribution agent

INNER JOIN msdb.dbo.MSagent_parameters AS PM
	ON PM.profile_id = D.profile_id

INNER JOIN msdb.dbo.MSagentParameterList AS PML
	ON PML.agent_type = P.agent_type -- Distributor
	AND PML.parameter_name = SUBSTRING(PM.parameter_name, 2, 50)
GO

Result looks something like this:

Distributor_AgentProfiles.PNG

Hope this helps,
_Sqltimes

Read Full Post »

Interesting article today on troubleshooting replication errors.

A few weeks ago, on production, we received an alert with on replication failures. Upon further inspection the error looks like this:

Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x001031C200001B06000700000000, Command ID: 1)

Error messages:
The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)
Get help: http://help/20598
The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)
Get help: http://help/20598

Identify Root Cause:

To understand the course of action, we need to first understand the underlying issues. Go to Replication Monitor >> Open Details Window on the subscription that has errors. Go to ‘Distributor to Subscriber’ tab for more details. See the image below:

replication_error_20598

Now we see that, replication is attempting to perform an action on the Subscriber, but the row does not exist. So, lets find out more.

Find the exact command that is being replicated (or executed on Subscriber as part of replication) that throws this error. Use replication procedure sp_browsereplcmds for that.

Query the Distribution agent ID from dbo.MSdistribution_agents and use it in the query below.

--
-- Uncover the replication command throwing error
--
EXEC sp_browsereplcmds @xact_seqno_start = '0x001031C200001A620004000000000000'
                     , @xact_seqno_end = '0x001031C200001A620004000000000000'
                     , @agent_id = 49
                     , @publisher_database_id = 3
GO

You’ll see something like this:

replication_error_20598_investigation

Under the command column, we’ll see the exact command that is running into this error.

--
--  Error 20598 occurring in
--
{CALL [mtx_rpldel_ReportCriterion] (908236,71357,250,-1)}

Now, lets go to that stored procedure ‘mtx_rpldel_ReportCriterion’ and see what tables are involved in manipulation. In my scenario, the table ReportCriterion does not have the record with ID = 908236

Resolution

Once you understand the root cause, we have a few options.

  1. Data Integrity: Looks like we have synchronization issues between Publisher and Subscriber. If it is a non-production environment or an environment where reinitializing is an option, then we could take that route to sync up the data first.
    1. Once data integrity issues are resolved, all subsequent replication commends would be successful.
  2. Manual fix: Manually insert the missing record at Subscriber and then allow replication to perform its operations on the new record.
    1. With this option, the more records we uncover as missing, the more manual operation would be required. Its not ideal, but it is a workaround to get things going again.
  3. Ignore, for now: In some situations, until further solution is identified, we may need to ignore this one record and move forward with rest of the replication commands.
    1. Take necessary precautions to make sure there are no more such missing records. Or gather a list of all missing ones.
    2. Configure replication to ignore error 20598 using skiperrors parameter. There are a couple of ways to achieve this; here we’ll look at one.
    3. Go to the Agent Profile for this particular Distributor Agent. One of the profiles allows us to skip certain errors. See the image below.
    4. replication_error_20598_resolution

For more information, please refer to Microsoft Support article on similar issue.

Hope this helps,
_Sqltimes

Read Full Post »