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:
_Sqltimes