KB2861456 - FIX: Access violation in replication Distribution Agent in SQL Server Transactional Replication

Symptoms

Assume that you have a Microsoft SQL Server 2008 R2, SQL Server 2012, or SQL Server 2014 Transactional Replication environment. There are multiple distribution instances of SQL Server in the environment. In this situation, an access violation may occur, and the SQL Server replication distribution agent crashes. Additionally, a mini dump file is generated.

Resolution

After you apply the hotfix, the agent will shut down gracefully instead of crashing together with a dump file. You can add an additional job step in the Distribution Agent job to restart the agent automatically when the agent has stopped and has the "scheduled for restart" status.

For more information about how to add an additional job step in the Distribution Agent job, please refer to the More Information section.

The issue was first fixed in the following cumulative update of SQL Server.

Hotfix update package for SQL Server 2008 R2 Service Pack 1The fix for this issue was first released in the hotfix update package for SQL Server 2008 R2 Service Pack 1.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

More Information

After you apply the hotfix, add the following script to the Distribution Agent job:DECLARE @JobID BINARY(16)
DECLARE @AgentID int
DECLARE @command varchar(max)
DECLARE JobCur CURSOR FOR SELECT Job.job_id, Agent.id FROM msdb.dbo.sysjobs AS Job
INNER JOIN msdb.dbo.syscategories AS Cat ON Job.category_id = Cat.category_id
INNER JOIN msdb.dbo.sysjobsteps AS Steps ON Job.job_id = Steps.job_id
INNER JOIN distribution.dbo.MSdistribution_agents AS Agent ON Job.job_id = Agent.job_id
WHERE Job.name like '%Put Text Mask To Identify Your Publisher DB Jobs Here%' AND Cat.name = 'REPL-Distribution'
GROUP BY Job.job_id, Agent.id
HAVING COUNT(step_id) = 3
OPEN JobCur
FETCH NEXT FROM JobCur INTO @JobID, @AgentID
WHILE @@FETCH_STATUS = 0 BEGIN
IF (@JobID is not NULL) BEGIN
set @command = N'if (select top 1 runstatus from MSdistribution_history where agent_id = ' + convert(varchar, @AgentID)+ N' order by timestamp desc) = 5
raiserror(''Agent was scheduled for retry. Performing restart now'',16,1)'
EXEC msdb.dbo.sp_add_jobstep @job_id=@JobID, @database_name=N'distribution', @step_name=N'Check for retry', @step_id=4, @on_fail_action=4, @on_fail_step_id=1, @command=@command
EXEC msdb.dbo.sp_update_jobstep @job_id=@JobID, @step_id=2, @on_success_action=4, @on_success_step_id=4
END
FETCH NEXT FROM JobCur INTO @JobID, @AgentID;
END
CLOSE JobCur;
DEALLOCATE JobCur;
GO