We help IT Professionals succeed at work.

Replication Issue

Hi, I am facing an issue while configuring replication on one of the databases on server getiing replicated.
All the other replication are working normally.

Recently data type of one of the column of the table involved in replication was changed from varchar(32) to nvarchar(32)
and a unique non clustered index was added to the same table.

But since yesterday the log reader fails to start and gives the below error.

The process could not execute 'sp_replcmds' on 'servername'.

I tried recreating publication and the subscriber but to no success.

I also decreased the Query Timeout and the Batch Read value still am getting the same error.

I ran dbcc checkdb on the database but there were no errors.

We have SQL Server 2008 R2 Standard edition with latest service pack.

Error messages:
The process could not execute 'sp_replcmds' on 'servername'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
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.  (Source: MSSQLServer, Error number: 3624)
Get help: http://help/3624
The process could not execute 'sp_replcmds' on 'servername'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037.

It creates a SQL dump file :

External dump process return code 0x20002001.
The error information has been submitted to Watson error reporting.2012-08-16 02:33:15.14 spid87      Error: 17066, Severity: 16, State: 1.
2012-08-16 02:33:15.14 spid87      SQL Server Assertion: File: <replrowset.cpp>, line=2184 Failed Assertion = '(LSN)m_curLSN < (LSN)(pSchemas->schema_lsn_begin)'. This

error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the

server to ensure in-memory data structures are not corrupted.
2012-08-16 02:33:15.14 spid87      Error: 3624, Severity: 20, State: 1.
2012-08-16 02:33:15.14 spid87      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 advise
ERRORLOG.txt
Comment
Watch Question

Commented:
Have you simply done checkdb or with the following statement:

DBCC CHECKDB(yourdbhere) WITH ALL_ERRORMSGS, NO_INFOMSGS;


If you have already done, then try restarting the server if possible. Else look for an update related to your version, i.e. any service packs. Else last resort: MAKE A CALL TO MSFT
SP_2018 .IT Consultant

Author

Commented:
i did dbcc checkdb databasename
Yes am planning to restart the server tonight ,
We have installed  latest service pack on the server : Cumulative Service Pack 5
AnujSQL Server DBA
Top Expert 2011

Commented:
Can you post us the SQL Dump file?

Commented:
Let me know after the server restarts.
SP_2018 .IT Consultant

Author

Commented:
My issue got resolved without restarting the server.
I dropped the table whose datatype got changed from the publication article and
ran the below command on the publisher database
exec sp_replrestart, ran the snapshot again , dropped and recreated the subscriber
and the issue was resolved
However i have one more issue
Though log reader agent is reading data from the publisher to distributor
the transactions seem to be not moving from the distributor to subscriber,
the status displays Delivering transaction,

and the number of command to be applied to subscriber from distributor is high


Please advise
SP_2018 .IT Consultant

Author

Commented:
This problem is resolved
IT Consultant
Commented:
Hi,

Log reader agent showed some undistributed transactions which were not marked for
replication.
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..
SP_2018 .IT Consultant

Author

Commented:
Resolved the problem