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

Question

So, we just started using ASP.NET sessions stored on the SQL server - they are pretty neat. However, since sessions now go through the processing of the application pool, this can create a problem if we deploy new code. Is there a way (with the exception of restarting the SQL server) to force all sessions (or at least all sessions for the application pool) to abandon this model?

Answer 1

Add this as a stored procedure to 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 the task to the SQL Server agent, which runs at regular intervals (my start-up works at midnight), which performs the next step in this database:

 dbo.DeleteExpiredSessions 

This ensures that when sessions expire, they leave, even if the process that created them does not exist. If you want to get rid of them all when you restart your process, just do a:

 DELETE [ASPState].dbo.ASPStatsTempSessions 

This will delete all sessions in the database.

Answer 2

As @RobertBeaubien writes, the DeleteExpiredSessions procedure should handle this. This procedure should have been created for you already in the database using the aspnet_regsql tool when creating the session store database. There must be an SQL agent job invoking this every 60 seconds, it may have been accidentally disabled.

The version of the ASP.NET 4.0 procedure is as follows:

 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 an explanation of the procedure.

The stored procedures and mechanics underlying the SQL Server Session State Provider are described in this MSDN article .

Answer 3

My advice is to stay away from cursors if possible for performance reasons. I rewrote the stored procedure DeleteExpiredSessions , 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