Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL 2008 Transactinal Replication, log reader agent fails during snapshot creation, then it starts but does nothing.

Pregunta SQL 2008 Transactinal Replication, log reader agent fails during snapshot creation, then it starts but does nothing.

  • Wednesday, October 03, 2012 1:01 PM
     
     
    Sign In to Vote
    0

    Hi, I´m trying to create several transactional replications, all based on the same DB, with the same set of articles, but with different filter values for them.

    What I did was:

    1 - Manually create the first replication,

    2 - then generated a script from it, changing the filter values, object names, etc.

    3- used the scripts to create the replicas.

    after I made them when trying to initialize running the snapshot agent(1 replica at a time), i´m getting this error

    "The process could not execute 'sp_replcmds' on 'serversql'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
    Get help: http://help/MSSQL_REPL20011
    Execution of filter stored procedure 1300251737 failed. See the SQL Server errorlog for more information. (Source: MSSQLServer, Error number: 18764)
    Get help: http://help/18764
    The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {00016200:000051dc:0041}. 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 'serversql'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
    Get help: http://help/MSSQL_REPL22037"

    The replica fails to initialize with the folowing error on the Synchronization status:

    "The concurrent snapshot for publication 'xx' is not available because it has not been fully generated or the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted, the Snapshot Agent for the publication m"

    snapshot is complete, and is present on the folder, i guess the error on the log reader is preventing it from being activated.

    after these errors, all the other replicas(some of them were working) seems to stop delivering commands, the log reader seems to be running but I only see messsages like this:

    "Delivering replicated transactions, xact count: 388,  command count: 3101"

    instead of the normal "x comands delivered..."

    I thought in adding more log readers(if possible), or try to manually create the replicas(admninistration nightmare)

    Any suggestions are welcome!!!!

    regards

    Mauro!

     

All Replies

  • Wednesday, October 03, 2012 2:03 PM
    Moderator
     
     
    Sign In to Vote
    0

    It is hard to see where you are at from this post.  It sounds like you had a show stopping failure, but somehow the log reader agent has started again and is doing useful work.

    What is the status of the log reader agent now?

    You will only be able to create one log reader agent per database, no matter how many publications are in that database.

    If you are still getting the command construction error message your log reader agent has failed, likely due to a bug. Your best approach is to contact Microsoft Customer Support Services so they can triage the problem, fix it or come up with 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

     
  • Wednesday, October 03, 2012 2:54 PM
     
     
    Sign In to Vote
    0

    It is hard to see where you are at from this post.  It sounds like you had a show stopping failure, but somehow the log reader agent has started again and is doing useful work.

    What is the status of the log reader agent now?

    You will only be able to create one log reader agent per database, no matter how many publications are in that database.

    If you are still getting the command construction error message your log reader agent has failed, likely due to a bug. Your best approach is to contact Microsoft Customer Support Services so they can triage the problem, fix it or come up with 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

    Thanks for your reply, Hilary, aparently my filters are not simple, but complex and time consuming (subquerys since i can't use  joins in filters), causing some blocking on the process, I dont deeply know how can this affect log reader, as I guess it should read the log, not the actual DB, anyway, I´ll switch to snapshot replication, as I´m afraid those filters can also affect performance.

    kind regards.