SQL Server Replication Troubleshooting – Why My Transaction Has Not Been Replicated?

replicationsql serversql server 2014transactional-replicationtroubleshooting

I have been troubleshooting a replication problem, according to the picture below:

the replication Problem

with reference to the picture above,

the bit that says
Sessions of the distribution agent
I can get using the following query:

--================================================================================
-- finally the contents 
-- sessions of the distribution agent:
--================================================================================
use [master]
exec [distribution]..sp_MSenum_distribution_s 
@name = N'MYSERVER-Product-Product-MYSUBSCRIPTION-184', 
@hours = -1, 
@session_type = 1

Actions in the selected session

--========================================================
-- get the actions in the selected session:
-- for each row on the above data, we can get the details:
--========================================================
-- example of the last session - without errors
use [master]
exec [distribution]..sp_MSenum_distribution_sd 
@name = N'MYSERVER-Product-Product-MYSUBSCRIPTION-184', 
@time = N'20170411 14:16:03.920'

enter image description here

-- example of the second session - with errors
use [master]
exec [distribution]..sp_MSenum_distribution_sd 
@name = N'MYSERVER-Product-Product-MYSUBSCRIPTION-184', 
@time = N'20170410 15:50:27.650'

enter image description here

Now this bit of code below gives me the errors I am looking for:

Error details or message of the solected session:

And here goes the Question too:

How can I get the number 5468694 from the result above and use it in the query below?

--========================================================================
-- Error details or message of the solected session:
--=========================================================================
    use [master]
    exec [distribution]..sp_MSenum_distribution_sd 
    @name = N'SQLAPPLON1-Product-Product-REPLON1-184', 
    @time = N'20170410 15:50:27.650'

    use [master]
    exec [distribution]..sp_MSget_repl_error 
    @id = 5468694

enter image description here

Best Answer

This isn't a verified query as I'm not running replication, but after scripting out definitions for both sp_MSenum_distribution_sd and sp_MSget_repl_error via sp_helptext and smashing together the underlying logic, I came up with the following that may give you what you're looking for.

DECLARE @name NVARCHAR(100), @time DATETIME

SET @name = N'SQLAPPLON1-Product-Product-REPLON1-184', 
    @time = N'20170410 15:50:27.650'

SELECT  msre.source_type_id, 
        msre.source_name, 
        msre.error_code,    
        msre.error_text, 
        sys.fn_replformatdatetime(msre.time) AS 'time',
        error_type_id, 
        CASE WHEN msre.xact_seqno IS NULL OR msre.xact_seqno = 0x0
            THEN CONVERT(BIT, 0) ELSE CONVERT(BIT, 1) END AS has_xact_seqno,
        msre.xact_seqno, 
        msre.command_id,
        rh.error_id
FROM    [distribution].dbo.MSrepl_errors msre 
        INNER JOIN [distribution].dbo.MSlogreader_history rh WITH (READPAST)
            ON msre.id = rh.error_id
WHERE   rh.agent_id = (SELECT TOP 1 ID 
                       FROM MSlogreader_agents 
                       WHERE name = @name)
        AND rh.start_time = @time 
        AND rh.comments not like N'<stats state%'
        AND rh.error_id <> 0
        -- rows with error_type_id are placeholders
        AND msre.error_type_id IS NOT NULL
ORDER BY 5 ASC
Related Question