OK – a few pointers to agent profiles, job parameters and such….
We usually find that our replication agents function quite nicely out of the box but at some stage we’ll need to tweak them. Perhaps they need to continue when an error is found, log more troubleshooting details to a file, have a longer query timeout and so on. This can be done by using one of the inbuilt profiles or by creating a custom one and setting up the values there. Either way we then assign the profile to the agent.
When we go down this route and take a look at agent profile parameters and compare to the list of agent executable parameters here it seems there is a big difference. Many are missing in our profile! First thing is to untick the checkbox at the bottom of the profile (below) and hey presto many more appear. Still not the full list of agent parameters, but 90% are there now. I’ll mention the missing 10% in a minute.
So, we create our profile, untick the checkbox, set values and set the agent to use it. Now maybe we have 100s of agents and many different profiles. It is quite possible that local subscriptions are optimised to use a different profile than remote subscriptions which have flaky connections. Some agents should continue on data consistency errors while others should fail and alert us. And so on. This one-to-many relationship between the profiles and agents is not represented in the Replication Monitor -we have to go into each agent to see which profile it is using. You can use the query below to see what is what:
[publication] as Publication
,c.srvname as SubscriberName
,b.profile_name as Profile
,[name] as DistributionJobName
FROM [distribution].[dbo].[MSdistribution_agents] a
inner join msdb.[dbo].[MSagent_profiles] b
on a.profile_id = b.profile_id
inner join master..sysservers c
on a.subscriber_id = c.srvid
order by b.profile_name
This returns a recordset which lists the agents and their profiles:
Remember that above I said 90% parameters are available when we untick the checkbox on the profile? There are some others such as “Output”, “Hostname”, “AltSnapshotFolder”, “UseInprocLoader” and a few more which have to be added to the job step directly as they don’t exist in the profile.
Now with this level of complexity – agents, different profiles, job step parameters and such we have a tricky situation. When an agent runs, which profile and parameters did it use, and what job values were used? We can use my query above to help, but perhaps the job parameter values have been changed since the job was run, and the agent might have been reset to use a different profile since then anyway, or the profile itself might have been changed. You can take a look in the distribution agent history but it is not detailed enough to help us and only gives basic info. To find out what our agent really used when it ran we need to check the job history.
You can see below that the job history separates the output into 2 logical sets of parameters – “user-supplied” and “from agent profile”. The profile name itself is not mentioned here (pity) but the particular values it used are, which anyway is the main level of detail we need.
One other point to note is that in the above example I’ve highlighted in blue the QueryTimeout parameter which has a value of 7000 seconds. Why? Because as an experiment I had added it to the agent job step and it was also in the profile with a different value of 3600 seconds. Now we see that the job step parameter takes priority and overrides the profile value when they are both entered. Really we should use one or the other and not mix the 2 methods and generally I’d advise to use the profile as it is more transparent.
I look at this output to confirm the values being used are the ones I expected to see. There are a few reasons for this. I might have many agents using a particular profile and just want to change one agent on an ad-hoc basis (and remove the parameter afterwards!), or I want to investigate why my custom profile value is not being applied or the parameter value is in the 10% not available in the profile and I just want to check my input value is being used…