Cleaning Up

If the Session_End event in the global.asax codebehind isn't fired when a session ends, that session will remain in the process forever.  To fix this, we have an SQL Server task scheduled to run the sp_CleanOldOnlineUsers stored procedure every day.

    CREATE PROCEDURE sp_CleanOldOnlineUsers
    /*
    Cleans out any sessions 1 day or more old - prevents the DB from being clogged
    up with old sessions. If ASP.Net managed to successfully raise the Session_End
    event every time a session timed out then this procedure would be redundant.
    */
    AS
    Delete From tbl_OnlineUsers
    Where DateDiff(Day, TimeStamp, GetDate()) > 0
    /* ie if the difference in days between now and the timestamp
    is 1 or more, delete it*/

The following code creates the SQL Server job that cleans out the database every night at midnight:

exec msdb.dbo.sp_add_job
        @job_name = 'OnlineUsers',
        @description = 'Cleanup routine for Online Users',
        @category_name = '[Uncategorized (Local)]',
        @enabled = 1,
        @notify_level_email = 0,
        @notify_level_page = 0,
        @notify_level_netsend = 0,
        @notify_level_eventlog = 2,
        @delete_level= 0
exec msdb.dbo.sp_add_jobstep
        @job_name = 'OnlineUsers',
        @step_id = 1,
        @step_name = 'Remove any session older than 1 day',
        @command = 'sp_CleanOldOnlineUsers',
        @retry_attempts = 5,
        @retry_interval = 5
exec msdb.dbo.sp_update_job
        @job_name = 'OnlineUsers',
        @start_step_id = 1

exec msdb.dbo.sp_add_jobschedule
        @job_name = 'OnlineUsers',
        @name = 'DailyCleanup',
        @enabled = 1,
        @freq_type = 4,
        @freq_interval = 1
       
exec msdb.dbo.sp_add_jobserver
        @job_name = 'OnlineUsers',
        @server_name = '(local)'

If you have any difficulty with this job creation code, you might be running an earlier version of SQL Server (earlier than 2000).  As always, the SQL Server Books Online is extremely helpful.