Tracking the progress of a long run process

Published on: 3rd Jul 2012

Updated on: 17th May 2021

Overview

Have you ever wondered what is the current progress of an active long run process? The following solution will help you in tracking the long run process in a stored procedure or even C# program.

For example, you have a stored procedure that requires you to run at least 30 minutes and the DBA wants to be able to see the current progress. Sounds magical?

Implementation strategy

• In the long run process, it must fulfill the following requirements,

  • The UPDATE and DELETE statements must be affecting minimal records. It is advisable to avoid executing an UPDATE and DELETE statement that affects thousands of records in one go. Instead, break down the affected records into smaller batches and wrap every batch with a transaction.

    By minimizing the affected records per UPDATE and DELETE statement, it reduces the resources to be locked and it allows other users to have a chance to access it concurrently. This will improve the overall database response time.

  • The long run process must be broken down to many sub-processes and report the status back to the main process. All sub-processes must run within a stored procedure whenever possible (avoid using do some process in C# or any other programming language). This will ease the testing and future maintenance/enhancement by targeting on SQL server only.

    The way of the status returning back to the main process must be standardized. For example, if you have decided to use the OUTPUT varchar parameter for the processing status, then, all stored procedures must follow the same way.

  • The transaction scope must be efficient for the number of affected records.

    For example, you want to run a mail merge process. You may consider to have a transaction for every 1,000 records.

    Another example is that if you are running a billing process, you may consider having a transaction for each bill if each bill has a very complex process. Otehrwwise, you may consider having a transaction for every 100 records.

  This requires to do many testing in the development or simulated live environment to confirm what is the appropriate numbers. In real life, the hardware specification, number of concurrent users, the number of records in the output, etc must be put into consideration.

• After you have done the above, you need a table to store the progress.

    CREATE TABLE tb_process_log (
        process_log_id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
        , sess_id UNIQUEIDENTIFIER
        , seq NVARCHAR(50) NULL
        , msg NVARCHAR(MAX) NULL
        , remarks NVARCHAR(255) NULL
        , created_on DATETIME NULL
        , created_by NVARCHAR(255) NOT NULL
    );
  

  Where

  • process_log_id - this is the primary key of the table which stores the value generated by NEWID() function.

  • sess_id - this field stores the session ID of the current process. For example, if two users are running the same process, then, they should have different session IDs. Again, the value is generated by NEWID() function.

  • seq - this field stores the sequence number of the log record or the current step in the process. For a complex process, assigning an appropriate sequence number is not an easy task especially if you are using this value for the troubleshooting process. For us, we use the 999.999 convention where the first three digits indicate the main block and the second three digits indicate the sub block.

    The process of calculating the overdue charges on an invoice can be very tricky. For example, the general rule is to charge 1.5% on those invoices that are due more than 30 days. With an exception to charge 1% if the customer has business with the company for more than 2 years and the total overdue balance is less than $10,000. In this case, you may named the seq like this:

    • 100.000 - has invoice overdue more than 30 days. This is the main block
    • 100.010 - has more than 2 years business and less than $10k overdue balance. Apply 1% overdue charges.
    • 100.090 - applies the general rule of 1.5% overdue charges.

  • msg - this field stores the message of progress. You may include the details of which condition has been trigger and the runtime value of some variables.

  • remarks - this is the special remarks set by the process and it is optional. For us, we save the stored procedure name into this field to ease the tracing process.

  • created_on - this is the current date/time when the log record is inserted.

  • created_by - this is the user ID who is executing the stored procedure.

• Here is how to handle the process logging,

  • For every process that starts, it should insert a log record to indicate that the stored procedure is starting to run. This is very important because the stored procedure might not even has a chance to run and crashes.

  • Before exiting any stored procedure, it should insert a log record to indicate that the exit is intentional. This is to ensure that the exit is by design or stop at where you are expecting.

  • Put the current process step details into the msg field. This allows you to find out what is the runtime value instead of looking for a way to reproduce the error or behavior. For example, "Issuing invoice to customer A (PK:123456)" and "Successfully issued an invoice #1234 (amounted $2,000.00)(PK:9876543) to customer A (PK:123456)." where "PK" means primary key.

    Ensure that the msg value contains sufficient information for you to troubleshoot. Don't confuse the message between the process log message and audit log message where the former is for the programmer or support team and the later is for the end user.

  • For the remarks field, we normally use it as the stored procedure name. But, you may use it any way you like.

  • Make sure that you have sufficient TRY..CATCH block within each stored procedure to track down the unexpected error and also perform cleanup.

• How to use the process log

  • To view the current progress, you may execute the following query. Since the long running process uses transactions and the tb_process_log might be locked, you need WITH (NOLOCK) query hint to read the uncommitted records.

      select top 10 *
      from tb_process_log with (nolock)
      order by created_on desc
    

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.