MSSQLSERVER Event Description:Replication-Replication Distribution Subsystem: agent scheduled for retry. Query timeout expired

Step 1:-  Check the locks on the DB
Step2 :- In my case it was one ALTER and Another INSERT Command.
Step3:- Make sure you check all blocked SPID's
Step4:- Once you resolve the blocking it will workfine.

Server is not configured for DATA ACCESS.

Step 1 :- Check server is listed in servers or not

Select * from sys.servers

Step 2 :- Add Server and Set it to true

EXEC sp_serveroption 'YourServer', 'DATA ACCESS', TRUE

 Step 3:- Verification

use Select * from sys.servers  verify field  “is_Data_Access_enabled” is set to 1.
 

Replication Agent Errors

--To get Agents that caused Error

SELECT  distinct da.name
FROM   MSpublications pub inner join MSsubscriptions subs
                                                ON pub.publication_id = subs.publication_id
                                inner join MSdistribution_agents da
                                                ON da.id = subs.agent_id
WHERE pub.publication = 'PublicationName'


-- To check the errors
select * from msdb..sysreplicationalerts 

-- Agent Errors
SELECT * FROM sys.messages WHERE message_id in ( 14151)

=================================================================

Message ID	Predefined alert	Condition causing the alert to fire	Enters additional information in msdb..sysreplicationalerts
14150	Replication: agent success	Agent shuts down successfully.	Yes
14151	Replication: agent failure	Agent shuts down with an error.	Yes
14152	Replication: agent retry	Agent shuts down after unsuccessfully retrying an operation (agent encounters an error such as server not available, deadlock, connection failure, or time-out failure).	Yes
14157	Replication: expired subscription dropped	Expired subscription was dropped.	No
20572	Replication: Subscription reinitialized after validation failure	Response job 'Reinitialize subscriptions on data validation failure' reinitializes a subscription successfully.	No
20574	Replication: Subscriber has failed data validation	Distribution or Merge Agent fails data validation.	Yes
20575	Replication: Subscriber has passed data validation	Distribution or Merge Agent passes data validation.	Yes
20578	Replication: agent custom shutdown
22815	Peer-to-peer conflict detection alert	Distribution Agent detected a conflict when it tries to apply a change at a peer-to-peer node.	Yes

I

History of a Job - T-SQL

Just change the Job name and Date, Run the query to get history of a job



SELECT      [JobName]   = JOB.name,

            [Step]      = HIST.step_id,

            [StepName]  = HIST.step_name,

            [Message]   = HIST.message,

            [Status]    = CASE WHEN HIST.run_status = 0 THEN 'Failed'

            WHEN HIST.run_status = 1 THEN 'Succeeded'

            WHEN HIST.run_status = 2 THEN 'Retry'

            WHEN HIST.run_status = 3 THEN 'Canceled'

            END,

            [RunDate]   = HIST.run_date,

            [RunTime]   = HIST.run_time,

            [Duration]  = HIST.run_duration

FROM        msdb..sysjobs JOB

INNER JOIN  msdb..sysjobhistory HIST ON HIST.job_id = JOB.job_id

WHERE    JOB.name = 'JOBNAMEEEE' and hist.run_date>'20130123'

ORDER BY    HIST.run_date, HIST.run_time 

Query to find insert/update on the table in any DB

Step1 . Replace your object name in the place of TABLENAME

 

Step2 . 

Run this

exec sp_MSforeachdb

';with cte as (

select db_id() as dbid,db_name() as dbname,

so.name,

sc.text

from

sysobjects so inner join syscomments sc on so.id = sc.id

where

sc.text like ''%INSERT%INTO%TABLENAME%''

or sc.text like ''%UPDATE%TABLENAME%''

and so.xtype = ''P''

union all

select  db_id() as dbid,db_name() as dbname,

so.name,

sc.text

from

sysobjects so inner join syscomments sc on so.id = sc.id

where

sc.text like ''%insert%into%TABLENAME%''

or sc.text like ''%UPDATE%TABLENAME%''

and so.xtype = ''P''

)

select * from cte where dbid not in (''1'',''2'',''3'',''4'',''5'',''6'')

Distribution Agent - Start /Stop in T-SQL

-- To STOP the Distribution Agent:
sp_MSstopdistribution_agent @publisher, @publisher_db, @publication,

 @subscriber, @subscriber_db
 
--To START the Distribution Agent:
sp_MSstartdistribution_agent @publisher, @publisher_db, @publication,

 @subscriber, @subscriber_db

Cannot insert duplicate key row - Replication

• 2601 Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'
• 20598 The row was not found at the Subscriber when applying the replicated command.
• 2627 Violation of PRIMARY KEY constraint 'PK__A'. Cannot insert duplicate key in object 'dbo.A'.

http://www.mssqltips.com/sqlservertip/2469/handling-data-consistency-errors-in-sql-server-transactional-replication/