Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 799
  • Last Modified:

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
0
Sonali P
Asked:
Sonali P
  • 5
  • 2
1 Solution
 
TempDBACommented:
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
0
 
Sonali PDatabase AdministratorAuthor 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
0
 
AnujSQL Server DBACommented:
Can you post us the SQL Dump file?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
TempDBACommented:
Let me know after the server restarts.
0
 
Sonali PDatabase AdministratorAuthor 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
0
 
Sonali PDatabase AdministratorAuthor Commented:
This problem is resolved
0
 
Sonali PDatabase AdministratorAuthor 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..
0
 
Sonali PDatabase AdministratorAuthor Commented:
Resolved the problem
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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