June 2, 2010 at 8:22 am
Strange thing happened last night that I haven't seen before.
I created some nonclustered indexes on tables that are currently being replicated using transactional with updatable subscribers. Everything seemed to go fine when running the create scripts (no errors were thrown.) Then this morning I noticed that at some point the Log Reader Agent stopped working and throwing an error:
The process could not execute 'sp_replcmds' on '{server}'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
and
The process could not execute 'sp_replcmds' on '{server}'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Is there anything I should know about creating indexes on tables that are involved in this type of replication? Do I need to do something before running the scripts? If not, then what could have caused this to happen as the only thing that changed was that we ran the index creation scripts.
One theory I had was that the index creation added so much to the log that it choked replication and it couldn't recover. (Layman's terms because I am not familiar enough with replication to know how to describe it.) I did see in the SQL Server logs that there was a Short Stack Dump around the time that everything started to go haywire. ???
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
June 2, 2010 at 8:35 am
It's been forever since I've used replication. I have this bizarre, vague memory that there's a setting in one of the properties windows that needs to be set in order to allow your indexes to be replicated. But I can't find it in Server or Database properties and I don't know the name of it.
Check your subscription and publication properties. See if there's something preventing the indexes from replicating properly.
June 2, 2010 at 8:37 am
Brandie Tarvin (6/2/2010)
It's been forever since I've used replication. I have this bizarre, vague memory that there's a setting in one of the properties windows that needs to be set in order to allow your indexes to be replicated. But I can't find it in Server or Database properties and I don't know the name of it.Check your subscription and publication properties. See if there's something preventing the indexes from replicating properly.
Well, we have the 'Repliate Schema Changes' property set to True so I would that would cover it.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
June 2, 2010 at 8:40 am
Check the dump log, and the server's event viewer. What are they saying?
The usual "fix" in SQL 2000 for me (when it came to problems like this) was re-initializing the subscriptions. But I don't know how that affects SQL 2k5.
June 2, 2010 at 9:34 am
Brandie Tarvin (6/2/2010)
Check the dump log, and the server's event viewer. What are they saying?The usual "fix" in SQL 2000 for me (when it came to problems like this) was re-initializing the subscriptions. But I don't know how that affects SQL 2k5.
The dump file has the following:
SqlDumpExceptionHandler: Process 116 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 06/02/10 10:59:56 spid 116
*
*
* Exception Address = 0000000002824AA6 Module(sqlservr+0000000001824AA6)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 0000000000000000
* Input Buffer 109 bytes -
* 16 00 00 00 12 00 00 00 02 00 00 00 00 00 00 00 00 00
* s p _ r e p 01 00 00 00 0b 00 73 00 70 00 5f 00 72 00 65 00 70 00
* l c m d s & ô 6c 00 63 00 6d 00 64 00 73 00 00 00 00 00 26 04 04 f4
* & & ÿ 01 00 00 00 00 26 04 04 00 00 00 00 00 00 26 04 04 ff
* ÿÿÿ ¥ & ff ff ff 00 00 a5 0a 00 00 00 00 00 26 04 04 10 00 00
* & & ¡ 00 00 00 26 04 04 00 00 00 00 00 00 26 04 04 20 a1 07
* 00
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
June 2, 2010 at 10:26 am
Check out this link on how to read the dump log. It might help you track down your problem child.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply