How to run periodic processes on SQL Server 2008

Question

I've built a very simple chatroom-like ASP.NET application which displays current Online/Offline users:

I have a Table with a DateTime column used as a TimeStamp. Every time a user causes a Postback or similar Get event, I update the TimeStamp. I want to, on the server, create a periodic process of some sort that I can use to check how long a user has been inactive given that I know the last time they were active. Once they have been deemed inactive (after a few minutes lets say), I want to set the value of another column to mark them as "Offline".

Any suggestions would be appreciated.

Answer 1

You could create a Sql Server Agent Job that runs periodically.

http://msdn.microsoft.com/en-us/library/ms187910.aspx

http://msdn.microsoft.com/en-us/library/ms186273.aspx

Answer 2

While the answer given above for using SQL Server Agent is a very good solution, have you considered having a component (a WCF component maybe) on the server which is doing all the state management, instead of managing the state in the database directly?

Answer 3

We do something similar by adding a simple webservice to the Asp.Net application, and calling it at a configurable time interval from a windows service running on the same web-server. This allows us to use our business logic, while keeping it all in one place - the web application.