Basics of ASPSTATE Database in SQL Server for DBAs
It is common for DBAs to notice databases with name ASPSTATE on multiple SQL Server instances and most often or not these are very small in size, but are very busy with user activity. Most of the times, DBAs are not really concerned with this ASPSTATE database and not much aware of the purpose of this database and if there are any considerations as a DBA to be aware of this database. Below are some basics about the purpose of this ASPSTATE database and some points for SQL Server Database Administrators to have some knowledge about this database which may turn helpful in future.
What is ASP.Net Session State?
ASP.NET session state allows to store and retrieve values for a user as the user navigates ASP.NET pages in a Web application. HTTP is a stateless protocol. This means that a Web server treats each HTTP request for a page as an independent request. The server retains no knowledge of variable values that were used during previous requests. ASP.NET session state identifies requests from the same browser during a limited time window as a session, and provides a way to persist variable values for the duration of that session. By default, ASP.NET session state is enabled for all ASP.NET applications.
In order to configure an ASP.NET application for ASP session state management, we need to configure the web.config file of the web application. There are different modes that are available for use to store the session state information as mentioned below.
OFF – In this mode the ASP session state is turned off for the web application.
InProc – In this mode ASP sessions are kept in memory on the web server. This is the default behavior.
StateServer – In this mode the session is stored in a separate process called the ASP.NET state service.
SQL Server – In this mode the session state is stored in SQL Server database generally named as ASPSTATE.
Custom – Developers can write their own custom mechanism or any other way that is readily available for the session storage mechanism.
This ASPState database is created by developers using aspnet_regsql.exe utility that comes with the .NET Framework. There are various parameters available for this utility to specify the instance name and other settings like authentication mode to connect to SQL instance, etc. ASPState database is used to store the stored procedures and functions required for session state management and by default all tables which store session state information is created in tempdb which will be lost after restart of the SQL Server instance. To avoid missing session state information even after restart of the SQL Server instance, there are various parameters passed to aspnet_regsql.exe utility which will create/store the tables which hold session state information in ASPSTATE database itself.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.