Rollover logging in database

We are currently working on an application (window service), which grabs alerts data from a third party web application and parses each alert and process its status, reasons, and source for different attributes and then creates tickets for support staff.

In this project we were logging all detail activities of parsing alerts into our database, which alert was raised, what was the source and IP Address and so on. Our service was grabbing data from that website after five minutes interval. There were nearly hundreds of alerts on each pass. So our log table was getting heavy each day.

We decided to implement rollover logging into this, so that previous logs could be discarded if not required. So we came up with following solution. In it we move yesterday’s log entries to a new table, and truncated current log table.

ALTER PROCEDURE [dbo].[LogEntry]
(
    @AlertID AS int,
    @Message AS varchar(1000)
)
AS

DECLARE @TableName AS nvarchar(200),
        @CurrentDate AS datetime,
        @PreviousDate AS datetime

SELECT
    @PreviousDate = getdate()-1,
    @CurrentDate = getdate()

SELECT
    @TableName = 'Logs_' + CONVERT(varchar, @PreviousDate, 112)

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@TableName) AND type IN (N'U' ) )
BEGIN
    EXEC ( 'SELECT * INTO ' + @TableName + ' FROM Logs' )
    TRUNCATE TABLE Logs
END 

INSERT INTO Logs
(
    AlertID,
    [Message],
    EntryDate
)
VALUES
(
    @AlertID,
    @Message,
    GETDATE()
)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.