My log reader has failed with the error:

Question

I have added 6 new articles and change the immediate_synch and allow anonymous property from 1 to 0 and then again 1 :

My log reader has failed with the error:

Error messages:
The process could not execute 'sp_replcmds' on 'REMEDYSQL'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Could not locate text information records for the column "C456", ID 13 during command construction. (Source: MSSQLServer, Error number: 18773)
Get help: http://help/18773
The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {00002e78:0000b35f:0023}. Back up the publication database and contact Customer Support Services. (Source: MSSQLServer, Error number: 18805)
Get help: http://help/18805
The process could not execute 'sp_replcmds' on 'REMEDYSQL'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037

 

---

Thanks

Answer 1

Is this new column "C456" a BLOB?

If yes check the document and see the topic 

5.1.8 Log Reader Agent Fails After Adding an Article to a Publication with
binary large object (BLOB) Data

HTH

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 2

No it is ntext...earlier replication was running with same table...

---

Thanks

Answer 3

Ntext also belongs to the same category (specifically Large objects which doesn't fit in a single page) 

Can you check what is mentioned in that document. What it says is, if you have a concurrent snapshot and if you have a large object column during the time a new column is added and if at the same time any update happens for this large object column you can get this error.

HTH

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 4

Whenever you get a message like this: "Could not locate text information records for the column "C456", ID 13 during command construction. (Source: MSSQLServer, Error number" is it a bug.

You need to open a support incident with Microsoft on this one. This is a bug.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 5

I have changed immediate_synch and allow anonymous property from 1 to 0 and then again 1.

Is this problem not arises due to above written statement.I have done this and after that log reader agent has started to fail.

---

Thanks

Answer 6

Ashwin and Hilary Thanks...I will update you whatever is going on...

---

Thanks

Answer 7

This command construction problem is unrelated to the immediate sync option.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 8

I have not yet added those articles...Should I add new publication for these articles...

---

---

Thanks

Answer 9

It will not matter is if it in the same publication or a different/new one. All publications in the same database will share the same log reader agent. When the command that causes the problem enters the database the single log reader agent will go down and you will be out of luck for every publication in that database.

You might want to run continual traces capturing the xactsequence column as this maps to xact_sequenceid in the distribution database and the error message will refer to this xact_sequenceid - this way you can see the offending command and perhaps find a workaround.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 10

Hi Hilary,

According to the shared documnet now I am going to add a new publication for these 6 views.

ShouLd i use old suscriber or new subscriber.

If use old one the old data should not be deleted...Please help

---

Thanks

Answer 11

For transactional replication when you reinitialize all subscriber data will be wiped out. Most people treat their subscribers as read only and do not make user changes there. Is this the case with you?

If so you will need to create a new subscription and subscriber database.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 12

Hi hilary Thanks for your efforts..

Can u tell default way for second publication to publish his data to subscriber.So that data loss not occured.

I cannot create new database on subscriber side.

---

Thanks

Answer 13

we are using a basic default configuration of Transactional replication.

I want to know:After creating second publication with 6 articles and add the same subscriber for this and after creation then reinitialize all subscription.

Q1: Will it disturb the first publication?

Q2: Will Subscriber wiped all data first from all other tables?

---

Thanks

Answer 14

Your log reader agent is the problem. No  matter how may publications you have in the publication database there will be one publication. If the problem table is part of a single publication containing all tables, or just the problem table, the single log reader agent will at some point fail. Ashwin has pointed you to a known problem with replicating blobs and text and ntext. You are likely running into this problem.

Now if you send a new snapshot down, by default all subscriber data will be replaced with a copy of the data from the publisher.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 15

In document it is written:

To modify replication configuration, use one of the following:

• Create new publications for any new articles. Do not add new articles to existing publications.

Can I go with this...

---

Thanks

Answer 16

It may work - if that is the particular bug you are running into. I have ran into a different one where you update a text column with a null or blank space which generates the same error.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 17

Can I do it after disable the application users who are using it..so that database will not be updated.

our main purpose is that database table should not be update of publication.

---

Thanks

Answer 18

Hi hilary,

I have got success after created a new publication for those 6 articles. 

---

Thanks