SQL Session state - Database SessionId different to ASP.NET_SessionID value

Question

I am using SQL Server 2008 to store the sessions for my .NET4 application. The sessions will be stored in [DatabaseA]. There isn't any other custom configuration for this, so the ASPState database is exactly how it would come out of the box (using aspnet_regsql)

My main application runs on [DatabaseB] (same server). Within this database I have a 2 tables that record a some data along with the sessionID.

When the [DeleteExpiredSessions] stored procedure (on DatabaseA) is run via SQL Agent, the sessions are correctly removed from the ASPState, but I want to extend this to delete the rows based on the SessionID from [DatabaseB]

I have tried editing the [DeleteExpiredSessions] stored procedure to include the following SQL

    OPEN ExpiredSessionCursor

    FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID

    WHILE @@FETCH_STATUS = 0 
        BEGIN
            -- BEGIN MY ADDITIONS
            DECLARE @myRecordCount int
            SELECT @myRecordCount= COUNT(*) FROM [DatabaseB].dbo.Table1 WHERE [DatabaseB].dbo.Table1.SessionId = @SessionID -- AND [DatabaseB].dbo.Table1.DateEntered < @now
            SELECT @myRecordCount 
            DELETE FROM [DatabaseB].dbo.Table1 WHERE [DatabaseB].dbo.Table1.SessionId = @SessionID AND [DatabaseB].dbo.Table1.DateEntered < @now
            DELETE FROM [DatabaseB].dbo.Table2 WHERE [DatabaseB].dbo.Table2.SessionId = @SessionID AND [DatabaseB].dbo.Table2.DateEntered < @now
            -- END MY ADDITIONS

            DELETE FROM [DatabaseA].dbo.ASPStateTempSessions WHERE SessionID = @SessionID AND Expires < @now
            FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
        END

    CLOSE ExpiredSessionCursor

    DEALLOCATE ExpiredSessionCursor

But @myRecordCount is returning 0 rows. There are no errors reported (the agent job runs correctly, and nothing in SQL Profiler), and @myRecordCount should be returning 4 in this instance.

The DECLARE/SELECT COUNT is there as a debugger.

UPDATE

so having debugged the sql and found that :

SELECT SessionId
        FROM [ASPState].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
        WHERE Expires < GETUTCDATE() 
--Returns SessionId = '3wj5nyrlz02ezw1vvjts4gjv28d8c075'

SELECT * FROM [DatabaseB].dbo.Table1 -- returns (4) results
SELECT * FROM [DatabaseB].dbo.Table1 WHERE [DatabaseB].dbo.Table1.SessionId = '3wj5nyrlz02ezw1vvjts4gjv28d8c075' -- returns (0) results

I have deduced that the SessionId is wrong. What is being stored in [DatabaseB].dbo.Table1 is '3wj5nyrlz02ezw1vvjts4gjv' - notice the truncation of the string.

Now my .NET code (using EF6.1.3) for storing the session variables are Table1.SessionId = HttpContext.Current.Session.SessionID this means that 3wj5nyrlz02ezw1vvjts4gjv is being stored. The Cookie ASP.NET_SessionId also has the same value.

The length of the SessionId column of Table1 is the same as the ASPState tmpSession table (88)

Updated Question Interestingly the ASPStateTempSessions.SessionId variable seems to be appending
28d8c075 to the end of it. So ASP.NET_SessionId and HttpContext.Current.Session.SessionID are 3wj5nyrlz02ezw1vvjts4gjv but ASPStateTempSessions.SessionId is 3wj5nyrlz02ezw1vvjts4gjv28d8c075

I have just cleared all session variables and cookies and I have a new session variable, but the 28d8c075 is still being removed/appended to the various cookies and data values.

I understand this is happening because the ASPStateTempSessions is appending a suffix of the application hash to the SessionId (https://msdn.microsoft.com/en-us/library/aa478952.aspx)

Column Name Column Type Description
SessionId   nvarchar(88)    Session ID + application ID

How do I return this to HttpContext.Current.Session.SessionID instead of just the SessionId?

Answer 1

Solved

The issue (as I originally diagnosed) was that the application ID wasn't being passed to the SessionID variable. So I did the following steps:

1) Created a fallback variable within Web.Config (it is very unlikely that the AppName is going to change)

<add key="AppId" value="685293685"/>

Why? - this is the id returned from the ASPState [TempGetAppID] for the appName

2) Created a stored procedure [GetApplicationIdForSession] within DatabaseB

DECLARE @appId int

    EXEC    [ASPState].dbo.[TempGetAppID]
        @appName = @iisName,
        @appId = @appId OUTPUT

    SELECT  @appId as N'AppId'

Why? - This utilises the built in SQL State SP that hashes the AppName to the correct AppId to maintain consistency. (i.e. i'm not writing a separate .net function to try and get the same value as SQL)

3) Within Global.asax.vb

Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
        Using db As New SqlConnection(ConfigurationManager.ConnectionStrings("LocalServer").ConnectionString)
            db.Open()
            Using cmd As SqlCommand = db.CreateCommand()
                cmd.CommandText = "GetApplicationIdForSession"
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("iisName", HttpRuntime.AppDomainAppId.ToLower)
                Using dr As SqlDataReader = cmd.ExecuteReader()
                    If dr IsNot Nothing AndAlso dr.Read() Then
                        Application("AppId") = CType(dr("appId"), Integer)
                    Else
                        Application("AppId") = CType(ConfigurationManager.AppSettings("AppId"), Integer)
                    End If
                End Using
            End Using
            db.Close()
        End Using
        ' Fires when the application is started
    End Sub

Why? - The HttpRuntime.AppDomainAppId is the same value that is used within the ASPState Database (DatabaseA). so I pass that into the stored procedure created in step 2 and then store the output in an application variable so I don't have to hit the database everytime to get the sessionid.

4) Created this function in the base class that my pages inherit from

Public Function GetAppIdHash() As String

        Dim hashCode As Integer = CType(Application("AppId"), Integer)
        Return ((hashCode).ToString("X2")).ToLower

    End Function

Why? - this takes the application variable ("AppId") and returns the Hex output

5) Altered the .net code that stores the Session id variable from: Table1.SessionId = HttpContext.Current.Session.SessionID to Table1.SessionId = HttpContext.Current.Session.SessionID & GetAppIdHash()

6) the DeleteExpiredSessions SP is updated to the -happy medium- version listed here - http://sqlperformance.com/2013/01/t-sql-queries/optimize-aspstate and the additional SQL commands are appended to it.

ALTER PROCEDURE [dbo].[DeleteExpiredSessions]
  @top INT = 1000
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @now DATETIME, @c INT;
  SELECT @now = GETUTCDATE(), @c = 1;

 /* START Additional SQL */
    CREATE TABLE #tblExpiredSessions 
    ( 
        SessionId nvarchar(88) NOT NULL PRIMARY KEY
    )
    INSERT #tblExpiredSessions (SessionId)
        SELECT SessionId
        FROM [ASPState].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
        WHERE Expires < @now
    /* END Additional SQL */

  BEGIN TRANSACTION;

  WHILE @c <> 0
  BEGIN
    ;WITH x AS 
    (
      SELECT TOP (@top) SessionId
        FROM [ASPState].dbo.ASPStateTempSessions
        WHERE Expires < @now
        ORDER BY SessionId
    )
    DELETE x;

    SET @c = @@ROWCOUNT;

    IF @@TRANCOUNT = 1
    BEGIN
      COMMIT TRANSACTION;
      BEGIN TRANSACTION;
    END
  END

  IF @@TRANCOUNT = 1
  BEGIN
    COMMIT TRANSACTION;
  END

  /* START Additional SQL */
  DELETE FROM DatabaseB.dbo.Table1 WHERE DatabaseB.dbo.Table2.SessionId in (SELECT * FROM #tblExpiredSessions)
  DELETE FROM DatabaseB.dbo.Table2 WHERE DatabaseB.dbo.Table2.SessionId in (SELECT * FROM #tblExpiredSessions)

  DROP TABLE #tblExpiredSessions
  /* END Additional SQL */
END

Sidenote - I'm not an SQL wizard so if anyone could suggest improvements to the DELETE FROM DatabaseB.dbo.Table1 etc part, that would be appreciated. (such as location within the query, can it work with the CTE), it currently seems kind of clunky to me.

Answer 2

My experience of having multiple session state databases are that SessionID is unique between applications. We've had to customise the code to get them to share one session state database and change the behaviour based on application name.

Answer 3

why you need cusrors ,assuming you have session id common between two tables,you can try below

delete t1
 from database1.table1 t1
join
database2.table2 t2
on t1.sessionid=t2.sessionid