SQL Server SQL query to show file loading is stuck

7kjnsjlb 于 2023-06-28 发布在其他

关注(0)|答案(1)|浏览(106)

Microsoft SQL Server is connected to our ETL (ab-initio) which shows the % progress of a file that is loading. When the file is running successfully the progress increases each time I run my file loading query but no other column updates including the date. Is there any way I can run a query which shows that a file is stuck running? This means that the progress would be stuck at the same number for 30 minutes or so. Please note that no date column updates as the progress increases for file loading.

Declare @Now DATETIME = CURRENT_TIMESTAMP

Declare @FromTimeStamp DATETIME= DATEADD (HOUR, -1, @NOW)

Select * from ExecuteFileStatus
Where Runstate = (‘RUNNING’)
And Progress = @FromTimeStamp

This image shows File loading for one file after I run the following query- select * from ExecuteFileStatus order by ABUpdateTime desc I ran the query 3 separate times and as you can see the File has successfully run until it completely loaded

sql-server

来源：https://stackoverflow.com/questions/76535825/sql-query-to-show-file-loading-is-stuck

1条答案

按热度按时间

3htmauhk1#

So, in your particular case, it appears to be the "Started" column that indicates when the file upload started, so that's the column that you need to compare against the current time (with whatever offset you are going to use - eg. 30 minutes, 1 hour, etc.).

DECLARE @offset smallint; -- number of minutes to indicate upload delay
Declare @Now DATETIME = CURRENT_TIMESTAMP;

SELECT @offset = -60;
Declare @FromTimeStamp DATETIME = DATEADD (minute, @offset, @NOW);

Select * from ExecuteFileStatus
Where Runstate = (‘RUNNING’)
And [Started] <= @FromTimeStamp;

That's going to give you uploads that have been running for more than the offset amount of time. If you really needed to check whether a particular upload is stuck at the same % for a set period of time, then you'd have to either a) go to the code that is doing the file upload, and include a date/time update whenever the progress is incremented (eg. could the "ABUpdate..." column have its value update when the progress changes?), or b) write off "snapshot" records of the file upload data to a separate table so that you can compare it as the progress changes - that's a reasonable task, though, and will depend on if you, yourself, have the necessary permissions to create tables, etc. in the database

赞(0）回复(0）举报 2023-06-28

我来回答

SQL Server SQL query to show file loading is stuck

1条答案

相关问题

热门标签

最新问答