How to calculate Session data size for SQL Server session mode?

By | December 27, 2010

SQL Server use ASPState database to store the session information for SQL Server Session mode. ASPState database having two tables ASPStateTempApplications and ASPStateTempSessions. ASPStateTempApplications  table contains Application ID and Application Name which is specific to each and every application which are using SQL Server session mode on the particular database. ASPStateTempSessions table having numbers of fields to store the session related information which includes [SessionId], [Created], [Expires], [Timeout]  etc. [SessionItemShort] and [SessionItemLong] actually contains the session data for every users. If the session data size is < = 7000 KB it will be stored in SessionItemShort field and anything > 7000 KB will be stored in SessionItemLong field

image

Once you have some session data stored inside ASPStateTempSessions  table, you can easily get the size of session data by running below SQL Query

use ASPState

select [sessionid],[created], datalength(SessionItemLong) as SessionDataSize from ASPStateTempSessions

image

Note : While running SQL query, you have to make sure you are calculating the size of proper field, if the session data size may vary for different user then you can try to find the data size for both  SessionItemLong and SessionItemShort field.