Skip to main content
Kofax

How to manage a backlog of data in the TotalAgility Reporting Staging database

Article # 3036695 - Page views: 117

Issue

There is a backlog of data in the TotalAgility_Reporting_Staging database, or the reportingstaginglive tables in an OPMT database.  It is important to have a view of the size and date ranges of the backlog.

Solution

KTA components, such as the webserver, Core Worker, or Transformation Server, write compressed reporting data directly to the wsa_messages table of the TotalAgility_Reporting_Staging database.  Then it is up to the Kofax TotalAgility Reporting Service to expand this raw data into the other staging tables, and then process it into the form that ends up in the TotalAgility_Reporting database.

If the Kofax TotalAgility Reporting Service is not running, then raw reporting data will build up in the form of an increasing number of records in the wsa_messages table of the TotalAgility_Reporting_Staging database.  If errors are Interfering with processing, records could build up in either the wsa_messages table or the other staging tables.

If the service was simply stopped, restart it to continue processing.  If a technical issue is preventing further processing, then it will be important to quantify the amount of backlogged data as part of troubleshooting with technical support. 

Query

Run the following diagnostic query on the TotalAgility_Reporting_Staging database to show the number of records along with the dates of the earliest and latest records.

SELECT 'wsa_messages.enqueued_time' AS TableColumn, COUNT(*) AS RecordCount,
    MIN(enqueued_time) AS Earliest, MAX(enqueued_time) AS Latest,
    DATEDIFF(DAY,MIN(enqueued_time),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM [wsa_messages] WITH(NOLOCK)
UNION ALL
SELECT 'kc_batch_action.session_end_datetime' AS TableColumn, COUNT(*) AS RecordCount,
    MIN(session_end_datetime) AS Earliest, MAX(session_end_datetime) AS Latest,
    DATEDIFF(DAY,MIN(session_end_datetime),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM [kc_batch_action] WITH(NOLOCK)
UNION ALL
SELECT '|-> status_kc_doc' AS TableColumn, COUNT(*) AS RecordCount, 
    NULL AS Earliest, NULL AS Latest, NULL AS DaysAgeOfEarliest
    FROM status_kc_doc AS s WITH(NOLOCK)
UNION ALL
SELECT '|-> status_kc_doc_page' AS TableColumn, COUNT(*) AS RecordCount, 
    NULL AS Earliest, NULL AS Latest, NULL AS DaysAgeOfEarliest
    FROM status_kc_doc_page AS s WITH(NOLOCK)
UNION ALL
SELECT '|-> status_kc_doc_field' AS TableColumn, COUNT(*) AS RecordCount, 
    NULL AS Earliest, NULL AS Latest, NULL AS DaysAgeOfEarliest
    FROM status_kc_doc_field AS s WITH(NOLOCK)
UNION ALL
SELECT 'batch_edit.action_datetime' AS TableColumn, COUNT(action_datetime) AS NonNullCount,
    MIN(action_datetime) AS Earliest, MAX(action_datetime) AS Latest,
    DATEDIFF(DAY,MIN(action_datetime),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM [batch_edit] WITH(NOLOCK)
UNION ALL
SELECT '|-> change_details_batch_act.action_datetime' AS TableColumn, COUNT(*) AS RecordCount,
    MIN(action_datetime) AS Earliest, MAX(action_datetime) AS Latest,
    DATEDIFF(DAY,MIN(action_datetime),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM [change_details_batch_act] WITH(NOLOCK)
UNION ALL
SELECT '|-> change_details_doc_act.action_datetime' AS TableColumn, COUNT(*) AS RecordCount,
    MIN(action_datetime) AS Earliest, MAX(action_datetime) AS Latest,
    DATEDIFF(DAY,MIN(action_datetime),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM [change_details_doc_act] WITH(NOLOCK)
UNION ALL
SELECT '|-> change_details_field_act.action_datetime' AS TableColumn, COUNT(*) AS RecordCount,
    MIN(action_datetime) AS Earliest, MAX(action_datetime) AS Latest,
    DATEDIFF(DAY,MIN(action_datetime),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM [change_details_field_act] WITH(NOLOCK)
UNION ALL
SELECT '|-> change_details_folder_act.action_datetime' AS TableColumn, COUNT(*) AS RecordCount,
    MIN(action_datetime) AS Earliest, MAX(action_datetime) AS Latest,
    DATEDIFF(DAY,MIN(action_datetime),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM [change_details_folder_act] WITH(NOLOCK)
UNION ALL
SELECT '|-> change_details_general_act.action_datetime' AS TableColumn, COUNT(*) AS RecordCount,
    MIN(action_datetime) AS Earliest, MAX(action_datetime) AS Latest,
    DATEDIFF(DAY,MIN(action_datetime),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM [change_details_general_act] WITH(NOLOCK)
UNION ALL
SELECT '|-> change_details_page_act.action_datetime' AS TableColumn, COUNT(*) AS RecordCount,
    MIN(action_datetime) AS Earliest, MAX(action_datetime) AS Latest,
    DATEDIFF(DAY,MIN(action_datetime),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM [change_details_page_act] WITH(NOLOCK)
OPMT Query

On an OPMT system run the following query on a specific tenant database to get information from the live environment.  If the dev environment is needed, replace "reportingstaginglive" with "reportingstagingdev".

SELECT 'wsa_messages.enqueued_time' AS TableColumn, COUNT(*) AS RecordCount,
    MIN(enqueued_time) AS Earliest, MAX(enqueued_time) AS Latest,
    DATEDIFF(DAY,MIN(enqueued_time),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM reportingstaginglive.[wsa_messages] WITH(NOLOCK)
UNION ALL
SELECT 'kc_batch_action.session_end_datetime' AS TableColumn, COUNT(*) AS RecordCount,
    MIN(session_end_datetime) AS Earliest, MAX(session_end_datetime) AS Latest,
    DATEDIFF(DAY,MIN(session_end_datetime),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM reportingstaginglive.[kc_batch_action] WITH(NOLOCK)
UNION ALL
SELECT '|-> status_kc_doc' AS TableColumn, COUNT(*) AS RecordCount, 
    NULL AS Earliest, NULL AS Latest, NULL AS DaysAgeOfEarliest
    FROM reportingstaginglive.status_kc_doc AS s WITH(NOLOCK)
UNION ALL
SELECT '|-> status_kc_doc_page' AS TableColumn, COUNT(*) AS RecordCount, 
    NULL AS Earliest, NULL AS Latest, NULL AS DaysAgeOfEarliest
    FROM reportingstaginglive.status_kc_doc_page AS s WITH(NOLOCK)
UNION ALL
SELECT '|-> status_kc_doc_field' AS TableColumn, COUNT(*) AS RecordCount, 
    NULL AS Earliest, NULL AS Latest, NULL AS DaysAgeOfEarliest
    FROM reportingstaginglive.status_kc_doc_field AS s WITH(NOLOCK)
UNION ALL
SELECT 'batch_edit.action_datetime' AS TableColumn, COUNT(action_datetime) AS NonNullCount,
    MIN(action_datetime) AS Earliest, MAX(action_datetime) AS Latest,
    DATEDIFF(DAY,MIN(action_datetime),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM reportingstaginglive.[batch_edit] WITH(NOLOCK)
UNION ALL
SELECT '|-> change_details_batch_act.action_datetime' AS TableColumn, COUNT(*) AS RecordCount,
    MIN(action_datetime) AS Earliest, MAX(action_datetime) AS Latest,
    DATEDIFF(DAY,MIN(action_datetime),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM reportingstaginglive.[change_details_batch_act] WITH(NOLOCK)
UNION ALL
SELECT '|-> change_details_doc_act.action_datetime' AS TableColumn, COUNT(*) AS RecordCount,
    MIN(action_datetime) AS Earliest, MAX(action_datetime) AS Latest,
    DATEDIFF(DAY,MIN(action_datetime),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM reportingstaginglive.[change_details_doc_act] WITH(NOLOCK)
UNION ALL
SELECT '|-> change_details_field_act.action_datetime' AS TableColumn, COUNT(*) AS RecordCount,
    MIN(action_datetime) AS Earliest, MAX(action_datetime) AS Latest,
    DATEDIFF(DAY,MIN(action_datetime),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM reportingstaginglive.[change_details_field_act] WITH(NOLOCK)
UNION ALL
SELECT '|-> change_details_folder_act.action_datetime' AS TableColumn, COUNT(*) AS RecordCount,
    MIN(action_datetime) AS Earliest, MAX(action_datetime) AS Latest,
    DATEDIFF(DAY,MIN(action_datetime),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM reportingstaginglive.[change_details_folder_act] WITH(NOLOCK)
UNION ALL
SELECT '|-> change_details_general_act.action_datetime' AS TableColumn, COUNT(*) AS RecordCount,
    MIN(action_datetime) AS Earliest, MAX(action_datetime) AS Latest,
    DATEDIFF(DAY,MIN(action_datetime),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM reportingstaginglive.[change_details_general_act] WITH(NOLOCK)
UNION ALL
SELECT '|-> change_details_page_act.action_datetime' AS TableColumn, COUNT(*) AS RecordCount,
    MIN(action_datetime) AS Earliest, MAX(action_datetime) AS Latest,
    DATEDIFF(DAY,MIN(action_datetime),GETUTCDATE()) AS DaysAgeOfEarliest
    FROM reportingstaginglive.[change_details_page_act] WITH(NOLOCK)

Level of Complexity 

Easy

Applies to  

Product Version Build Environment Hardware
Kofax TotalAgility All      

 

  • Was this article helpful?