Sessions Lost With SqlServer

[joeriggs]

Having trouble with an application which is using SQL Server 2000 to store session state. The App is ASP.NET 2.0 on Win2003.

Occasionally all sessions are lost at once, causing the app to break.

I know there are issues with InProc sessions relating to antivirus products, directory deletes, config changes .... and had the problem InProc before, but now I'm using SQL Server it should be persistent.

If I do an IIS reset the sessions magically come back. When InProc before they were lost permenantly.

Any suggestions ?

[joeriggs]

By the way I am using a web garden

[Frinavale]

By the way I am using a web garden

I am not familiar with web gardens so I took some time to look up why you might be encountering these issues.

I found a very informative conversation posted and found the following quite interesting. It explains why you would be having problems with inProc Session storage while using a web garden:

If you have code that relies on sessions, and you enable the web garden feature (set max processes to > 1 for an app pool), you will experience this "curious" "intermitte nt" loss of sessions (or "unexplaina ble logouts").

The problem isn't work process recycling, nor even application domain restarts (other problems that can kill sessions or force logouts). Instead, it's just that the web garden feature can cause your requests to eventually be directed to a new worker process where the session you had is not existing.

Bottom line: if using sessions and you enable web gardens, you had better enable StateServer or SQLServer for session management.

Since you're using SQLServer to store your session state you shouldn't be experiencing these problems.

Have you made sure that your Application Pool settings are correct?
Have you determined the scenario when the Sessions are lost?

-Frinny

[joeriggs]

Hi, yes there are a number of problems with In Proc. I have moved to SQL Server sessions to avoid these but still lose all sessions at once a couple of times a day. When using In Proc they were gone for good, but with SQL Server 200 backed sessions they come back with an IIS reset.

[joeriggs]

My full web.config is below incase that will help:

Part 1

<?xml version="1.0"?> 
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
    <configSections>
        <sectionGroup name="system.web.extensions" type="System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
            <sectionGroup name="scripting" type="System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
                <section name="scriptResourceHandler" type="System.Web.Configuration.ScriptingScriptResourceHandlerSection, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="false" allowDefinition="MachineToApplication"/>
                <sectionGroup name="webServices" type="System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
                    <section name="jsonSerialization" type="System.Web.Configuration.ScriptingJsonSerializationSection, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="false" allowDefinition="Everywhere"/>
                    <section name="profileService" type="System.Web.Configuration.ScriptingProfileServiceSection, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="false" allowDefinition="MachineToApplication"/>
                    <section name="authenticationService" type="System.Web.Configuration.ScriptingAuthenticationServiceSection, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="false" allowDefinition="MachineToApplication"/>
                </sectionGroup>
            </sectionGroup>
        </sectionGroup>
        <section name="XXXXX.Data" type="XXXXX.Data.Bases.NetTiersServiceSection, XXXXX.Data" allowDefinition="MachineToApplication" restartOnExternalChanges="true"/>
        <section name="enterpriseLibrary.ConfigurationSource" type="Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ConfigurationSourceSection, Microsoft.Practices.EnterpriseLibrary.Common, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null"/>
    </configSections>
    <XXXXX.Data defaultProvider="SqlNetTiersProvider">
        <providers>
            <!--
                *** SqlClient Provider ***
                    connectionStringName: sqlclient connection string to the db
                    useStoredProcedure: if true, indicates that we use the stored procedures, otherwise, we use parametrized queries that are embedded.
            -->
            <add name="SqlNetTiersProvider" type="XXXXX.Data.SqlClient.SqlNetTiersProvider, XXXXX.Data.SqlClient" connectionStringName="LocalSqlServer" providerInvariantName="System.Data.SqlClient" entityFactoryType="XXXXX.Entities.EntityFactory" useEntityFactory="true" enableEntityTracking="true" enableMethodAuthorization="false" useStoredProcedure="false"/>
            <!-- 
                *** WebserviceClient Provider ***
                The url parameter indicates the webservices url (ex: http://localhost/XXXXX/XXXXXServices.aspx)
                <add 
                    name="WsNetTiersProvider" 
                    type="XXXXX.Data.WebServiceClient.WsNetTiersProvider, XXXXX.Data.WebServiceClient"
                    url="/XXXXXServices.asmx" />
            -->
        </providers>
    </XXXXX.Data>
    <enterpriseLibrary.ConfigurationSource selectedSource="File Configuration Source">
        <sources>
            <add name="File Configuration Source" type="Microsoft.Practices.EnterpriseLibrary.Common.Configuration.FileConfigurationSource, Microsoft.Practices.EnterpriseLibrary.Common, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null" filePath="entlib.config"/>
        </sources>
    </enterpriseLibrary.ConfigurationSource>
    <appSettings/>
    <connectionStrings>
  <clear />
  <add name="LocalSqlServer" connectionString="data source=Bintang\Bintang2000;Initial Catalog=XXXXX2;user id=XXXXXAdmin;password=password;persist security info=true;packet size=4096"
   providerName="System.Data.SqlClient" />
 </connectionStrings>
    <system.web>
        <!-- SessionState -->
        <customErrors mode="Off" />
  <sessionState sqlConnectionString="data source=YYYYYServer; user id=XXXXXAdmin;password=$car1ito!!;persist security info=true;packet size=4096" />
        <roleManager enabled="true" defaultProvider="CustomizedRoleProvider">
   <providers>
    <add connectionStringName="LocalSqlServer" applicationName="/"
     name="CustomizedRoleProvider" type="XXXXXRoleProvider" />
   </providers>
  </roleManager>
        <membership defaultProvider="AspNetSqlProvider">
   <providers>
    <clear />
    <add connectionStringName="LocalSqlServer" enablePasswordReset="true"
     requiresQuestionAndAnswer="false" requiresUniqueEmail="false"
     passwordFormat="Clear" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="5"
     minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10"
     passwordStrengthRegularExpression="" applicationName="/" name="AspNetSqlProvider"
     type="XXXXXMembershipProvider" />
   </providers>
  </membership>
        <pages pageBaseType="WebPageBase">
            <controls>
                <add tagPrefix="ajaxToolkit" namespXXXXX="AjaxControlToolkit" assembly="AjaxControlToolkit"/>
                <add tagPrefix="asp" namespXXXXX="System.Web.UI" assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
                <add tagPrefix="asp" namespXXXXX="Microsoft.Web.Preview.UI" assembly="Microsoft.Web.Preview"/>
                <add tagPrefix="asp" namespXXXXX="Microsoft.Web.Preview.UI.Controls" assembly="Microsoft.Web.Preview"/>
                <add tagPrefix="data" namespXXXXX="XXXXX.Web.Data" assembly="XXXXX.Web"/>
                <add tagPrefix="data" namespXXXXX="XXXXX.Web.UI" assembly="XXXXX.Web"/>
            </controls>
            <tagMapping>
                <add tagType="System.Web.UI.WebControls.CompareValidator" mappedTagType="Sample.Web.UI.Compatibility.CompareValidator, Validators, Version=1.0.0.0"/>
                <add tagType="System.Web.UI.WebControls.CustomValidator" mappedTagType="Sample.Web.UI.Compatibility.CustomValidator, Validators, Version=1.0.0.0"/>
                <add tagType="System.Web.UI.WebControls.RangeValidator" mappedTagType="Sample.Web.UI.Compatibility.RangeValidator, Validators, Version=1.0.0.0"/>
                <add tagType="System.Web.UI.WebControls.RegularExpressionValidator" mappedTagType="Sample.Web.UI.Compatibility.RegularExpressionValidator, Validators, Version=1.0.0.0"/>
                <add tagType="System.Web.UI.WebControls.RequiredFieldValidator" mappedTagType="Sample.Web.UI.Compatibility.RequiredFieldValidator, Validators, Version=1.0.0.0"/>
                <add tagType="System.Web.UI.WebControls.ValidationSummary" mappedTagType="Sample.Web.UI.Compatibility.ValidationSummary, Validators, Version=1.0.0.0"/>
            </tagMapping>
        </pages>
        <!-- 
          Set compilation debug="true" to insert debugging 
          symbols into the compiled page. Because this 
          affects performance, set this value to true only 
          during development.
    -->
 

[joeriggs]

Part 2:

<compilation debug="true">
            <assemblies>
                <add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="System.Management, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
                <add assembly="System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
                <add assembly="System.Configuration.Install, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
                <add assembly="System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
                <add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="System.Security, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="stdole, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="System.Messaging, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="System.Runtime.Serialization.Formatters.Soap, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
                <add assembly="System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="DevExpress.Data.v6.3, Version=6.3.2.0, Culture=neutral, PublicKeyToken=79868B8147B5EAE4"/>
                <add assembly="DevExpress.Utils.v6.3, Version=6.3.2.0, Culture=neutral, PublicKeyToken=79868B8147B5EAE4"/>
                <add assembly="DevExpress.XtraPrinting.v6.3, Version=6.3.2.0, Culture=neutral, PublicKeyToken=79868B8147B5EAE4"/>
                <add assembly="DevExpress.XtraReports.v6.3, Version=6.3.2.0, Culture=neutral, PublicKeyToken=79868B8147B5EAE4"/>
                <add assembly="DevExpress.XtraEditors.v6.3, Version=6.3.2.0, Culture=neutral, PublicKeyToken=79868B8147B5EAE4"/>
                <add assembly="DevExpress.XtraBars.v6.3, Version=6.3.2.0, Culture=neutral, PublicKeyToken=79868B8147B5EAE4"/>
                <add assembly="DevExpress.XtraCharts.v6.3, Version=6.3.2.0, Culture=neutral, PublicKeyToken=79868B8147B5EAE4"/>
                <add assembly="DevExpress.XtraRichTextEdit.v6.3, Version=6.3.2.0, Culture=neutral, PublicKeyToken=79868B8147B5EAE4"/>
                <add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
                <add assembly="DevExpress.XtraNavBar.v6.3, Version=6.3.2.0, Culture=neutral, PublicKeyToken=79868B8147B5EAE4"/>
                <add assembly="DevExpress.Web.ASPxDataControls.v6.3, Version=6.3.2.0, Culture=neutral, PublicKeyToken=79868B8147B5EAE4"/>
                <add assembly="System.Drawing.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="Microsoft.Build.Utilities, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="Microsoft.Build.Framework, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="vjslib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/></assemblies>
            <buildProviders>
                <add extension="*.asbx" type="Microsoft.Web.Preview.Services.BridgeBuildProvider"/>
            </buildProviders>
        </compilation>
        <authentication mode="Forms">
            <forms name=".ASPXAUTH" loginUrl="Default.aspx" protection="All"/>
        </authentication>
        <authorization>
            <deny users="?"/>
            <!--swith this to deny to turn on logon-->
        </authorization>
        <!--
          ASMX is mapped to a new handler so that proxy javascripts can also be served.
    -->
        <httpHandlers>
            <remove verb="*" path="*.asmx"/>
            <add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
            <add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
            <add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false"/>
        </httpHandlers>
        <httpModules>
            <add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
            <add name="EntityTransactionModule" type="XXXXX.Web.Data.EntityTransactionModule"/>
        </httpModules>
        <!--<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
          <error statusCode="403" redirect="NoAccess.htm"/>
          <error statusCode="404" redirect="FileNotFound.htm"/>
      </customErrors>-->
        <globalization requestEncoding="UTF-8" responseEncoding="UTF-8" fileEncoding="UTF-8" enableClientBasedCulture="false" responseHeaderEncoding="UTF-8" resourceProviderFactoryType="" enableBestFitResponseEncoding="false" culture="auto" uiCulture="auto"/>
    </system.web>
    <location path="GeneralError.aspx">
        <system.web>
            <authorization>
                <allow users="?">
                </allow>
            </authorization>
        </system.web>
    </location>
    <location path="PageNotFound.aspx">
        <system.web>
            <authorization>
                <allow users="?">
                </allow>
            </authorization>
        </system.web>
    </location>
 <system.webServer>
        <validation validateIntegratedModeConfiguration="false"/>
        <modules>
            <add name="ScriptModule" preCondition="integratedMode" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
        </modules>
        <handlers>
            <remove name="WebServiceHandlerFactory-Integrated"/>
            <add name="ScriptHandlerFactory" verb="*" path="*.asmx" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
            <add name="ScriptHandlerFactoryAppServices" verb="*" path="*_AppService.axd" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
            <add name="ScriptResource" preCondition="integratedMode" verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
        </handlers>
    </system.webServer>
</configuration>
 

[joeriggs]

The App Pool Settings are as below:

*Recycle Tab
Recycle Worker Process (mins) 40
Recycle Worker Process (requests) 3000
Max virtual memory 500
Max used memory 512

*Performance Tab
Shutdown worker processes ... 10
Limit the kernal queue (requests) 1000
Web Garden worker processes 12

*Health
Ping worker process every 30
Failures 5
Time period 5
Worker process startup 90
Shutdown 90

*Identity
Predefined - Network Service

Looks fine to me but maybe someone can spot something.

[joeriggs]

No joy on this one so far, anyone have any ideas

[Motoma]

I am coming at this thread with almost no IIS session experience.

Have you gone into the database to see that the session is actually gone? If so, fire up the SQL Profiler in Enterprise Manager and see if there is anything that may be causing that. Perhaps you are forgetting to do a commit?

In other languages, maintaining a session requires the session id to be stored client side. This is usually done through a cookie. What may be happening is your cookie is getting invalidated or expired a couple times a day. How does your system draw a link between a user and the session information?

[joeriggs]

hi the session data is still there as it can be brought back with an IIS reset. I'll look into the cookie issue bt it shouldn't cause all sessions to die at the same moment. Thought someone else might have faced this one.