Expired Sessions are not being Deleted from the ASP.NET Session State Database

  • Article

**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. Smile

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.

SP2010_Health_Service_Issues_large

Figure A: You may see a notification in Central Admin that is either red or yellow depending on the severity of the alerts.

SP2010_Review_Health_Issues_large

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.

SP2010_State_Service_Health_Rule_Failed_large

SP2010_State_Service_Health_Rule_Details_large

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.

SQL2012_AgentRights

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:

SQL2012_AgentJob

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.

Comments

  • Anonymous
    September 11, 2013
    Spot on Brian!! I am going to check this with my customers right away.
  • Anonymous
    September 11, 2013
    Very nice post and thanks for the information.
  • Anonymous
    April 01, 2014
    Very Helpful. Thanks.
  • Anonymous
    June 04, 2014
    Great post thanks
  • Anonymous
    June 04, 2014
    Hi Brian,I was re-reading you post and was wondering something. At the begining of the post you state "In this case we are discussing SharePoint Server 2010 ... and the provisioning of the State Service Application." But later in the article when you give the PowerShell commands "Enable-SPSessionStateService –DefaultProvision", which is not the State Service Application, but is the ASP.NET session state Service.According to this article ( blogs.msdn.com/.../using-session-state-in-sharepoint-2010.aspx ) which describes the difference between the two state services, it says that the ASP.NET session state service is automatically disabled in normal installations of SharePoint 2010.Just wondering if you can clarify this?Thank you
  • Anonymous
    June 05, 2014
    Hey Brian,Thanks for the information. Great post and workaround. However, I checked and it seems we do need the Agent Job. I ran profiler on the SQL server and kicked off the Timer Job State Service Delete Expired Sessions timer job. Unfortunately, that only trigger remote procedure on the "State Service Database" and NOT on the "ASP.NET Session State Service" Database. As both databases are different, if the SQL Agent job is not run, then sessions from the ASP.NET database will not be deleted and the health check warning will not go away.Thanks again for the awesome information.
  • Anonymous
    June 11, 2014
    John & otgkahn:Thanks for the comments... I'm not ignoring you at the moment, but trying to find time to get back to this.. Let me re-research this and I'll post updates as appropriate. Thanks again!  It's why we have community!Brian
  • Anonymous
    July 16, 2014
    This article helped me to resolve the issue. Thanks a lot Brian.
  • Anonymous
    July 21, 2014
    John-Rock.... great catch! I totally misread the separation here ... stay tuned for an update to the article to clarify!
  • Anonymous
    August 28, 2014
    Despite of existing the related job on SSMS, I have this issue in my Health Analyser. also, the SQL Server Agent service is running. What's the problem then?!
  • Anonymous
    August 28, 2014
    Find the Solution!Although the "sessionstatedb_Job_DeleteExpiredSessions" job was enable on Sql Server Agent and also the service was running on Timer Job, I had this issue on health Analyzer. the solution was:Go to Sql Server Management studio and run the following script:use [sessionstatedb]select * from ASPStateTempSessions (it will return 1 or more records)exec DeleteExpiredSessionsselect * from ASPStateTempSessions (it will return 0 record)then back to issue on health analyzer and reAnalyse the issue for 1 or more time. It will disappear then.
  • Anonymous
    November 05, 2014
    The SessionStateDb_Job_DeleteExpiredSessions SQL Job is not supported on an AlwaysAvailibleGroup, about there is no check if the Node is the Primary or not.  On using Systemcenter you will get an error message and a Ticket all minute for each secondary Node!This needed a very urgent fix!
  • Anonymous
    January 07, 2015
    I was able to resolve this by simply changing the owner of the SQL Agent job from the user/service account to sa.
  • Anonymous
    January 07, 2015
    skategeezer, while that may work to resolve the issue I would not expect that to be a recommended practice as your sa account has a much higher level of privilege than what is needed. However, as with most recommendations they are all dependent on your own installation and needs. If you understand the risks and are comfortable with them, then run with it!! :)