• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

Replication error - Urgent

Hi ,

Recently a data type change and unique nonclustered index was inserted in one of the tables  in the database which was publisher in replication
and that point forward i get this message in the log reader agent of all the subscribers
involved in the replication

The process could not execute 'sp_replcmds' on 'servername'.
We have SQL Server 2008 R2 Standard edition(64 bit)
version : 10.0.4316

in sql error log ..i see a lot of messages wit the below error :

A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.

Please help!!
This is very urgent
Sonali P
Sonali P
  • 7
  • 5
1 Solution
Did you take the log's advice and run DBCC CHECKDB?
If so what was the result?
Sonali PDatabase AdministratorAuthor Commented:
Dbcc had no errors.
There are replications on other databases on the same server running fine , just this one
Where data type and a unique constraint was added last week started giving issues since yesterday the developer changed the data type from  to nvarchar on a column and added a unique constraint on the table. Can this cause the issue ? It says the process cannot run sp_replcmds
Pls advise this is mission critical db
What was the original datatype?
What was the constraint?
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Sonali PDatabase AdministratorAuthor Commented:
Original data type was varchar32 changed to
Unique constraint was applied to three columns in the table one of which had the data type changed
Yes, but what EXACTLY was the constraint?
And while your typeing, what are the datatypes of the other two columns?
PS: One problem might be that varchar is ascii and nvarchar is unicode.
Was the database modification applied to the databases you are trying to replicate to?
Sonali PDatabase AdministratorAuthor Commented:
No the change was not made on the replicated database, it did not apply those changes on the replication environment
by wld that change corrupt the replication metadata . I tried reintializing the subscription by the log reader agent but it gives error saying it cannot start the process sp_repl Cmds . I even dropped and recreated the replication on publisher and subscriber but it does not . SQL error log gives error saying assertion check failed, I have no idea what it means
Sonali PDatabase AdministratorAuthor Commented:
These are the columns in the table

column 1 datat type changed from varchar(32) to nvarchar(32)
column 2 int
column 3 int

apart from data type change , these indexes were added

alter table tablename add unique nonclustered ([column2] asc,[column 3] asc, [column 1])
1. Narrowing down the problem:
Does the table still have its primary key?

If you drop the constraint, does the replication error still happen? If No, then the problem is within the constraint, if YES, then the problem is with the datatype change, or both.
If you leave the constraint in place, but change the datatype back to a varchar, does the error still occur.

2. You state the data change was not applied to the databases you are trying to replicate to.
If you apply the changes to a copy of one of those databases and then try to replicate it, does the problem disappear?
Another thought:
Even though an index can be added at the publisher, it won't get automatically propagated to the subscribers.
To do that you use sp_addscriptexec, otherwise you have to manually create the index on each subcriber.
Does this describe your replication scheme.
Sonali PDatabase AdministratorAuthor Commented:

Log reader agent showed some undistributed transactions which were not marked for
I guess that was the reason the latency was increasing.
I simply dropped the subscription and the target database,
Rebuild all the indexes at the publisher side.
Created a new publication and subscrber and and it seems to have resolved the issue.

Thanks all for your help..
Yes. Like I said; it sounds like the imdex was not replicated to the subscribers.
By dropping the database and rebuilding all the indexs at the Publisher side, they were picked up correctly in the replication scheme.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now