I have a data warehouse which runs several jobs during the night in order to update data.
Background and job execution(s) setup
One of these jobs updates the FactSale table consisting of the sales. This is ran at 01.00 AM once daily for a specific store. We can call this store
StoreA and the job is called
StoreAUpdate which executes the SSIS package which updates
StoreA. FactSale consists of a column named
InsertTime which is generated in
StoreAUpdate package updates data in
InsertTime is simply
getdate() at execution of the package.
Another job is executed at 5 AM, let's call it
StoreAExport, which finds all transactions corresponding to
StoreA which has an
InsertTime greater than the last time the execution of
StoreAExport executed (the previous day at 5 AM).
StoreAExport job in SQL agent has three steps, where the last step is to find the datestamp of the last time
StoreAExport job was executed successfully. So, if
StoreAExport is executed without any errors at ´2019-10-10 05:00:00´, then that timestamp is saved in a table
LastRun on the SQL server. This timestamp is found using:
INSERT INTO LastRun Select top 1 dbo.agent_datetime(jh.run_date,jh.run_time) as LastRun from msdb.dbo.sysjobhistory jh JOIN msdb.dbo.sysjobs j ON j.job_id = jh.job_id where j.name = 'StoreAExport' and jh.run_status = 1 and jh.step_id = 1
How it should work (and do work during daytime)
The idea is that since
FactSale table for
StoreA only gets updated once a day, at 1 AM, and the
StoreAExport job is only executed once a day at 5 AM and because the step to update
LastRun is ran as the last step in the sequence of job
StoreAExport ought to find all transactions from 5 AM the previous day to 5 AM the current day.
This idea works if I run the
StoreAExport job manually during the day, but during nighttime at the scheduled time of execution, no transactions are found!
FactSale consists of 600 transactions corresponding to the previous date, all of these with the
InsertTime = '2019-10-11 01:00:00.000. If I run
StoreAExport now, the transactions are found and exported.
I've created a package for debugging which simply tries to count all transactions with the same time constraints as
StoreAExport job (based on
LastRun table) and the SQL task which conducts the
COUNT fails due to the ResultSet being defined as a SingleRow but no rows are returned. The debug package fails until 08:30. It isn't until then the transactions seems to be "recognized" by the SQL server.
What I have tried and personal thoughts
I've tried to include
WITH (NOLOCK) in each
FROM clause in the
StoreAExport step which finds the transactions and creates the export file, but even then, the transactions doesn't seem to be "recognized" by SQL server.
StoreAUpdate job takes about 5 minutes, so it is completed around 01:05 AM.
FactSale gets updated frequently during the day. It is only
StoreA which needs to be ran once daily. All other stores' sales are ran continually. The first job to update
FactSale with different stores doesn't get executed until 08 AM, so I fail to see how that would impose a 7 hour gap between data being inserted into a table, and data being recognized. Especially since I use WITH (NOLOCK).
I've asked our DBA if there are any backups or anything in the line of that which occurs at around 5 AM, which he said only happens during weekends (neither of these jobs are ran during weekends).