ASP.Net session size in SQL Server session database

Problem:
We need to calculate ASP.Net Session size in a SQL server session database

Solution:

Use the following query(shamelessly stolen from asp.net: checking session size in SQL Server ASPState DB) to check the size of the session state data.

SELECT 
    a.SessionId, 
    SUBSTRING(a.SessionId, 25, 8) AS AppIDHex, 
    b.AppId AS AppIDDec, 
    b.AppName, 
    DATALENGTH(a.SessionItemLong) AS SessionSize 
FROM 
    dbo.ASPStateTempSessions AS a 
    LEFT OUTER JOIN 
    dbo.ASPStateTempApplications AS b 
    ON 
    SUBSTRING(a.SessionId, 25, 8) = 
    SUBSTRING(sys.fn_varbintohexstr(CONVERT(VarBinary,b.AppId)), 3, 8) 
WHERE 
    (DATALENGTH(a.SessionItemLong) > 0) 
ORDER BY SessionSize DESC

You may also like

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.