Search code examples
c#sqlsql-serversql-server-expressuser-instance

SQL Server User Instances error: Existing databases has reached the max number allowed

I'm using C# in Visual Studio 2008 to loop through MDF Files on my PC and extract data from within them. I'm using a Table Adapter to point to the local MDF file.

Recently one of my PC's refuses to let me attach any new Data Source as it says

System.Data.SqlClient.SqlException: Unable to create/attach any new database because the number of existing databases has reached the maximum number allowed: 32766

Even if I start a fresh Windows application and try to add an MDF file (on my desktop) to it as a Data Source, I get the above error.

Can anyone tell me how to remove/delete the existing connections ?

My code works fine on another PC and I've re-installed Visual Studio on my PC but still get this error.

C# Table adapter code:

tmTableAdapter.Connection.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename='" + pathofmdffile + "';Integrated Security=True;Connect Timeout=30;User Instance=True";
tmTableAdapter.Connection.Open();
Solution

  • Reinstalling VS won't help as the error is coming from SQL Server.

    Look at these MSDN pages which have lots of good info:

    Since you are using "User Instances", I guess the DBs won't show up when connecting to the main SQLEXPRESS instance.

    Things to check:

    • In SSMS, connect to your last attached DB by doing the following:

      • Go to Object Explorer
      • New Connection to a Database Engine
      • In the "Connect to Server" popup:
      • Click the "Options >>" button
      • Go to the "Additional Connection Properties" tab
      • Enter in the following in the text area:
        User Instance = true
      • Click the "Connect" button
      • Expand "Databases" folder. You should see them all, named as the full path to the MDF file
      • Right-click on a database
      • Go to "Tasks >"
      • First option is "Detach"

      Obviously this isn't practical for 32,766 databases, but for a few it is the best option.

    • By default the sqlservr.exe process hangs around for 60 minutes, and any databases you attach prior to it terminating gets added to the list and likely reset the expiration counter. You can end the process immediately by connecting to the most recent attached DB (as noted above; those steps will work for a New Query, or if connecting via Object Explorer, then right-click on the instance name and go to "New Query"), and run the following:

      SHUTDOWN;

      This will clear out all connected databases in one shot.

    • Set the timeout to be less than 60 minutes. According to the SQL Server Express User Instances page (same link as above):

      A system administrator on the parent instance can set the duration of the time-out period for a user instance by using sp_configure to change the user instance timeout option. The default is 60 minutes.

      In SSMS (make sure you are connected to the parent instance):

      -- View current setting (in the "run_value" field)
EXEC sp_configure 'user instance timeout'
-- Documentation says default is 60 but mine was 5


-- If you can't see the option, run the following:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;


-- To change it, run the following:
EXEC sp_configure 'user instance timeout', 5;

    • Use the "SQL Server Express Utility" to detach one or more databases:

    • Look in the following directory which will have the 4 system DBs:

      • On XP / Vista: C:\Documents and Settings{UserName}\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

      • On Windows 7 and newer: C:\Users{UserName}\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

      This is mostly just informational as these files cannot be deleted so long as the SQL Server process is running.

    For an on-going basis, if you will be attaching many DBs in a loop, you can programatically get rid of them in that process by running sp_detach_db when you are done using each DB:

    USE [master];
ALTER DATABASE [{DatabaseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sp_detach_db @dbname =N'{DatabaseName}';

    And just FYI, "User Instances" are now deprecated. You should look into using SQL Server Express LocalDB.