How to fix: Expired Sessions are not being deleted

You are sitting there minding your own business thinking life is great.  You just installed a SharePoint 2010 environment and WHAM! You have the indentation of a bus license plate on your forehead.  What was that?!?  You just opened up your SharePoint Health Analyzer and see the error. (See Figure 1) Expired sessions are not being deleted from the ASP.NET Session State database.  Fight the urge to play “It’s the End of the World” by R.E.M. and crawling under your desk repeating “duck and cover.”  This can be fixed, and without too much pain. Really!

Figure 1

Ok, go to your SQL Server and open up SQL Server Management Studio.  You will need to have SQL Server Admin rights. (I believe.  My SQL Admin days are a faint memory in my mind.  I know enough to be dangerous.) Look for SQL Server Agent and expand that.  There you will find Jobs.  Expand Jobs and see if you have a DeleteExpiredSessions job registered or not. (See Figure 2)  If not you will need to fix this.

Figure 2

We need to create this job so we can save the world. (Mwaa haaa haaa) Right click the Jobs folder in SQL Server Management Studio and Select New Job.  On the General configurations page, Enter the Name, who you want the owner to be, category if needed, and a description. (See Figure 3) NOTE:  The SharePoint health checker is looking for a specific name. DeleteExpiredSessions should be the name you use.  Or change the rule to the name that you select.

Figure 3

On the select a page on the right hand side click on Steps next.  At the bottom you will see the New button.  This will allow you to build a new step.  From here give the Step a name, use Transact-SQL script (T-SQL), Select your StateService_<GUiD> DB and put in your SQL statement in. (See Figure 4)  Click Parse to make sure your SQL statement is correct. Click OK.

Figure 4

On the select a page on the right hand side click on Schedules.  At the bottom of the new window you will see the New button.  This will allow you to build a schedule.  Give the schedule a name, and time(s) that work for your corporation. (See Figure 5) Click OK.

Figure 5

Fill out alerts, notifications and/or targets if needed then hit OK.  You will see your job appear in the job list. If you wish, you can execute the job you just created by right clicking on it and selecting Execute Stored Procedure.