March 10, 2020 at 2:59 pm
Hello all... had an odd issue crop up and was wondering if anyone had ideas on what might have caused it...
We have a nightly job that copies data from one of our local databases (SQL Server 2016) to an Oracle database at our corporate headquarters. The setup is pretty basic - we have a linked server using the OraOLEDB.Oracle provider. Overall this has worked pretty flawlessly. We're only transferring a few hundred thousand rows.
The job is set to retry 10 times in case of network issues and we have logic to track the last successful table that was copied and start after that point.
The issue is that last night, it doesn't appear to have copied all of the data on multiple tables. It obviously copied some data because the first thing we do is clear the tables on the Oracle side - but the row counts did not match. However, according to the job history no errors occurred; there were no retries and the job reports success. I ran the exact same script today and it worked as expected.
We have had failures in the past due to networking issues, but those have always been caught. I am trying to figure out how this time the inserts failed - multiple times - but no error.
Anyway, I realize this is all kinda vague but was just wondering if anyone has seen something like this before, or might have some insight as to how it might happen.
thanks
March 10, 2020 at 4:12 pm
so , some tables got partial data? are you using SSIS? i'm kinda going down the "batch size" route.. maybe incorrectly
not sure why you aren't getting an error, but you could hand code in a check at then end of each table copy, and throw an error if the rowcounts don't match .. I suppose i'm suggesting check after each table, not at the end of the job
mv
MVDBA
March 10, 2020 at 5:25 pm
We have had failures in the past due to networking issues, but those have always been caught. I am trying to figure out how this time the inserts failed - multiple times - but no error.
Just a thought but maybe it's not the inserts failing - could be the selects didn't capture all the rows. One scenario I can think of is if any locking hints are used and there is some activity on the tables in the SQL Server end when the job runs.
Sue
March 10, 2020 at 5:35 pm
cphite wrote:We have had failures in the past due to networking issues, but those have always been caught. I am trying to figure out how this time the inserts failed - multiple times - but no error.
Just a thought but maybe it's not the inserts failing - could be the selects didn't capture all the rows. One scenario I can think of is if any locking hints are used and there is some activity on the tables in the SQL Server end when the job runs.
Sue
good point - a further question - at a guess, what percentage of data is missing? if it's less than 5% i'd go with sue's theory. 50% then i'm going with my first guess
MVDBA
March 10, 2020 at 5:38 pm
We aren't using SSIS - we have a stored procedure that we call where we're doing basically this for each of about a dozen tables:
truncate table xfer_fact_booking
insert xfer_fact_booking
select
*
from
bizint.financials.dbo.fact_booking
The fact_booking is a view; we put the results in table xfer_fact_booking specifically to avoid issues with locking and whatnot. The database is used only for this purpose, so there really isn't any other activity that should occur. After we fill that table we delete from the remote Oracle table and insert into it as follows:
delete from [ORACLEBOX.OURCORP.COM:1533]..[STG_OWNER].[FACT_BOOKING_STG]
insert [ORACLEBOX.OURCORP.COM:1533]..[STG_OWNER].[FACT_BOOKING_STG]
select * from xfer_fact_booking
In the past we have had network issues where it'll time out - but that always results in an error. In this case, it reported no errors and proceeded as though everything was fine. The only indication we had that anything was wrong was that we do a COUNT(*) against the tables on both sides as a last check, and that indicated different row counts.
I appreciate the replies though... I am going to take Mike's advice and add some checks at each table...
March 10, 2020 at 5:46 pm
Found the problem.... *sigh*
Our infrastructure team spun up a copy of our production environment, but forgot to update the firewall to prevent it from communicating outside itself. So it updated the data after the "real" one did...
March 10, 2020 at 6:59 pm
Found the problem.... *sigh*
Our infrastructure team spun up a copy of our production environment, but forgot to update the firewall to prevent it from communicating outside itself. So it updated the data after the "real" one did...
Heh... perhaps you invented a whole new meaning to "double entry accounting". 😀
Anyway... thanks for updating us on what the true fault turned out to be.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2020 at 7:26 pm
Hehe... the worst part is the same thing happened like a year ago, but it's been fine since we did the firewall changes... they just forgot to apply them when they spun up the test environment.
I've added a step to the job to check via IP address if it's running in production, and to abort if it's not.
March 11, 2020 at 8:36 am
Hehe... the worst part is the same thing happened like a year ago, but it's been fine since we did the firewall changes... they just forgot to apply them when they spun up the test environment.
I've added a step to the job to check via IP address if it's running in production, and to abort if it's not.
be carefull about using IP address - use a DNS name - when you upgrade or move hardware it becomes a real pig
MVDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply