SQL Server Performance

A field schema change replication causes an error 'The process could not execute sp_replcmds'

Discussion in 'SQL Server 2008 Replication' started by WingSzeto, Apr 11, 2011.

  1. WingSzeto Member

    We are using SQL 2008 std x64, push transactional replication. Both publisher db and distribution db are on the same box. Today we encountered a very strange replication issue. I was making a field type change from tinyint to smallint on a table say table A. I removed the article from replication and made the change, and then put it back to replication. I tested this change in our test envirnoment and everything worked fine. I made the same changes in Production and it seems to be working as well. However, a few seconds after the changes, I encountered the following error messages from the replication monitor in our production envir.
    - The process could not execute 'sp_replcmds' on 'Server A' (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
    - Could not locate text information records for the column "Personal_Note", ID 6 during command construction. (Source: MSSQLServer, Error number 18733)
    - The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {0001c28e:0005a1f4:0003}. Back up the publication database and contact Customer Support Service. (Source: MSSQLServer, Error number 18805)
    I looked at the second line which indicated that repliation had issue with the column "Personal_Note". So I found out the table (say table B) contains that column and removed the table from replication. Then replication started working fine again. I added the table B back to replication after that.
    The table A which I initially made the changes has no FK relationship with table B and they are created for a differnet business function so they are not used together by any mean. The column "Personal_Note" which is a text field in table B doesn't exist in table A.
    Can anyone explain what happened to this replication problem? It just doesn't make any sense how the problem started. Any help on this is very much appreciated.
    w
  2. satya Moderator

    How big is the table?
    Were there any non-logged operations happened at the same time?
  3. WingSzeto Member

    Satya,
    Thank for the reply. I didn't get the alert from the forum that informs me your reply. Sorry about getting back to you so late. I did enable 'email replies to this post' but not sure what happened.
    Anyway, the table A (the one I made the schema change) is a small table (< 10,000 records). The un-related table (table B) that caused the replication issue has above 2.2 million records. The database that involves in replication is in Full recovery mode so unless I mis-understand your question, my answer is that there weren't any non-logged operations at the same time.
    Last week this situation happens again. I was making a schema change on another table (table C). The same text field in the same un-related table (table B) was casuing the problem. It is quite strange that both incidents happened on Monday morning and both time the problem occurs when I took the table C out of replication and add it back after the schema change.
    w

Share This Page