Review of some replication issues and workaround - SQL Server by serverku

After posting some snaps to configure a transactional replication in SQL server having publication server as a distributor, I want to share some of the issues which I got during working with it. You might also face the same issue and got resolved as well. You can comment if you faced any other replication issues and the solution for the same.

1."There is insufficient system memory to run this query" - Distributor agent error for subscriber database : During working with replication this is one of the errors which I encountered and get resolved it. This is coming with a Distributor agent, and it stopped working. It's due to the memory issue. I have flashed the unused memory consumed by Adhoc queries and system cache.

The error,

" There is insufficient system memory to run this query. (Source: MSSQL_REPL, Error number: MSSQL_REPL27453) "

To flush the memory uses the below commands for a solution of above error,

DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL')

2. "Could not find stored procedure 'sp_MSins_TableName' in replication in sql server" - Distributor reader agent error: It came after reinitialize all subscriptions for one publication and it stopped working with error,

"Could not find stored procedure 'sp_MSins_TableName' in replication in sql server"

This is due to replication object was not created to insert and named like 'sp_MSins_TableName'. The solution for this issue, we need to create these missing replication objects using 'sp_scriptpublicationcustomprocs' which generates scripts the custom INSERT, UPDATE, and DELETE procedures for all table articles in a publication. Workaround for the solution is,

EXEC sp_scriptpublicationcustomprocs 'PublicationName'

After running it, and generated scripts from it. Ran it in the subscriber database, Distributor reader agent started to work.

3."The row was not found at the Subscriber when applying the replicated command" : It encountered it with Distributor agent and it was stopped due to error as,

"Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000CAB2A00038CB6003B00000000, Command ID: 2)
Error messages:
The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)"

Row conflict issue was occurring due to some of the tables which we can get the details with following query to reach towards a solution.

SELECT
*
FROM dbo.MSarticles m
WHERE EXISTS
(
SELECT mc.Article_id from MSrepl_commands mc
WHERE mc.xact_seqno = 0x000CAB2A00038CB6003B00000000
and mc.Article_id = m.article_id
)

Hope you like this and I will share more replication errors if will get in the future.