SQL Server Performance

Transactional Replication Problem

Discussion in 'Performance Tuning for SQL Server Replication' started by iambhushan, Oct 31, 2005.

  1. iambhushan New Member

    Hi Everyone,
    I am trying to set-up transactional repliation to database DB1 from sever1 to server2 . The database "DB1" size is approximately 50GB so I have taken a backup of a database from server1 and restored the backup copy at server2 to avoid initilization of snapshot file from server1 to server2 which causes huge network traffic. Then later I configured server1 as publisher and distributor, and server2 as subscriber. So far everything is fine and set-up went smoothly but when I update a record in table1 (server1) then replication monitor was throwing error message that SP_MSins_table1 custom stored procedure is not existed similarly for update and delete records in table1(server1) the error messages are SP_MSupd_table1 and SP_MSdel_table1 custom stored procedures are not existed respectively. I did some research on this and found that I need to be run "sp_scriptpublicationcustomprocs" with @pulication on publication database. I executed the above stored procedure but still errors are not resolved. If anyone has gone through or have experience please help me. <br>I really appreciate your help and time. if you want to contact me please call me at 317 410 8649.
    Thanks,
    Bhushan Kalla

    Bhushan Kalla
  2. chen_wan New Member

    Can you see the SP_MSins_table1 stored procedures in server2. I noticed that the stored procedures will be created at the subscriber automatically when the database is first snapshot to the subscriber. Since, you are not using snapshot to replicate the initial copy of you database, I guess that is why the stored procedures is not created. I also wish to know how to instruct sql server to create the stored procedure automatically at the subscriber. Currently, what I am doing is to create the stored procedure manually.
  3. jeroen New Member

    I had exactly the same problem.

    First you run the sp_scriptpublicationcustomprocs procedure, then run the generated statements on the subscriber (not on the publisher). It should work now (it did for me).

Share This Page