Expired Sessions are not being Deleted from the ASP.NET Session State Database
**UPDATE**
Based on comments from readers I had to revisit this topic and admittedly I was confusing two different topics! The ASP.Net Session Service and the State Session Service. I have updated the content in this article and welcome additional edits. If you see something wrong, then speak up!! Thanks again, John-Rock!
**UPDATE**
So if you recall from previous posts I tend to attempt doing least privileged setup and configuration of products. If you don’t recall, then go back and read some of my earlier posts. 
Basically when you try to minimize the security exposure by granting only the rights that are needed to accomplish a specific task you are mostly at the mercy of the software vendor for having properly documented what those necessary rights might be. In this case we are discussing SharePoint Server 2010 (this likely applies equally to Foundation, but I have not confirmed) and the provisioning of the State Service Application.
Health Alerts!
SP2010 has a health engine that alerts you to potential and real problems within your SharePoint farm. One such warning you may encounter is the one titled “Expired sessions are not being deleted form the ASP.NET Session State database”, and it is documented here.
Figure A: You may see a notification in Central Admin that is either red or yellow depending on the severity of the alerts.
Figure B: You can get to the health alerts either through the notification link in Figure A or through the “Review problems and solutions” link from the Monitoring section of Central Admin.
Figures C & D: Show the health rule as it has fired and the details.
At a recent customer visit we found this health warning and began investigating. Sure enough their SQL Server Agent service was not running. We set it to Automatic and started it, then allowed the rule to reanalyze and the warning went away. Obviously that is not the intended content of this post as it’s not very exciting. The content of this post is around my own personal lab where I found this same health rule warning. I took a quick look at SQL Server Agent and it was not running, but after starting it I noticed that the required SQL Agent job –DeleteExpiredSessions- did not exist.
The Missing SQL Agent Job
This got me wondering what might have happened to not allow this job to get created at provision time for the ASP.Net State Service. If you review the least privilege documentation for SharePoint Server setup and configuration it states that you only need to be a local Administrator on the server in question and that you need only dbcreator and securityadmin rights in SQL Server.
The problem is that these rights do not allow for the creation of SQL Agent jobs and the ASP.Net State Service needs to create one such job in order to not throw the above warning. Notice that I didn’t say it needs to create the job in order to work… we’ll get to that in a few minutes.
**The above edits are clarified information. I was confusing ASP.Net State Service and the SharePoint Session State Service**
The Fix
In order to correct this problem in a supported fashion, you will need to disable and then re-enable the ASP.Net State Service with the appropriate rights. Following the information here allows you to grant the SQLAgentOperatorRole to your setup account and map it to the MSDB database in order to allow it to create SQL Agent jobs. You may notice that after adding SQLAgentOperatorRole your login actually also includes SQLAgentReaderRole and SQLAgentUserRole; these are added by SQL Server automatically and (as far as I can tell) all of these SQLAgent* rights can be removed after re-creating the job.
Figure E: Under User Mapping, select the SQLAgent* roles for the user in the MSDB database.
After providing the correct rights for your account, you may then execute the following SharePoint Powershell cmdlets (from an elevated console):
Disable-SPSessionStateService Enable-SPSessionStateService –DefaultProvision
NOTE: There are many parameters that you may need to use for creating the objects in the correct SQL Server, but you should be able to figure that part out. My example simply uses the default parameters, but in reality you would *at least* want to use the –DatabaseName parameter so as to avoid the dreaded database guid naming convention.
After these have executed, then you should now see the proper SQL Agent job:
Figure F: SessionStateDb_Job_DeleteExpiredSessions SQL Agent job after re-enabling State Service.
NOTE: As you may suspect, the owner of the newly created job will be the account you used to execute the above powershell cmdlets.
At this point, if you allow the health rule to reanalyze, then it should remove itself and be resolved. So now that you have resolved the issue, let’s discuss what I meant earlier when I said you didn’t necessarily need to recreate the job in order for the State Service to work properly.
Do we really need the SQL Agent job?
**UPDATES – this entire section has been revamped to remove the inaccuracies about the sql agent job relationship to timer job. The SQL Agent job *is* needed**
What can be confusing is that there are two very similar services: State Service and ASP.Net Session State Service. There is a health rule –“The State Service Deleted Expired Sessions timer job is not enabled”- for the State Service to verify that its timer job –”State Service Delete Expired Sessions”- is enabled.
There is also a health rule for the ASP.Net Session State Service-“Expired sessions are not being deleted from the ASP.NET Session State database.”- that verifies its SQL Agent job is executing and removing expired sessions appropriately.
The SQL Agent job, <sessionstatedatabasename>_Job_DeleteExpiredSessions, that is scheduled by default to execute every minute, executes the stored procedure, dbo.DeleteExpiredSessions.