{"id":1323,"date":"2012-05-31T10:25:52","date_gmt":"2012-05-31T04:25:52","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/?p=1323"},"modified":"2016-01-07T11:31:04","modified_gmt":"2016-01-07T05:31:04","slug":"what-if-you-encounter-error-number-20598-with-sql-server-replication","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=1323","title":{"rendered":"What if you encounter Error number: 20598 with SQL Server replication ?"},"content":{"rendered":"

Today on one of the sql server I encountered an interesting\u00c2\u00a0scenario, one of the dbas accidentally deleted data from subscriber and then when he realized he turned around and delete the data from publisher. Crap ! is the diplomatic term to use here. \u00c2\u00a0That too it was a huge table and part of a 50 table article. The moment he did that transactional replication stopped throwing the\u00c2\u00a0Error number: 20598.<\/a> Which means the distributor is not able to deliver the delete statement to the subscriber since it cannot find the row. \u00c2\u00a0Once this happens all the subsequent transactions just pile up. You got few ways to handle it:<\/p>\n

1. Reinitialize the subscription (after giving the dba a kick in the you know what)
\n2. If replication must continue while you identify the source of the error, specify the parameter -SkipErrors 20598 for the Distribution Agent and either fix the subscriber table.
\n3. Manually insert the deleted rows to subscriber table, so the deletes can flow through
\n4. If all of the above can’t be done, then delete the offending commands from the\u00c2\u00a0MSrepl_commands table. (again give the dba a kick in the you know what !)<\/p>\n

The last option should be done with care since you are messing with replication commands that the distributor has pending. The table distribution.dbo.MSrepl_commands<\/a> table has all the commands the distributor has send and going to send to subscriber.\u00c2\u00a0If you delete more than what you need now you would have data inconsistency between publisher and subscriber. \u00c2\u00a0In this post I am going to show you how you can identify the offending command and delete them from the MSrepl_commands table.<\/p>\n

First lets find the errors that the distributor has encountered . You can find this either from the Replication monitor or by querying\u00c2\u00a0dbo.MSrepl_errors. \u00c2\u00a0From the error_text column you should be able to identify the\u00c2\u00a0xact_seqno and command_id . Get the list of the xact_seqno and command_id that is causing the issue.<\/p>\n

Second lets see what those commands are . For this execute the\u00c2\u00a0sp_browsereplcmds<\/a>\u00c2\u00a0passing the xact_seqno. This will give you the list of all the commands that the distributor is trying to execute. Confirm the once you need to delete.
\neg:\u00c2\u00a0sp_browsereplcmds @xact_seqno_start = ‘0x0000002A000000D3000700000000’<\/p>\n

Third \u00c2\u00a0delete the rows from the\u00c2\u00a0MSrepl_commands table so that replication can continue with other pending ones. Be very cautious on what rows you delete. You do not want to delete rows that are not causing the issue.
\neg: \u00c2\u00a0delete from dbo.MSrepl_commands
\nwhere xact_seqno = 0x0000002A000000D3000700000000\u00c2\u00a0and command_id in (1,2)<\/p>\n

Once you delete the offending rows from the\u00c2\u00a0MSrepl_commands, replication should start sending the commands to subscriber. You the replication monitor to see whether the undistributed commands queue is decreasing.<\/p>\n

Use this approach at your own risk.<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

Today on one of the sql server I encountered an interesting\u00c2\u00a0scenario, one of the dbas accidentally deleted data from subscriber and then when he realized he turned around and delete the data from publisher. Crap ! is the diplomatic term to use here. \u00c2\u00a0That too it was a huge table and part of a 50 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[303,658],"tags":[814,811,817,816,812,815,813,810],"class_list":["post-1323","post","type-post","status-publish","format-standard","hentry","category-database","category-sql-server-2","tag-distributor-not-working","tag-error-number-20598","tag-find-replication-errors","tag-msrepl_commands","tag-pending-replication-command","tag-sp_browsereplcmds","tag-sql-server-replication-commands","tag-sql-server-replication-error"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1323","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1323"}],"version-history":[{"count":2,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1323\/revisions"}],"predecessor-version":[{"id":1491,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1323\/revisions\/1491"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1323"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1323"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1323"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}