none
SQL Server user instance: underlying provider failed to open RRS feed

  • Question

  • I have a Windows application accessing a SQL server database with user instances.  On first use after a period of non-use, and attempting to access the database, the application generates a SQL Server Entity Exception: "The underlying provider failed to open", with the inner exception being:

    SqlException: Connection Timeout Expired.  The timeout period elapsed during the post-login phase.  The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=1754; handshake=2031; [Login] initialization=0; authentication=385; [Post-Login] complete=10628;

    This only occurs if the application has been closed for more than around 30 minutes (I have read that SQL Server keeps a database open for access for around 30 minutes before closing the connection automatically, which accounts for this).

    My initialization code is as follows:

    public MainWindow()
            {
                InitializeComponent();
                Database.SetInitializer<PMMEntities>(new CreateDatabaseIfNotExists<PMMEntities>());
                
                firstLoad = true;
                
            }
    
            private void PMM_MainWindow_Initialized(object sender, EventArgs e)
            {
                //try
                //{
                    PMMEntities pmmData = new PMMEntities();
                    var getSettings =
                        from gs in pmmData.UserSettings
                        where gs.SettingsID == 0
                        select gs;
                    if (getSettings.Count()==0)
                    {
                        CRUDMethods.SetUpDefaultSettings();
                    }
                    else
                    {
                        CRUDMethods.LoadUserSettings();
                    }
                    
                //}
                //catch (Exception ex)
                //{
                //    MessageBox.Show("Cannot access SQL data files (getting settings).\nPlease check disc and try again.\n\n" + ex.Message + "\n" + ex.GetType());
                //}
            }

    The failure occurs in the PMM_MainWindow_Initialized method at:

    if (getSettings.Count()==0)

    Notwithstanding the failure, if the error is caught by the try/catch block and program execution is allowed to continue, the loading of the user settings appears to be successful.

    My connection string is:

    <add name="PMMEntities" connectionString="metadata=res://*/PMMData.csdl|res://*/PMMData.ssdl|res://*/PMMData.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.\SQLEXPRESS;initial catalog=PMM;Integrated security=True;MultipleActiveResultSets=True; User Instance=true; App=EntityFramework&quot;"
          providerName="System.Data.EntityClient" />
    Can you suggest why I am getting this error, and how I can force an open of the database before the first attempt to access it?


    Mike Whalley

    • Moved by CoolDadTx Wednesday, June 14, 2017 2:36 PM EF related
    Wednesday, June 14, 2017 12:55 PM

All replies

  • Wednesday, June 14, 2017 1:48 PM
  • Hi Mike Whalley,

    According to your description and related error message, it seems that it is an "out of resources" issue on the SQL Server causing it not to be able to respond to the login request.  Check your CPU/RAM and Disk IO performance counters during the failure time.

    In addition, please connect your database via SSMS and check if it could connect it.

    you could also try the following solution with adding more memory.

    https://stackoverflow.com/questions/1421978/help-troubleshooting-sqlexception-timeout-expired-on-connection-in-a-non-load

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 15, 2017 7:42 AM
    Moderator
  • Cole Wu

    Many thanks.  If this is a memory prblem (as I suspect) then I will have more confidence in distributing my application to others - i.e. the issue is a PC issue, not with the application or SQL Server itself (btw, SSMS opens the database properly every time so no time-outs there).

    Thank you also for the link to the StackOverflow discussion on this issue - very helpful.

    I am reluctant, however, as thaty discussion advises, to set the database's setting for Auto Close to false, as this could lead more easily to database corruption - do you have a view on this?  If it is a memory capacity problem, that suggestion will probably not fix it anyway.

    Thanks again

    Mike

    Post script: For anyone coming to this thread, I have solved the memory problem by delaying the first call to the database until after the application and its main window has initialised and loaded.  This means that the demands made on RAM by the loading of the application have passed by the time an attempt to open the database is made, and there is then accordingly a greater amount of free RAM, and less queueing and delays (and timeouts).  In the code snippett above, the getSettings call to the database was moved out ofPMM_MainWindow_Initialised into PMM_MainWindow_Loaded.


    Mike Whalley


    Thursday, June 15, 2017 9:31 AM
  • Hi Mike Whalley,

    >>I am reluctant, however, as thaty discussion advises, to set the database's setting for Auto Close to false, as this could lead more easily to database corruption - do you have a view on this?

    I am familiar with it, it seems that it is a SQL Server issue, I would suggest that you could post your issue on SQL server forum for suitable support.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 20, 2017 6:09 AM
    Moderator