Post reply

SQL SERVER 2005 Transaction Replication Debugging

  • December 6, 2011 at 11:03 am

    #249219

    I'm finding it very hard to figure out where my problem is occuring in my transactional replication.

    I get an error stating that and insert is trying to insert and * into an integer field. According to

    replication Monitor, this is happening between the Distributer and the Subscriber. Where can I go to see the exact statement so that I can chase this down? The only thing I know to do is to turn sql profiler on the subscriber database server to see if I can spot something there. There's got to be a better way to debug this I would think.

    Just to give you more details, I got the replication happening continously. We shot down the idea of using log-shipping which would have been much easier because they wanted to install triggers on the subscriber database tables.

    Thanks for any help that you can provide.

  • December 6, 2011 at 11:45 pm

    #1417789

    ericwenger1 (12/6/2011)

    I get an error stating that and insert is trying to insert and * into an integer field.

    Where do you see this error message?

    Can you post the complete error message...

    ericwenger1 (12/6/2011)

    ... they wanted to install triggers on the subscriber database tables.

    I guess triggers are causing the issue...

  • December 7, 2011 at 12:52 am

    #1417820

    ericwenger1 (12/6/2011)

    Just to give you more details, I got the replication happening continously. We shot down the idea of using log-shipping which would have been much easier because they wanted to install triggers on the subscriber database tables.

    I assume you are using Transactional Replication. In that case, triggers on subscriber tables will make the replication's performance terrible & the latency will be very high between publisher & subscriber.

    From MSDN:

    Be cautious with application logic in triggers.

    Business logic in user-defined triggers at the Subscriber can slow down the replication of changes to the Subscriber:

    For transactional replication, it can be more efficient to include this logic in custom stored procedures used to apply the replicated commands.

    You can read more here to improve the performance of replication.

    Sujeet Singh

  • December 7, 2011 at 1:25 am

    #1417829

    Were you able to locate the error message?

    M&M

  • December 7, 2011 at 4:29 am

    #1417945

    Thanks for your reply. I will post it shortly

  • December 8, 2011 at 7:21 pm

    #1419032

    here is the error that I'm getting

    Conversion failed when converting the varchar value '*' to data type int. (Source: MSSQLServer, Error number: 245)

    now prior to this error message I got this error message

    if @@trancount > 0 rollback tran

    (Transaction sequence number: 0x00029F9B000001F0004E00000000, Command ID: 1654)

    Error messages:

    The process could not connect to Subscriber 'DALGPDB01'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20084)

  • December 9, 2011 at 12:21 pm

    #1419579

    You can find the exact command having issues by running this:

    exec sp_BrowseReplCmds '0x00029F9B000001F0004E00000000','0x00029F9B000001F0004E00000000'

    against your distribution database.

    This will return all commands in that transactionID and you would then look for command#1654

    There is also a column in the output (don't remember the name) that says whether it was a partial command or not (partial = 1). If this is a 1 then you'll also want to include command 1655 as well

    Good luck

    Ben

  • December 9, 2011 at 5:19 pm

    #1419667

    Thanks a milliion

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply