.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Sandeep Singh
Post New Web Links

Replication Distribution Agent: CXPACKET

Posted By:      Posted Date: October 18, 2010    Points: 0   Category :Sql Server
I have a transactional replication that is growing in latency. The performance is Critical. Please guide how i can go about troubleshooting the problem. Please provider detailed steps. Thanks.

View Complete Post

More Related Resource Links

Error: The replication agent has not logged a progress message in 10 minutes

Hello,      We are consistently getting the error message below on our subscribers that have blob images. Is there a way to increase a setting to avoid SQL to throw this error, or another suggestion? Thanks in advance.   John   Error messages: The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.   18 -BcpBatchSize 10000018 -ChangesPerHistory 10018 -DestThreads 218 -DownloadGenerationsPerBatch 518 -DownloadReadChangesPerBatch 10018 -DownloadWriteChangesPerBatch 10018 -FastRowCount 118 -HistoryVerboseLevel 318 -KeepAliveMessageInterval 30018 -LoginTimeout 1518 -MaxBcpThreads 218 -MaxDownloadChanges 018 -MaxUploadChanges 018 -MetadataRetentionCleanup 118 -NumDeadlockRetries 518 -PollingInterval 6018 -QueryTimeout 40018 -SrcThreads 218 -StartQueueTimeout 018 -UploadGenerationsPerBatch 318 -UploadReadChangesPerBatch 10018 -UploadWriteChangesPerBatch 10018 -Validate 018 -ValidateInterval 60   Due to the memory leak issue with respect to replicating blobbed images we have changed UploadGenerationsPerBatch to = 3.

Transactional Replication: Agent Profiles

Can someone explain the different Agent profiles and when is it appropriate to use one over the other?  Agent profile, continue on data consistency error...when can this be used?

TX Replication Error: Agent set to verbose 2


Tx Replication is giving an error

Command attempted:

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

Error messages:

2010-09-16 20:10:17.543 Microsoft SQL Server Log Reader Agent 10.0.2531.0
2010-09-16 20:10:17.597 Copyright (c) 2008 Microsoft Corporation
2010-09-16 20:10:17.622 Microsoft SQL Server Replication Agent: logread
2010-09-16 20:10:17.647
2010-09-16 20:10:17.663 The timestamps prepended to the output lines are expressed in terms of UTC time.
2010-09-16 20:10:17.717 User-specified agent parameter values:
   -Publisher Server_A
   -PublisherDB db_A
   -Distributor Server_b
   -DistributorSecurityMode 1
   -Output C:\Users\xyz\Desktop\OUTPUTFILE.txt
   -Outputverboselevel 1
   -XJOBID 0x052684F1D410C746B5AFE798220E5440
   -XJOBNAME Server_A-db_A-5
   -XSUBSYSTEM LogReader
   -XSERVER Server_A
   -XCancelEventHandle 0000000000000960
   -XParentProcessHandle 00000000000009F0

Distribution Agent Logging


I'm on SQL 2005.

How do you enable extra logging for the distribution agent? This is for troubleshooting.

I tried adding this to the command line for the distribution agent job, but hasn't created the file, so I assume logging isn't working.

-output E:\ReplicationDistLog.txt -outputverboselevel 2

How to name the agent job during setup replication


hi, there

I am setup a new DB replication, during which I discover that there is no place to name the agent jobs for publisher, snapshot and subscriber, system wizard will name these jobs for me. But these name are ramdon and I wish they can be finalized before setup. Is there any way to do this work?

I have tried, 1) rename them after setup, but it seems risky since internally replication will interact with these jobs through name. 2) Setup place holder jobs before replication setup, and let new jobs overwrite these jobs, it does not work as wished and some of the job setting is not applied to new job.

Could any one help me out? And it will be great if your way can be done programmatically.


log reader agent stopped in transactional replication


hello frnds

I got this error in transactional replication in sql server

Error:The process could not execute 'sp_repldone/sp_replcounters' on 'FS001XSSQL110'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)

Get help: http://help/MSSQL_REPL20011

The log scan number (54862:18189:10) passed to log scan in database 'Affinity' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. (Source: MSSQLServer, Error number: 9003)

Get help: http://help/9003

The process could not set the last distributed transaction. (Source: MSSQL_REPL, Error number: MSSQL_REPL22017)

Get help: http://help/MSSQL_REPL22017

The process could not execute 'sp_repldone/sp_replcounters' on 'FS001XSSQL110'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)

Get help: http://help/MSSQL_REPL22037

this is what i am getting when i viewed the log reader agent in replication monitor.



SQL 2008. Merge replication. Snapshot agent. Access Denied

Windows Server 2008 Standard x64 SP1, SQL Server 2008 Enterprise Edition x64 SP1
Snapshot agent has read-write permissions to ReplData folder but cannot access local snapshot folder. How to resolve this error?

Error messages:
Source: mscorlib
Target Site: Void WinIOError(Int32, System.String)
Message: Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\ReplData\unc\ServerName_DatabaseName_PublicationName\DateTime\' is denied.
Stack:    at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.Directory.InternalCreateDirectory(String fullPath, String path, DirectorySecurity dirSecurity)
   at System.IO.Directory.CreateDirectory(String path, DirectorySecurity directorySecurity)
   at Microsoft.SqlServer.Replication.Utilities.CreateDirectoryWithExtendedErrorInformation(String directory)
   at Microsoft.SqlServer.Replication.Snapshot.SnapshotProvider.CreateSnapshotFolders()
   at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.CreateSnapshotFolders()
   at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
   at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
   at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: mscorlib, Error number: 0)
Get help: http

How should be the Replication agent privileges ?

I'm going to setup the transactional replication in sql server 2008.and publisher ,distributer and subscriber are going to place on different servers. I want to know, how should be the privileges for sql agents for all servers?Is any custom important setting when we install the sql server for distributor ?

How should be the Replication agent privileges ?

I'm going to setup the replication in sql server 2008.and publisher ,distributor and subscriber are going to place on different servers. I want to know, how should be the privileges for sql agents for all servers?Is any custom important setting when we install the sql server for distributor ?

Replication Log Reader Agent Error - No Information


SQL Server 2005 SP3, I have created an Oracle Publication for transactional replication.  All seems to be working, even getting transaction updates from Oracle. In replication monitor Log Reader Agent status is "Error".  Last action is "applying retries....".  When I view details, window is empty - though once before (when I saw details), details told me to check job history.  Job history shows - "The replication agent encountered an error and is set to restart within the job step retry interval. See the previous job step history message or Replication Monitor for more information."

I understand that the Log Reader is moving data change info to distribution and it seems important to understand the errors.  The Log Reader agent is using the SQL Agent Service Account.

Log Reader Agent profile properties are default.

Here are details of error:

Details in job step message:

Date  10/29/2010 11:23:54 AM
Log  Job History (PULPDEV-PULPDEV-19)

Step ID  2
Step Name  Run agent.
Duration  01:06:07
Sql Severity  0
Sql Message ID  0
Operator Emailed  
Operator Net sent  

The best way to prevent an agent job to run in the middle of a merge replication.



I would like to configure a SQL agent job such that it doesn’t run in the middle of merge replication synchronization. Means when a merge replication is taking place, the job have to wait, or just run again until merge replication is done.

What would be the best way to do this?

Thank you.

SQL SERVER AGENT ACCOUNT: Replication Question

SQL Server Agent is running on a <Local Service> account. I need to change it so that it can run on SQL SERVER's Domain Account. Replication has been configured on this server with a remote distributor. Will this change cause any errors? Thank you. 

Replication-Replication Distribution Subsystem scheduled for retry.



We have configured a Transactional Replication between two servers, information as follows, The Replication environment has absolutely no issues before Friday night. Suddenly we have received Alerts every couple of minutes and the distributor Job is keep on re trying.


We hope there is a network or Security Configuration change made in the Subscriber Server for this communication failure.


Is anyone please help me to resolve the issue would be very much appreciated.

Testing replication agent


Hi all,

I've a transactional replication scenario build on SQL Server 2005, I've configured alert for various situations such as "Replication: agent failure"and so on. I've configured e-mail notification for theese alerts, but when the replication agents fail I get no e-mail messages. How can I test theese alerts? Is there any log for the e-mail notification?

Programmatically changing a merge replication agent job


Is there a supported way to change a merge replication agent job programmatically?  I know how I can do it in SSMS, but I would like to be able to script it.  I basically want to do the following:

  1. Change a replication job from uni-directional merge replication to bi-directional merge replication by removing "-EXCHANGETYPE 1" from the agent command
  2. Reinitialize the subscription tied to the replication job
  3. Wait some period of time for the merge replication job to start running again
  4. Change the replication job back from bi-direction merge replication to uni-directional merge replication by adding "-EXCHANGETYPE 1" from the agent command



Rename replication distribution agents using the Job_ID in MSDistribution_agents


Hi All,

I am trying to generate a scipt to rename the replication distribution agent jobs using the MSDistribution_agents table. I have found a way to rename the snapshot jobs on the Publisher based on the information stored in the job_id field in the MSsnapshot_agents table and I was hoping to do the same for the distribution jobs using the job_id and the information stored in the MSDistribution_agents table.

The problem is that the distribution agents are running on a different database on a different box and there is no way that I can associated the job_id in the MSDistribution table with any of the job_ids in the msdb.sysjobs on the remote server.

Can anyone tell me if the job_id stored in the MSdistribution_agents table corresponds to any of the objects on the remote servers? I need to do this on more than 6 servers so it is very tedious trying to manually rename the jobs everytime I have to fix/setup replication.

Thanks in advance for looking at this

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend