SharePoint Session State; The Guest That Just Won’t Leave

  • Article

MOSS uses ASP.Net session state for Forms Services, Project Server, etc. SharePoint installs a PartitionResolver which redirects the ASP.NET session state module to look to the SSP DB for its stored procedures and tables for storage of session state. Within CA there is an option to turn off Session State (its on by default) however if you try to start an OOTB workflow we attempt to load an InfoPath form via forms services and you will get probably one of the most clear and understandable errors in SharePoint (I won’t spoil the surprise here, you will either have to try it out or trust me :-)) ASP.net session state is a binary serialized representation of the client’s session state along with metadata such as the expiry and created date/time. When session state enabled sessions are inserted into the table ASPStateTempSessions within the SSP DB for each SSP.

So what’s the problem? ASP.Net Session state uses a sliding expiry which by default should expire the session state object after 60 min of not hearing from the client for which the session was created. So while this all works fine the issue is that SharePoint never cleans up these session state records out of the ASPStateTempSessions table. The result is your SSP DB(s) may continue to grow in size.

 

The Fix

Fortunately the fix is trivial however does involve a few manual steps which I will outline here. The goal is to create a SQL Agent timer job which will run once a minute and clean up expired sessions by executing the stored procedure ‘DeleteExpiredSessions’

1. Ensure the SQL Server Agent (SQLSERVERAGENT) service is started and set to startup when the machine starts. At a minimum this service needs to run on all SQL servers/instances which host an SSP DB.

2. Change to the .net Framework directory where ASP.Net is located, eg %windir%\Microsoft.Net\Framework[64]\v2.0.50727.

3. From the framework director run the following command replacing [YOUR SSP DB NAME] with the name of your SSP DB: aspnet_regsql -sstype c –d [YOUR SSP DB NAME] -sqlexportonly fix.sql –ssadd

4. The command from step 3 instructs the aspnet_regsql utility to generate a sql script named ‘fix.sql’ which can be used to install the ASP.Net session state stored procedures, tables, and jobs. Since everything we need for ASP.net session state is already in the SSP DB and we don’t want to edit that DB directly or risk entering into the land of “Unsupported” we only need to execute a portion of this file. Open fix.sql into SQL management studio and locate the line which has the following text:  /* Create the job to delete expired sessions */  Highlight everything below this line to the end of the file. With this text highlighted execute the query (hit F5).

5. At this point you should be able to navigate to the SQL Agent service and see the job: [YOUR SSP DB NAME]_Job_DeleteExpiredSessions. As I mentioned previously this job will execute every one min and clean up expired sessions.

6. Repeat for each additional SSPs in the farm.