SharePoint 2010: Expired sessions are not being deleted from the ASP.NET Session State database

While there are several things that can cause this warning to appear from the Health Analyzer in Central Administration, I'm assuming you've gotten past the obvious issues - such as making sure the SQL Server Agent is running, as this fantastic TechNet article states. If you're running SQL Express, you're just plain out of luck.

As the warning states, the issue revolves around a SQL job that is supposed to delete expired sessions from the ASP.NET session state database, which is probably missing if you're getting this warning. There are lots of articles out there that describe how to resolve the issue, but I want to share links to a few that I think provide the best information to the cause and resolution.

As for the cause of the issue, we go to a blog post from Bram Nuyts, Enable-SPSessionStateService; Check your SQL permissions. Understanding that the Enable-SPSessionStateService command is trying to create a job under the SQL Server Agent, it's simply a matter of permissions. Following guidance from Microsoft, Initial deployment administrative and service accounts, your setup user account has securityadmin and dbcreator permissions in SQL. His article indicates that you need sysadmin permissions as well for the account that you're using to run Enable-SPSessionStateService. While sysadmin perimssions will do the trick, in reading about permissions for the SQL Server Agent on MSDN, it looks like SQLAgentUserRole should be enough. But I have yet to test that.

A few articles I read when trying to understand this warning seemed to confuse the SharePoint State Service with the SharePoint Server ASP.NET Session State Service. They both have expired sessions that need to be regularly deleted, but they operate differently, timer job vs. SQL job. For a great article on how the two services work, read More Information about Health Analyzer Rules talking about session expirations, by Microsoft PFE sowmyancs.

To resolve the problem, you have to manually create the job under the SQL Server Agent. Dave Pileggi posts steps to do that here. But if you read this forum thread, you'll find a post from Sean Douglas which states that the job must be created such that it meets a specific naming convention. Otherwise, you'll still get a warning from the Health Analyzer. That naming convention is: <state db name>_Job_DeleteExpiredSessions

What I didn't see in the articles I read was information about the schedule for the job. If you're wondering, when the job is created successfully by the Enable-SPSessionStateService command, it is scheduled to run every minute. Better yet, here's the actual SQL script to create the job as it should have been created to begin with. All I did was generate a create script from the job, then make a few tweaks so it's easier to run. Edit the lines towards the top with your session state database name (@SessionStateDB) and your farm account username (@FarmAccount).

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

DECLARE @SessionStateDB NVARCHAR(200)
DECLARE @FarmAccount NVARCHAR(200)
SET @SessionStateDB = N'Session_State_DB'
SET @FarmAccount = N'DOMAIN\farm_account'

DECLARE @jobName NVARCHAR(MAX), @stepName NVARCHAR(MAX), @scheduleName NVARCHAR(MAX), @scheduleUID UNIQUEIDENTIFIER
SET @jobName = @SessionStateDB + N'_Job_DeleteExpiredSessions'
SET @stepName = @SessionStateDB + N'_JobStep_DeleteExpiredSessions'
SET @scheduleName = @SessionStateDB + N'_JobSchedule_DeleteExpiredSessions'
SET @scheduleUID = NEWID()

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@jobName,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Deletes expired sessions from the session state database.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=@FarmAccount, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@stepName,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXECUTE DeleteExpiredSessions',
@database_name=@SessionStateDB,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@scheduleName,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20001016,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=@scheduleUID
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO