Can we delete old records from aspstatetempsessions table?

Question

Recently I created Session State in my project, code is below

<sessionState mode="SQLServer" allowCustomSqlDatabase="true" 
    sqlConnectionString="Data Source=ADMIN-9F8C57749\SQLEXPRESS;Initial 
    Catalog=kecbliss;Integrated Security=True" timeout="60" 
    stateNetworkTimeout="60">
</sessionState>

The problem is day by day aspstatetempsessions table is becoming big so my question is

1. Can i delete previous days records from aspstatetempsessions?
2. How to achieve this without affecting to project ?

Project details

Front end ASP.NET
Back end MS SQL Server

Answer 1

To resolve this issue:

Step1:

Make sure that the SQL Agent service is running and also check to see if the SQL Agent job called SSPdatabaseName_Job_DeleteExpiredSessions exists and run successfully.

If you have the SQL Agent Services is started and the SQL Agent Job is missing, proceed to the Step2

Step2:

Run the DeleteExpiredSessions Stored Procedure manually on the SSP database.

• Connect to the database server using Microsoft SSMS --> Client on New query.
• Make sure you have selected the SSP database in the new query windows.
• Type in “EXEC DeleteExpiredSessions” and execute the query.

This command would clean up the ASPStateTempSessions table and not remove any active connections.

Note: Depending on the ASPStateTempSessions table size, it is recommended to run this command during off-peak times on the SharePoint/SQL server.

To register the job to be run automatically in the future follow the below steps:

• Ensure the SQL Agent is on (it should be set as an Automatic startup Windows service as well)
• Obtain the SSP database name that contains the ASPStateTempSessions table missing an associated SQL Agent Job.
• Execute the following command on a WFE or any application servers with the server farm:

From the command prompt get to the ASP .Net folder %WINDIR%\Microsoft.NET\Framework\v2.0.50727\

aspnet_regsql.exe -sqlexportonly exportfilepath.sql -ssadd -sstype c -d SSP db name 

• Open the exportfilepath.sql file in a note pad, make sure to select the text between the following lines:

/* Create the job to delete expired sessions */

and

/**********************************/

• Copy the above copied test and Execute the code on the SQL box hosting the SSP database.

Now you should find a SQL Agent Job is created.

Raj

Source: support.microsoft.com/kb/970788