How can I recycle ASP.NET sessions stored in SQL Server?

Question

So we've just started using ASP.NET sessions stored in SQL server - they're pretty neat. However, since sessions now last across an application pool recycle this could pose us a problem if we deploy new code. Is there a way (outside of re-starting the SQL server) to force all sessions (or at least all sessions for an application pool) to be abandoned in this model?

Answer 1

Add this as a stored procedure in the ASPState database:

        CREATE PROCEDURE [dbo].[DeleteExpiredSessions]
    AS
        DECLARE @now datetime
        SET @now = GETUTCDATE()

        DELETE [ASPState].dbo.ASPStateTempSessions
        WHERE Expires < @now

        RETURN 0

Then add a job to the SQL Server Agent that runs at a regular interval (mine runs at midnight) that executes the following step on that database:

dbo.DeleteExpiredSessions

That makes sure when the sessions expire, they go away even if the process that created them is not around. If you want to get rid of them all on a restart of your process, just execute a:

DELETE [ASPState].dbo.ASPStatsTempSessions

That will remove all sessions in the database.