Click here to Skip to main content
13,045,285 members (80,194 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


8 bookmarked
Posted 16 May 2009

Friday Tip 04, Behind Sqlserver sesssions

, 16 May 2009
Rate this:
Please Sign up or sign in to vote.
ASP.Net developers have been using sessions for state management since released and on a daily bases in their applications. However most developers use sessions as its default mode moreover others don’t know about these modes at all.

ASP.Net developers have been using sessions for state management since released and on a daily bases in their applications. However most developers use sessions as its default mode moreover others don’t know about these modes at all. Today I’ll recap the different types of session states and discuss in details one important type “Sqlserver sessions” that would be necessary to know about when working on big applications that require web farm deployment. session state modes:

1- Inproc (default)

2- Out Of Proc / State Server

3- SqlServer

session states can be configured from web.config under sessionstate node as follows

 <span style="color: #ff0000;"> <system.web><br />
<sessionState mode="InProc"><br />
</sessionState><br />

There is more,

Sqlserver Session mode

Sqlserver mode for session although is not the fastest way to store sessions is sometimes an important when it comes to big applications that implement web farms since the INPROC mode won’t be approperiate, why? Imagine that you have a web farm composed of 2 servers A and B. A user had made a request to your web site and was directed to Server A, then that user made another request which was handled through Server B. In that case the INPROC mode is not approperiate since that user session will be held on one server while his request on another which can’t access his session info. hence the need for shared place to hold sessions.

Installing Sqlserver session DataBase is shipped with script that allow you to install the sessions Database. You can find this script in this directory Drive:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

Moreover 2 is shipped with a command line tool that allow you to install / uninstall that database with different options. Using this tool is more recommended by Microsoft than using the scripts which exist for backword compatibility.

To launch this toll,

From Start menu Open your visual studio 2005/ 2008 > Visual Studio tools > Visual Studio Command Prompt

The command window will appear, The command line tool is called Aspnet_Regsql so just write Aspnet_Reqsql -? and you will get the help for that tool to know all parameters you need to pass.

Note: This tool has many functionalities than just installing the session database. You can also use it to install membership, enable and disable sql cache dependancy. provide the required parameters and click enter to install the database. (the paranmeters include server, login credentials and the operation you want, Add / Remove the database).

Persist VS Temporary

You may have noticed that there are two sql scripts for session database in the .netframe work directory named InstallPersistSqlState, InstallSqlState.sql . So what is the difference ?! Sqlserver sessions can be temporary or persistent . The temporary sessions are sotored in the TempDB rather than in Aspstate database. On the other hand the persistant sessions exist in the AspState Database. The Temp one is faster when writing and reading sessions becauase operations are not logged but the session data lost if the sqlserver goes down for any reason while the persistant one keeps its data.

The Aspnet_regsql tool gives you the option to install whatever type you want by passing the approperiate value to the sstype parameter.

Now, What’s Behind ?

When you finish installing the database it creates 2 tables, Punch of stored procedures and one sql job.


For the first while the above 2 tables have no significant relation between each other but that’s not true. each application has an application Id and each application has multiple sessions but how this is maintained since no relation exist. If you noticed, the sessionsId in AspstateTempsessions is a 32 bytes of which the first 24 are for sessionId and the last 8 are the ApplicationId. However the value of the AppId in The AspstateTempApplications table is in Decimal Format where where in the AspstateTempSessions stored in HexaDecimal. You can try It yourself, Copy the AppId of The AspStateTempApplications Table and Convert It to Hexadecimal and compare it with last 8 characters of the SessionId in the AspstateTempSessions table.

Note: This is a bit of usefull information cause you can use it  to share sessions among multiple applications. You will simply make sure to use one static AppId for all appplications.

Stored Procedures

I won’t goto detail every single stored procedure However I can highlight important ones. It’s obvious to have insert and update procedures to insert and update existing sessions. for the delete the DeleteExpiredSessions stored procedure is used and I’ll discuss it when discussing the job below. another procedure that you will need to know about is the TempGetAppID which you will need to edit if you want to unify your applications IDs.


Only one sql job is created with the ASPSATE database. this job is responsible for deleting all expired sessions from the database.

This job consists of only one step that call the stored procedure DeleteExpiredSessions to clear the database from expired sessions. This stored procedure is simple, it just check the current DateTimeand delete all session rows that has its expires field exceeded that DateTime.

CREATE PROCEDURE [dbo].[DeleteExpiredSessions]
DECLARE @now datetime

DELETE [ASPState].dbo.ASPStateTempSessions
WHERE Expires < @now


Note: When using sqlserver mode for sessions all objects stored in sessions have to be serializable (that can be serialized and deserialized).

Although the need for developers to interact with the SessionState database is not actually a case but it’s powerful to know how it works and looks like for furthere complicated needs that business cases may put developers in.

Hope It was clear enough and added alittle bit to your knowledge.
This Post is on


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Bassem Fawzy
Web Developer TayaIt
Egypt Egypt
Senior Developer / Project Manager.

You may also be interested in...

Comments and Discussions

QuestionCan I get current ASP.Net session id in SQL Server. Pin
Sachin9926-May-09 11:02
memberSachin9926-May-09 11:02 
AnswerRe: Can I get current ASP.Net session id in SQL Server. Pin
Bassem Fawzy27-May-09 9:38
memberBassem Fawzy27-May-09 9:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170713.1 | Last Updated 16 May 2009
Article Copyright 2009 by Bassem Fawzy
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid