Peer to Peer Replication missing index on MSpeer_lsns

Question

I am running SQL Server 2008 Peer to Peer replication across 2 sites for 1 database.

After applying SQL 2008 SP1 Cumulative Update 4 and starting the logreader and distribution jobs the following message appears in the SQL Server errorlog:

                  Index 'nci_MSpeer_lsns' on table 'MSpeer_lsns' (specified in the FROM clause) does not exist.


Having had a look at stored procedure sp_MSgetpeerlsns it is using this index but the index does not exist on the system table. 

Is anybody able to shed some light on this issue ?  Is this a problem with CU4 ?

Answer 1

I was able to work around this error by creating an index on the MSpeer_lsns system table call nci_MSPeer_lsns.  Updating system tables by adding indexes is a not a permanent solution but it at least would help me to identify and isolate the cause of the issue.

I then stopped and restarted the log reader and distribution jobs and this error no longer appears.


Unfortunately, this has now caused another error message to appear:
  Invalid column name '$sys_p2p_cd_id'. (Source: MSSQLServer, Error number: 207)

The only reference I was able to find for this error was in another thread but there does not seem to be a resolution to this issue.
http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/165bb7e4-09d6-4968-ae84-975485d9071c?prof=required

Has anyone else experienced this issue and if so, was there any solution ?

Additional information is that the environment is running 64-bit Windows Server 2003 R2 Standard Edition with SQL Server 2008 64-bit Enterprise Edition.

Answer 2

I am afraid but it looks like your metadata is corrupt somewhere along the line. I think  you would be best off to drop this publication and start over.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 3

Thanks Hilary.

I assume that I will need to remove the replication system tables manually if they are still there after dropping the publication.

I will try dropping and recreating the publication and let you know how it goes.

Answer 4

Apologies for the long delay in getting back to you but dropping and recreating the publication solved the issue.

Answer 5

this index was added in CU4, can you verify in your setup logs that replication upgrade scripts completed successfully? 

Running proc sp_vupgrade_replication should be able to create this new index for you.