Replication job error once server rebootes

  • Hello
    I am facing an issue after rebooting the replication server while executing the job of replication i.e 

    Started: 10:23:00 AM
    Error: 2017-09-25 10:23:14.84
     Code: 0xC0202009
     Source: Replicate cdr_main Insertions Replication cdr_main [118]
     Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The statement has been terminated.".
    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'pk_cdr_main'. Cannot insert duplicate key in object 'dbo.cdr_main'.".
    End Error
    Error: 2017-09-25 10:23:14.84

     Code: 0xC0209029
     Source: Replicate cdr_main Insertions Replication cdr_main [118]
     Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (131)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (131)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
    End Error
    Error: 2017-09-25 10:23:14.84
     Code: 0xC0047022
     Source: Replicate cdr_main Insertions SSIS.Pipeline
     Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Replication cdr_main" (118) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (131). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
    End Error
    Error: 2017-09-25 10:23:22.81
     Code: 0xC02020C4
     Source: Replicate cdr_main Insertions Primary cdr_main [1]
     Description: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
    End Error
    Error: 2017-09-25 10:23:22.81
     Code: 0xC0047038
     Source: Replicate cdr_main Insertions SSIS.Pipeline
     Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Primary cdr_main" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
    End Error
    DTExec: The package execution returned DTSER_FAILURE (1).
    Started: 10:23:00 AM
    Finished: 10:24:10 AM
    Elapsed: 69.638 seconds

    Can anybody help me with that as destination table don't have latest data.

  • Take a look in the replication monitor, it should give you a transaction ID that is failing.  With that you can use sp_browserplcmds to find out what that transaction is trying to go, then work the error from that point.

    looks like a transaction has been replayed but not marked complete in the distributor, so your trying to insert the same record again which is failing with a duplication key error.

  • Hey thanks for the reply but cdr's are replicated to last day but error on replication is of current day i mean the record doesn't exist for that day in databases;

  • I can't find the above stored procedure you mentioned and ms sql server 2008 R2 i'm using, where to find the replication monitory. Can you tell me exactly about it please.

  • Hey i found that but it says it can be found in distribution database.

  • aminbangash110 - Monday, September 25, 2017 5:21 AM

    I can't find the above stored procedure you mentioned and ms sql server 2008 R2 i'm using, where to find the replication monitory. Can you tell me exactly about it please.

    Are you actually using replication or are you using SSIS?

    Re-reading the full original post looks to me like its a SSIS package not replication.

    The error you have is your trying to violate the PRIMARY KEY, you cannot do this, look though the job, debug it and find the task causing the error.  The problem for example is because your trying to insert the ID 100 into cdr_main when ID 100 already exists in cdr_main

    sp_browserplcmds, is in the distributor database, replication monitor, can be opened by right clicking the replication folder in SSMS.

  • why data is not actually synced. Basically replication was stop for example for 24 16:00 but it gives me that error for 25th date and i can see data of only 24 how can data be duplicated if it doesn't exist. This error happens when server is rebooted.

  • We are using SSIS job to replicate data from primary to secondary and our job give above error i have mentioned and don't know why because job was working fine before restarting server.

  • aminbangash110 - Monday, September 25, 2017 5:33 AM

    why data is not actually synced. Basically replication was stop for example for 24 16:00 but it gives me that error for 25th date and i can see data of only 24 how can data be duplicated if it doesn't exist. This error happens when server is rebooted.

    You've not answered the first question, is this replication (Transaction / Merge / Snapshot) or is this SSIS extracting and loading data?

    If its actual proper replication look at the monitor and the sp_browsereplcmds, it will give you what is failing then you can fix it.

    If its SSIS, open the project, debug the package causing the problems.

    How can data be duplicated?  I don't know your code, your systems or your processes.  You will need to go and debug.  Something is violating the primary key for cdr_main, go find what that is and fix it, then move on to the next error.

  • anthony.green - Monday, September 25, 2017 5:39 AM

    aminbangash110 - Monday, September 25, 2017 5:33 AM

    why data is not actually synced. Basically replication was stop for example for 24 16:00 but it gives me that error for 25th date and i can see data of only 24 how can data be duplicated if it doesn't exist. This error happens when server is rebooted.

    You've not answered the first question, is this replication (Transaction / Merge / Snapshot) or is this SSIS extracting and loading data?

    If its actual proper replication look at the monitor and the sp_browsereplcmds, it will give you what is failing then you can fix it.

    If its SSIS, open the project, debug the package causing the problems.

    How can data be duplicated?  I don't know your code, your systems or your processes.  You will need to go and debug.  Something is violating the primary key for cdr_main, go find what that is and fix it, then move on to the next error.

    Replication monitor is not set there but we are replicating data via a job from primary server and our job failed with following error as i have provided above. how can i found the duplicate value as it didn't exist for 25. data is already replicated to 24.

  • aminbangash110 - Monday, September 25, 2017 5:48 AM

    anthony.green - Monday, September 25, 2017 5:39 AM

    aminbangash110 - Monday, September 25, 2017 5:33 AM

    why data is not actually synced. Basically replication was stop for example for 24 16:00 but it gives me that error for 25th date and i can see data of only 24 how can data be duplicated if it doesn't exist. This error happens when server is rebooted.

    You've not answered the first question, is this replication (Transaction / Merge / Snapshot) or is this SSIS extracting and loading data?

    If its actual proper replication look at the monitor and the sp_browsereplcmds, it will give you what is failing then you can fix it.

    If its SSIS, open the project, debug the package causing the problems.

    How can data be duplicated?  I don't know your code, your systems or your processes.  You will need to go and debug.  Something is violating the primary key for cdr_main, go find what that is and fix it, then move on to the next error.

    Replication monitor is not set there but we are replicating data via a job from primary server and our job failed with following error as i have provided above. how can i found the duplicate value as it didn't exist for 25. data is already replicated to 24.

    09/25/2017 07:32:01,spid60,Unknown,Problem inserting data into destination table<c/> exiting.
    09/25/2017 07:31:01,spid54,Unknown,Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
    09/25/2017 07:31:00,spid54,Unknown,Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
    09/25/2017 07:30:03,spid51,Unknown,Using 'xplog70.dll' version '2009.100.1600' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
    09/25/2017 07:30:03,spid51,Unknown,Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
    09/25/2017 07:30:03,spid51,Unknown,Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
    09/25/2017 07:30:03,spid51,Unknown,Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
    09/25/2017 07:30:03,spid51,Unknown,Using 'xpsqlbot.dll' version '2009.100.1600' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
    09/25/2017 07:30:03,spid51,Unknown,Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
    09/25/2017 07:30:01,spid7s,Unknown,Recovery is complete. This is an informational message only. No user action is required.

    getting above error in sql server log in ssis.

  • aminbangash110 - Monday, September 25, 2017 5:54 AM

    aminbangash110 - Monday, September 25, 2017 5:48 AM

    anthony.green - Monday, September 25, 2017 5:39 AM

    aminbangash110 - Monday, September 25, 2017 5:33 AM

    why data is not actually synced. Basically replication was stop for example for 24 16:00 but it gives me that error for 25th date and i can see data of only 24 how can data be duplicated if it doesn't exist. This error happens when server is rebooted.

    You've not answered the first question, is this replication (Transaction / Merge / Snapshot) or is this SSIS extracting and loading data?

    If its actual proper replication look at the monitor and the sp_browsereplcmds, it will give you what is failing then you can fix it.

    If its SSIS, open the project, debug the package causing the problems.

    How can data be duplicated?  I don't know your code, your systems or your processes.  You will need to go and debug.  Something is violating the primary key for cdr_main, go find what that is and fix it, then move on to the next error.

    Replication monitor is not set there but we are replicating data via a job from primary server and our job failed with following error as i have provided above. how can i found the duplicate value as it didn't exist for 25. data is already replicated to 24.

    09/25/2017 07:32:01,spid60,Unknown,Problem inserting data into destination table<c/> exiting.
    09/25/2017 07:31:01,spid54,Unknown,Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
    09/25/2017 07:31:00,spid54,Unknown,Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
    09/25/2017 07:30:03,spid51,Unknown,Using 'xplog70.dll' version '2009.100.1600' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
    09/25/2017 07:30:03,spid51,Unknown,Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
    09/25/2017 07:30:03,spid51,Unknown,Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
    09/25/2017 07:30:03,spid51,Unknown,Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
    09/25/2017 07:30:03,spid51,Unknown,Using 'xpsqlbot.dll' version '2009.100.1600' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
    09/25/2017 07:30:03,spid51,Unknown,Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
    09/25/2017 07:30:01,spid7s,Unknown,Recovery is complete. This is an informational message only. No user action is required.

    getting above error in sql server log in ssis.

    Go and debug whatever the job is doing.

    The above is the normal startup messages, nothing to worry about there except for that SPID60 one, but you need to go and debug it.

Viewing 12 posts - 1 through 11 (of 11 total)

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