Who's Online Application
page 4 of 7
by Alex Campbell
Feedback
Average Rating: 
Views (Total / Last 10 Days): 48269/ 105

Cleaning Up

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.


View Entire Article

User Comments

Title: Session.SessionID.ToString());   
Name: Spyros
Date: 2011-10-26 3:19:28 PM
Comment:
Seems not to work on asp.net 3.5 .
In global asa Session.SessionID.ToString()); is null.
Any idea ?
Title: Download links are broken   
Name: Linu
Date: 2009-12-21 1:33:33 AM
Comment:
The download link is broken, please fix it!
Title: I think it is possible to overcome the database hit issue   
Name: Yasser
Date: 2009-11-16 12:56:51 PM
Comment:
You're right, there is no point in doing a trip to the database on every request. As information about active users is useless once their session expires, I think it is a silly idea to store this information in the database (unless some kind of user online history needs to be stored). One alternative is to keep all logged in users in memory and do an in-memory lookup rather than a database lookup. The cleanup tasks are also performed on the online users collection object in memory.
Title: Bad idea ?!!! are you kidding me   
Name: Vex
Date: 2008-11-09 3:51:31 PM
Comment:
When is the last time you checked out myspace.com and facebook.com . People want to know which ones of their friends are online and they want to chat to them. that's one of the main interactive features of any social network website.
Title: Why this could be useful   
Name: Sharky
Date: 2008-01-19 11:31:05 PM
Comment:
one use is to see who is online to engage in an online chat. I think MySpace does this. There is a built in ASP.Net 2.0 function that shows you the number of users online but no built in query to show you who they are.
Title: Code names mixed   
Name: Frank
Date: 2007-09-17 8:16:37 AM
Comment:
Just an FYI. The links for the zip files are backwards. CS is under VB and vice versa.

Great info tho, just what my customer wants. Same as David Allen said, small site (100 users tops) and the admin's want to be able to see who's logging in as well as who's sharing their logins.

I had no idea how this would be done and you've knocked off a lot of time figuring it out on my own.

Thanks,
Frank
Title: Why this is a good idea   
Name: David Allen
Date: 2005-08-02 10:43:21 PM
Comment:
Your concerns about performance impact are legitimate; I share them.
But in enterprise applications (not high-volume, public applications), where users are known and from a limited pool of users, and you support them, then this can be very handy.
Respectfully,
davidkallen at yahoo dot com (email munged to prevent spam)






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-11-20 7:11:06 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search