4

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();
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
lway
  • 43
  • 4
  • 3
    Do you ever do `tmTableAdapter.Connection.Close()`? – John Saunders Jan 13 '15 at 15:12
  • 1
    Reinstalling VS won't help as the error is coming from SQL Server. If you connect to the SQLEXPRESS instance in SSMS and look at the Databases folder, do you see all 32,766 of them? You likely need to detach them. – Solomon Rutzky Jan 13 '15 at 15:32
  • Thanks, yes I do a tmTableAdapter.Connection.Close() three lines later in the C# code. When I open SSMS and connect to MYCOMPUTERNAME\SQLEXPRESS and Look in Databases\System Databases I see 4 DB (master, model,msdb & tempdb) where should I look (apologies, not too familiar with SQL Server) – lway Jan 13 '15 at 15:45
  • 1
    @JohnSaunders The DBs stay attached even after closing the connection. I detail how to get rid of them in my answer. – Solomon Rutzky Jan 13 '15 at 17:12

1 Answers1

5

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.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • 2
    Thank you so much for your help. A clear and concise answer (for a learner like me) with links to help me learn more, fantastic answer. Can you expand a bit on shutting down all the connections in one go. I can detach individually like you say above but would like to select and detach multiple databases if possible (like you say it's not practical to do for all 32k dbs) – lway Jan 14 '15 at 09:22
  • Nevermind, found a solution the "SQL Server Express Utility". See following link: http://stackoverflow.com/questions/2367688/how-do-you-stop-a-user-instance-of-sql-server-sql-express-user-instance-databa – lway Jan 14 '15 at 16:56
  • 1
    @lway Yes, that utility is mentioned in that second link "SQL Server 2005 Express Edition User Instances", though it did not indicate that it could do multiple detaches with a wildcard. Nice. I can add that to my answer. But that is still not a good option to do programmatically as it requires shelling out. Instead you should still do the `sp_detach_db` in a loop if you don't want to do it per each process. After all, that is all SSEUtil is doing. You just need to ensure single user mode. And there is a DMV that shows just the User Instances and not real DBs. I will update. – Solomon Rutzky Jan 14 '15 at 17:08
  • 1
    Totally agree, at least now I can run my code again and incorporate the sp_detach_db into it. The SSE Utility is useful for digging you out of a hole and getting you back up and running but you can't beat proper programming practice. Thanks again for your help. – lway Jan 15 '15 at 08:03