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
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
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.
