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.
Cumulative Update 1 for SQL Server 2014 /en-us/help/2931693
Cumulative Update 9 for SQL Server 2012 SP1 /en-us/help/2931078
Cumulative Update 11 for SQL Server 2012 /en-us/help/2908007
Cumulative Update 9 for SQL Server 2008 R2 SP2 /en-us/help/2887606
About cumulative updates for SQL Server
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
- Latest cumulative update for SQL Server 2014
- Latest cumulative update for SQL Server 2012 SP1
- Latest cumulative update for SQL Server 2012
- Latest cumulative update for SQL Server 2008 R2 SP2
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