0

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 SSIS when StoreAUpdate package updates data in FactSale. 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).

The 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, then 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 InsertTime in FactSale and LastRun in 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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.