RAPID PUBLISHING ARTICLES PROVIDE INFORMATION DIRECTLY FROM WITHIN THE MICROSOFT SUPPORT ORGANIZATION. THE INFORMATION CONTAINED HEREIN IS CREATED IN RESPONSE TO EMERGING OR UNIQUE TOPICS, OR IS INTENDED SUPPLEMENT OTHER KNOWLEDGE BASE INFORMATION.
The ASPStateTempSessions table in the Shared Services Database grows in size considerably.
The SQL Agent Job to clean the ASPStateTempSessions table is missing or the SQL Agent services is stopped.
To resolve this issue, follow these steps:
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 we 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 SQL Server Management Studio --> 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 (in my file, line numbers 1518 and 1604, respectively):
/* 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.
Please note: This job runs every minute.
If the SQL Agent Services is not started and the SQL Agent Job <SSPdatabaseName>_Job_DeleteExpiredSessions is missing, the table can grow considerably.
MICROSOFT AND/OR ITS SUPPLIERS MAKE NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY, RELIABILITY OR ACCURACY OF THE INFORMATION CONTAINED IN THE DOCUMENTS AND RELATED GRAPHICS PUBLISHED ON THIS WEBSITE (THE “MATERIALSâ€) FOR ANY PURPOSE. THE MATERIALS MAY INCLUDE TECHNICAL INACCURACIES OR TYPOGRAPHICAL ERRORS AND MAY BE REVISED AT ANY TIME WITHOUT NOTICE.
TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, MICROSOFT AND/OR ITS SUPPLIERS DISCLAIM AND EXCLUDE ALL REPRESENTATIONS, WARRANTIES, AND CONDITIONS WHETHER EXPRESS, IMPLIED OR STATUTORY, INCLUDING BUT NOT LIMITED TO REPRESENTATIONS, WARRANTIES, OR CONDITIONS OF TITLE, NON INFRINGEMENT, SATISFACTORY CONDITION OR QUALITY, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, WITH RESPECT TO THE MATERIALS.