Distrubution agent fails with “Could not find stored procedure XXXXX_msrepl_ccs”
Sateesh Yele
Microsoft SQL Server Escalation Services.
Issue:
The distribution agent is failing when applying the snapshot so it will retry applying the snapshot again and again. Below is the Distribution agent verbose log that explains the error message and you can see that the distribution agent is retrying the snapshot by scanning the first LSN (0x0002898400004f4f003a) of the snapshot.
2013-07-09 00:54:48.359 sp_MSget_repl_commands timestamp value is: 0x0x0000000000000000000000000000
2013-07-09 00:54:48.375 Last transaction timestamp: 0x0000000000000000000000000000
Transaction seqno: 0x0002898400004f4f003a ? starting LSN to applying the snapshot
Command Id: 1
Partial: 0
Type: 37
Command: SYNCSTAT
.
.
. applied some commands and then it failed
.
.
2013-07-09 01:53:52.040
42000 Could not find stored procedure ‘sp_MSdel_dboEmployee_msrepl_ccs’. 2812
2013-07-09 01:53:52.040
42000 Could not find stored procedure ‘sp_MSins_dboEmployee_msrepl_ccs’. 2812
2013-07-09 01:53:52.040
2013-07-09 01:53:52.071 Last transaction timestamp: 0x00028985000001cc008f00000000
Transaction seqno: 0x00028985000001cc008f
Command Id: 163
Partial: 255
Type: 30
Command: <<parameterized command>>
2013-07-09 01:53:52.462 Disconnecting from Subscriber ‘Sub_XXXXX’
2013-07-09 01:53:52.462 Repl Agent Status: 3
2013-07-09 01:53:52.477 Disconnecting from OLE DB Subscriber ‘Sub_XXXXX’
2013-07-09 01:53:52.477 Connecting to Subscriber ‘Sub_XXXXX’
2013-07-09 01:53:52.477 Repl Agent Status: 3
2013-07-09 01:53:52.477 Connecting to OLE DB Subscriber at datasource: ‘Sub_XXXXX’, location: ”, catalog: ‘Sub_XXXXX’, providerstring: ” using provider ‘SQLNCLI’
2013-07-09 01:53:56.712 OLE DB Subscriber: Sub_XXXXX
2013-07-09 01:53:56.712 Error executing a batch of commands. Retrying individual commands..
2013-07-09 01:53:56.712 Repl Agent Status: 3
2013-07-09 01:53:56.930 OLE DB Distributor ‘Sub_XXXXX’: {call sp_MSget_subscription_guid(344)}
2013-07-09 01:53:58.087 sp_MSget_repl_commands timestamp value is: 0x0x0000000000000000000000000000 ? the distribution agent is retrying the cmds.
2013-07-09 01:53:58.102 Last transaction timestamp: 0x0000000000000000000000000000
Transaction seqno: 0x0002898400004f4f003a ? The distribution agent went back to the starting LSN of the snapshot and applying it again.
Command Id: 1
Partial: 0
Type: 37
Command: SYNCSTAT
Cause:
The issue will only occur when the following conditions are true
• The publication is configured for concurrent snapshot (in sp_addpublicaiton @sync_method = ‘concurrent’)
• There are changes occurring to the published tables when the snapshot process is running.
• The replicated tables are configured for “not to generate the stored procedures” option in sp_addarticle (not using @schema_option = 0x02). Screenshot of schema_option from BOL
Snapshot agent creates xxxxx_msrepl_ccs procedures to replicate any DML changes occurred to the replicated tables during the snapshot process. Once these concurrent changes are delivered to the subscriber by the distribution agent, these msrepl_ccs procedures are deleted at the subscriber. Later on, if the schema option is modified (0x02 is not used) and these procedures are not generated. When the distribution agent tries to replicate the any concurrent changes to the replicated tables, the agent will fail with the above error message as these msrepl_ccs procedures are deleted after the last snapshot was applied and are not generated in the new snapshot.
Workaround/Solution:
There are 2 options to solve the issue
1) Avoid making changes to the published tables when the snapshot process is running
2) Configure the tables to create stored procedures in sp_addarticle procedure (use @schema_option = 0x02)
You can find more about the schema_option in the link below
http://technet.microsoft.com/en-us/library/ms173857.aspx
