File Processing

Stuck Files

Occasionally, a file will get stuck during a processing step (for example, validation or de-identification). In order to identify these issues more easily, a stored procedure was created to scan the upload activity log for any file that is in a “running” state but has not had any log entry for over 12 hours. This check is triggered every daily in via a logic app in staging and production.

In the even that a file is found in that state, an email is sent to staff in development and research including the filename, and the number of hours since last activity was recorded. This can be changed from the logic app.

/****** Object:  StoredProcedure [dbo].[usp_GetStuckUploads]    Script Date: 6/1/2026 12:13:01 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_GetStuckUploads]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        u.filename,
        u.org,
        u.status,
        t.LastActivityTime,
        DATEDIFF(HOUR, t.LastActivityTime, GETDATE()) AS hours_since_last_activity
    FROM dbo.uploads u
    CROSS APPLY (
        SELECT MAX(v) AS LastActivityTime
        FROM (VALUES
            (u.[timestamp]),
            (u.l1_timestamp),
            (u.l2_timestamp),
            (u.l3_timestamp),
            (u.deid_timestamp),
            (u.cert_timestamp),
            (u.loaded_timestamp)
        ) AS value(v)
    ) t
    WHERE
        (
            u.status IN ('rerun', 'inprogress', 'running')
            OR u.l1status IN (3,4,5,6)
            OR u.l2status IN (3,4,5,6)
            OR u.l3status IN (3,4,5,6)
            OR u.deidstatus IN (3,4,5,6)
            OR u.certstatus IN (3,4,5,6)
        )
        AND t.LastActivityTime < DATEADD(HOUR, -12, GETDATE())
    ORDER BY t.LastActivityTime;
END
GO