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

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

  • Comments 3

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 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 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 Fix

In order to correct this problem in a supported fashion, you will need to disable and then re-enable the 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?

Basically with regards to the State Service and its database, SharePoint 2010 has the following artifacts:

  1. A health rule for the state service to detect if there are any expired sessions that are older than 5 minutes. However, by default, this health rule is scheduled to only run once every day.
  2. A timer job, State Service Delete Expired Sessions, that actually calls a stored procedure in the State Service database. This stored procedure we can tell by its name -dbo.DeleteExpiredSessions- is responsible for deleting expired sessions. The timer job, by default, is scheduled to run once every hour.
  3. A SQL Agent job, <sessionstatedatabasename>_Job_DeleteExpiredSessions, that is scheduled, by default, to execute every minute. This job also executes the same stored procedure, dbo.DeleteExpiredSessions.

 

So what I meant earlier by saying you don’t necessarily need to re-create this job, is that you can see the timer job should take care of expired sessions. The catch that I see is that it’s possible that the health rule fires at a time when there are older, expired sessions and thus providing the warning.

Leave a Comment
  • Please add 3 and 2 and type the answer here:
  • Post
  • Spot on Brian!! I am going to check this with my customers right away.

  • Very nice post and thanks for the information.

  • Very Helpful. Thanks.

Page 1 of 1 (3 items)