Sql-server – -skipErrors in Transactional Replication not happening

If fn_dblog is hanging you may try some third party tools from ApexSQL, RedGate or Idera. Or you can try to backup the transaction log file and use fn_dump_dblog just to exclude problems with an online transaction log. You can find more info on this DBA post

I found a simple solution here.

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

My complete implementation, working on my test environment is below. I have left everything there, select queries and procedures, to remind myself, where to go to get the data to be used as parameters to the sp_MSstartdistribution_agent and sp_MSstopdistribution_agent stored procedures.

--==================================================================================
-- script to start-stop the distribution agent job for a publication
-- marcelo miorelli
-- 12-mar-2015
--==================================================================================




--http://www.msqlserver.net/2013/01/distribution-agent-start-stop-in-t-sql.html

--==================================================================================
--Script to run on Publisher database
--==================================================================================

PRINT @@SERVERNAME
--SQLBOCSSLON1 -- test 


use Bocss2
GO

-- get the distributor
sp_helpDistributor
--SQLDISTLON1 -- distributor server

--==================================================================================
-- Script to run on Distribution database
--==================================================================================

PRINT @@SERVERNAME
--SQLDISTLON1 -- test 

-- distributor database in this instance is called "distribution"
USE Distribution 
GO 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

select * from dbo.MSrepl_errors 
ORDER BY [TIME] DESC



-- Get the publication name based on article 
SELECT DISTINCT  
srv.srvname publication_server  
, a.publisher_db 
, p.publication publication_name 
, a.article 
, a.destination_object 
, ss.srvname subscription_server 
, s.subscriber_db 
, da.name AS distribution_agent_job_name 
FROM MSArticles a  
JOIN MSpublications p ON a.publication_id = p.publication_id 
JOIN MSsubscriptions s ON p.publication_id = s.publication_id 
JOIN master..sysservers ss ON s.subscriber_id = ss.srvid 
JOIN master..sysservers srv ON srv.srvid = p.publisher_id 
JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id  
     AND da.subscriber_id = s.subscriber_id 
--WHERE da.name = 'SQLBOCSSLON1-Bocss2Archive-Bocss2ArchiveMergedAc-REPLON1-54'
ORDER BY 1,2,3  

--==================================================================================
--Script to run on Publisher database
--==================================================================================

PRINT @@SERVERNAME
--SQLBOCSSLON1 -- test 


-- Run from Publisher Database  
-- Get information for all databases 
DECLARE @Detail CHAR(1) 
SET @Detail = 'Y' 

BEGIN TRY
DROP TABLE #tmp_replcationInfo
END TRY
BEGIN CATCH
END CATCH


CREATE TABLE #tmp_replcationInfo ( 
PublisherDB VARCHAR(128),  
PublisherName VARCHAR(128), 
TableName VARCHAR(128), 
SubscriberServerName VARCHAR(128), 
) 
EXEC sp_msforeachdb  
'use ?; 
IF DATABASEPROPERTYEX ( db_name() , ''IsPublished'' ) = 1 
insert into #tmp_replcationInfo 
select  
db_name() PublisherDB 
, sp.name as PublisherName 
, sa.name as TableName 
, UPPER(srv.srvname) as SubscriberServerName 
from dbo.syspublications sp  
join dbo.sysarticles sa on sp.pubid = sa.pubid 
join dbo.syssubscriptions s on sa.artid = s.artid 
join master.dbo.sysservers srv on s.srvid = srv.srvid 
' 
IF @Detail = 'Y' 
   SELECT * FROM #tmp_replcationInfo 
ELSE 
SELECT DISTINCT  
PublisherDB 
,PublisherName 
,SubscriberServerName  
FROM #tmp_replcationInfo 

-------------------------------------------------------------------------     
select  
db_name() PublisherDB 
, sp.name as PublisherName 
, sa.name as TableName 
, UPPER(srv.srvname) as SubscriberServerName 
,SP.*
,SA.*
,S.*
,SRV.*

from dbo.syspublications sp  
join dbo.sysarticles sa on sp.pubid = sa.pubid 
join dbo.syssubscriptions s on sa.artid = s.artid 
join master.dbo.sysservers srv on s.srvid = srv.srvid 
-------------------------------------------------------------------------



--==================================================================================


--Msg 21482, Level 16, State 1, Procedure sp_MSstopdistribution_agent, Line 22
--sp_MSstopdistribution_agent can only be executed in the "distribution" database.

--==================================================================================

PRINT @@SERVERNAME
--SQLDISTLON1 -- test 

 --Distribution Agent - Start /Stop in T-SQL
    -- To STOP the Distribution Agent:
    sp_MSstopdistribution_agent @publisher, @publisher_db, @publication,
     @subscriber, @subscriber_db



--Msg 21846, Level 16, State 1, Procedure sp_MSdistributionagentjobcontrol, Line 60
--Cannot find a distribution agent job for the specified transactional or snapshot push subscription.

use distribution
go

sp_helpdistpublisher

select msda.*
       ,sjv.*
       ,sjs.*
      from dbo.MSdistribution_agents as msda
inner join msdb.dbo.sysjobs_view as sjv
        on msda.job_id = sjv.job_id
inner join msdb.dbo.sysjobsteps as sjs
        on sjv.job_id = sjs.job_id


    -- To STOP the Distribution Agent:
    sp_MSstopdistribution_agent 'SQLBOCSSLON1', 'Bocss2Archive', 'Bocss2ArchiveMergedAccounts',
     'REPLON1', 'Bocss2Archive'

    -- To check whether the Distribution Agent is running:
    sp_runningJobs 'SQLBOCSSLON1-Bocss2Archive-Bocss2ArchiveMergedAc-REPLON1-54'          

    --To START the Distribution Agent:
    sp_MSstartdistribution_agent @publisher = 'SQLBOCSSLON1',
                                 @publisher_db = 'Bocss2Archive', 
                                 @publication = 'Bocss2ArchiveMergedAccounts',
                                 @subscriber = 'REPLON1', 
                                 @subscriber_db = 'Bocss2Archive'


    -- To check whether the Distribution Agent is running:
    sp_runningJobs 'SQLBOCSSLON1-Bocss2Archive-Bocss2ArchiveMergedAc-REPLON1-54'