Welcome to ciysys blog

You need an application log for your systems

Published on: 24th Jul 2012

Updated on: 17th May 2021

Overview

All systems require a common area to keep track of certain information. For example,

Our approach

The table that keeps the application log,

CREATE TABLE tb_log (
    log_id bigint NOT NULL,             --primary key
    log_type_id INT NOT NULL,           --type of log.
    workstation NVARCHAR(255) NOT NULL, --the workstation name
    uid NVARCHAR(255) NOT NULL,         --the current user ID.
    msg NVARCHAR(max) NULL,             --the details of the log
    remarks NVARCHAR(255) NULL,         --the remarks/procedure name.
    is_sent INT NOT NULL,               --if '1', means the log has been sent.
    app_id INT NOT NULL,                --the application ID
    module_id INT NOT NULL,             --the module ID
    created_on DATETIME NOT NULL,       --this is the current date/time when the log record is inserted.
    created_by NVARCHAR(255) NOT NULL   --the current user ID while creating this record.
);

The allowed values for the log_type_id field:

Add-on module to be build on top of this log table:

On sending notification, please refers to this article: design a custom mail process

Cleanup the application log

The application log will grow every second and it will become irrelevant after a few months. So, you may want to have a scheduled task to perform the cleanup periodically.

This can be done by with the following stored procedure which keeps the latest 100k records:

create proc mgt_cleanup_log_table
as
begin
    declare
        @min bigint
        , @max bigint

    select
        @min= isnull(min(log_id), 0),
        @max = isnull(max(log_id), 0)
    from tb_log

    -- leave 100k records in the log table
    set @max = @max - 100000

    while @min < @max
    begin
        select @min = @min + 1000
        --print 'Deleting log_id < ' + cast(@min as varchar)

        delete from tb_log
        where log_id < @min + 1000
    end
end
go

Jump to #MSSQL blog

Author

Lau Hon Wan - the developer and business owner.