HOW TO: Configure ASP.NET for Persistent SQL Server Session State Management (311209)

SUMMARY

This article includes script files that configure Microsoft SQL Server to support ASP.NET SQL Server mode session state management so that session data is persisted outside the tempdb database. This article includes information to download these alternative script files, which are named InstallPersistSqlState.sql and UninstallPersistSqlState.sql.

Note The downloadable scripts that are referenced in this article are only intended for use with the Microsoft .NET Framework 1.0. You must not use these scripts with the Microsoft .NET Framework 1.1 because the .NET Framework 1.1 does not support them. Use the persistent scripts that are supplied with the .NET Framework 1.1 instead when you develop applications by using the .NET Framework 1.1.

back to the top

Requirements

• Microsoft Windows XP, Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, or Microsoft Windows 2000 Advanced Server
• Microsoft .NET Framework
• Microsoft Internet Information Services (IIS)
• Microsoft SQL Server

back to the top

Description of the Technique

When you use the original SQL Server mode session state management script files (InstallSqlState.sql and UninstallSqlState.sql), the ASPStateTempApplications and the ASPStateTempSessions tables are created in the tempdb database to store the session data. However, the session state data is lost if you restart the computer that is running SQL Server.

The persisting versions of the scripts (InstallPersistSqlState.sql and UninstallPersistSqlState.sql) resolve this problem by creating these tables in the ASPState database instead. Therefore, the session data is retained after you restart the computer that is running SQL Server.

To obtain the InstallPersistSqlState.sql and the UninstallPersistSqlState.sql files, download the PersistSqlState.exe self-extracting executable file. The following file is available for download from the Microsoft Download Center:
Release Date: April 23, 2002

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file. back to the top

Configure Persistent SQL Server Mode Session State Management

To run the InstallPersistSqlState.sql script file that configures persisting SQL Server mode session state management, follow these steps:

1. If you previously used the original InstallSqlState.sql file to configure SQL Server mode session state, you must run the original UninstallSqlState.sql file to remove SQL Server mode session state.
2. In SQL Query Analyzer, click Open on the File menu.
3. In the Open Query File dialog box, click InstallPersistSqlState.sql, and then click Open.
4. Click Execute on the Query menu to execute the script file.

back to the top

Remove the Persistent SQL Server Mode Session State Management Configuration

To run the UninstallPersistSqlState.sql script file that removes the persisting SQL Server mode session state management configuration, follow these steps:

1. In SQL Query Analyzer, click Open on the File menu.
2. In the Open Query File dialog box, click UninstallPersistSqlState.sql, and then click Open.
3. You must stop the w3svc service before you execute the UninstallPersistSqlState.sql script. To stop the w3svc service, follow these steps:
   1. Click Start, and then click Run.
   2. Type cmd in the Open box, and then click OK.
   3. At the command prompt, type net stop w3svc. You receive a confirmation that the w3svc service has been stopped.
4. In SQL Query Analyzer, click Execute on the Query menu to execute the script file.
5. Restart the w3svc service after the uninstall is complete. To do this, type net start w3svc at a command prompt.

back to the top

Troubleshooting

• If you do not stop the w3svc service before you execute the UninstallPersistSqlState.sql script, you may receive the following error message:
  Cannot drop the database 'ASPState' because it is currently in use
• If you notice that the entries in the ASPStateTempSessions table are not removed after the related sessions have exceeded their expiration, make sure that SQL Server Agent is running. This functionality is implemented through stored procedures that are scheduled through jobs in SQL Server. SQL Server Agent manages these jobs.

back to the top