SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replication job error once server rebootes


Replication job error once server rebootes

Author
Message
aminbangash110
aminbangash110
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 7
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.
anthony.green
anthony.green
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101334 Visits: 8698
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.



How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


aminbangash110
aminbangash110
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 7
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;
aminbangash110
aminbangash110
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 7
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.
aminbangash110
aminbangash110
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 7
Hey i found that but it says it can be found in distribution database.
anthony.green
anthony.green
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101334 Visits: 8698
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.




How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


aminbangash110
aminbangash110
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 7
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.
aminbangash110
aminbangash110
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 7
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.
anthony.green
anthony.green
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101334 Visits: 8698
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.



How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


aminbangash110
aminbangash110
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 7
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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search