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.

Answer 2

As @RobertBeaubien writes the DeleteExpiredSessions procedure should handle this. The procedure should have been created for you already in the database by the aspnet_regsql tool when you created the session store database. There should be an SQL Agent job calling this every 60 seconds, maybe that has been accidentally disabled.

The ASP.NET 4.0 version of the procedure looks like this:

CREATE PROCEDURE dbo.DeleteExpiredSessions
AS
    SET NOCOUNT ON
    SET DEADLOCK_PRIORITY LOW 
    DECLARE @now datetime
    SET @now = GETUTCDATE() 
    CREATE TABLE #tblExpiredSessions 
    ( 
        SessionID nvarchar(88) NOT NULL PRIMARY KEY
    )
    INSERT #tblExpiredSessions (SessionID)
        SELECT SessionID
        FROM [ASPState].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
        WHERE Expires < @now
    IF @@ROWCOUNT <> 0 
    BEGIN 
        DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
        FOR SELECT SessionID FROM #tblExpiredSessions 
        DECLARE @SessionID nvarchar(88)
        OPEN ExpiredSessionCursor
        FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
        WHILE @@FETCH_STATUS = 0 
            BEGIN
                DELETE FROM [ASPState].dbo.ASPStateTempSessions WHERE
                    SessionID = @SessionID AND Expires < @now
                FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
            END
        CLOSE ExpiredSessionCursor
        DEALLOCATE ExpiredSessionCursor
    END 
    DROP TABLE #tblExpiredSessions
RETURN 0     
GO

See here for explanation of the procedure.

The stored procedures involved and the mechanics behind the the SQL Server session state provider is explained in this MSDN article.

Answer 3

My advice is to stay away from cursors, if you can, for performance reasons. I rewrote the DeleteExpiredSessions stored procedure using merge instead:

CREATE PROCEDURE dbo.DeleteExpiredSessions
AS
    SET NOCOUNT ON
    SET DEADLOCK_PRIORITY LOW 
    DECLARE @now datetime
    SET @now = GETUTCDATE() 

    CREATE TABLE #tblExpiredSessions 
    ( 
        SessionID nvarchar(88) NOT NULL PRIMARY KEY
    )

    INSERT #tblExpiredSessions (SessionID)
        SELECT SessionID
        FROM dbo.ASPStateTempSessions WITH (NOLOCK)
        WHERE Expires < @now

    MERGE dbo.ASPStateTempSessions AS [target]
    USING #tblExpiredSessions AS [source]

    ON ([target].[SessionID] = [source].[SessionID] AND Expires < @now)

    WHEN MATCHED
        THEN DELETE;

    DROP TABLE #tblExpiredSessions
RETURN 0     
GO