SQL Server用户实例错误：现有数据库已达到允许的最大数量。

Question

SQL Server用户实例错误：现有数据库已达到允许的最大数量。

c#sqlsql-serversql-server-expressuser-instance

4

我正在使用Visual Studio 2008中的C#遍历我的PC上的MDF文件并从中提取数据。我使用一个Table Adapter指向本地的MDF文件。

最近，我的一台PC拒绝让我附加任何新的数据源，因为它显示：

System.Data.SqlClient.SqlException：由于现有数据库数量已达到允许的最大数量32766，无法创建/附加任何新数据库

即使我启动一个新的Windows应用程序并尝试将MDF文件（在我的桌面上）添加到其中作为数据源，我仍会收到上述错误。

有人能告诉我如何删除现有的连接吗？

我的代码在另一台PC上运行良好，我已重新安装了Visual Studio，但仍然出现此错误。

C＃Table适配器代码：

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

- lway

3

你曾经执行过 tmTableAdapter.Connection.Close() 吗？ - John Saunders

1

重新安装VS是没有用的，因为错误是来自SQL Server。如果您在SSMS中连接到SQLEXPRESS实例并查看数据库文件夹，是否看到了32766个数据库？您可能需要将它们分离。 - Solomon Rutzky

谢谢，是的，在C#代码中我会在三行后执行tmTableAdapter.Connection.Close()。当我打开SSMS并连接到MYCOMPUTERNAME\SQLEXPRESS，然后查看Databases\System Databases时，我看到了4个数据库（master，model，msdb和tempdb），请问我应该在哪里查找（抱歉，对SQL Server不太熟悉）。 - lway

1

@JohnSaunders 即使关闭连接，DB 仍然保持连通。我在我的答案中详细说明如何摆脱它们。 - Solomon Rutzky

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Solomon Rutzky · Accepted Answer

重新安装VS不会有所帮助，因为错误是来自SQL Server。
查看这些MSDN页面，其中包含大量有用的信息：

由于您正在使用“用户实例”，我猜当连接到主SQLEXPRESS实例时，数据库不会显示出来。
需要检查的事项：

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:
- SSEUtil.exe was written in 2005 and has not been updated since (current version is v1.0.2130)
- Download from: https://www.microsoft.com/en-us/download/confirmation.aspx?id=3990
- Use the -d[etach] <dbpath[*]>|name=<dbname> command
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.

如果您将在循环中附加许多数据库，则可以通过在使用每个数据库后运行sp_detach_db来编程地在该过程中摆脱它们，以便持续进行：

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

顺便提一下，“用户实例”现已被弃用。您应该考虑使用SQL Server Express LocalDB。