January 2, 2019 at 11:40 pm
We have implemented CDC on few tables on our database. From CDC tables, we load the data into datawarehouse and store all the changes. Job to run to load to Datawarehouse table kicks off at 12.01 am every morning and it is a call to a stored proc.
But for some reason, for past of couple of weeks, records didn't get loaded in the datawarehouse table, although job to to load to datawarehouse table ran sucessfully every night.
Since the CDC tables still had data, we ran the stored proc manually for missing days and all the data loaded in the datawarehouse table.
But the question is what could have caused this to not load the data? Data was present in CDC tables and Job to run to load to datawarehouse ran sucessfully every night (according to SQL server agent history).
Any idea how to troubleshoot CDC? I'm kind of new to this architecture.
Thanks in advance.
January 3, 2019 at 9:26 am
did you restore database on some other server.. ?
January 3, 2019 at 9:31 am
Not sure what you mean. Didn't restore this specific database to other server. We had to restore copy of its database to the same server with different name for some other purpose. But that's dropped now.
January 3, 2019 at 10:41 am
SQL_Surfer - Wednesday, January 2, 2019 11:40 PMWe have implemented CDC on few tables on our database. From CDC tables, we load the data into datawarehouse and store all the changes. Job to run to load to Datawarehouse table kicks off at 12.01 am every morning and it is a call to a stored proc.But for some reason, for past of couple of weeks, records didn't get loaded in the datawarehouse table, although job to to load to datawarehouse table ran sucessfully every night.
Since the CDC tables still had data, we ran the stored proc manually for missing days and all the data loaded in the datawarehouse table.
But the question is what could have caused this to not load the data? Data was present in CDC tables and Job to run to load to datawarehouse ran sucessfully every night (according to SQL server agent history).
Any idea how to troubleshoot CDC? I'm kind of new to this architecture.
Thanks in advance.
The stored procedure ran fine manually but not in the job so you'd probably want to start looking at that job. Did you check the job history? Has the job changed at all since the issues started? You can also add an output file for the job - go to the job step that executes the stored procedure and go to the advanced tab. From there you can add an output file if there isn't one already.
Sue
January 3, 2019 at 10:54 am
No changes on the job that runs the stored procedure. Agent history shows sucessful runs every day. Adding output file now as you suggested. Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply