本文將介紹當您嘗試卸離資料庫時，發生【Cannot detach the database '資料庫名稱' because it is currently in use.】錯誤，可能的解決方案。
當您嘗試以下列 T-SQL 卸離資料庫時，發生【Cannot detach the database '資料庫名稱' because it is currently in use.】錯誤：
1: USE master
2: GO
3:
4: --1. 建立資料庫
5: IF (DB_ID(N'MyDB') IS NOT NULL)
6: DROP DATABASE MyDB
7: GO
8:
9: CREATE DATABASE [MyDB]
10: CONTAINMENT = NONE
11: ON PRIMARY
12: ( NAME = N'MyDB', FILENAME = N'D:\Temp\MyDB.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
13: LOG ON
14: ( NAME = N'MyDB_log', FILENAME = N'D:\Temp\MyDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
15: GO
16: ALTER DATABASE [MyDB] SET COMPATIBILITY_LEVEL = 110
17: GO
18: ALTER DATABASE [MyDB] SET ANSI_NULL_DEFAULT OFF
19: GO
20: ALTER DATABASE [MyDB] SET ANSI_NULLS OFF
21: GO
22: ALTER DATABASE [MyDB] SET ANSI_PADDING OFF
23: GO
24: ALTER DATABASE [MyDB] SET ANSI_WARNINGS OFF
25: GO
26: ALTER DATABASE [MyDB] SET ARITHABORT OFF
27: GO
28: ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF
29: GO
30: ALTER DATABASE [MyDB] SET AUTO_CREATE_STATISTICS ON
31: GO
32: ALTER DATABASE [MyDB] SET AUTO_SHRINK OFF
33: GO
34: ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS ON
35: GO
36: ALTER DATABASE [MyDB] SET CURSOR_CLOSE_ON_COMMIT OFF
37: GO
38: ALTER DATABASE [MyDB] SET CURSOR_DEFAULT GLOBAL
39: GO
40: ALTER DATABASE [MyDB] SET CONCAT_NULL_YIELDS_NULL OFF
41: GO
42: ALTER DATABASE [MyDB] SET NUMERIC_ROUNDABORT OFF
43: GO
44: ALTER DATABASE [MyDB] SET QUOTED_IDENTIFIER OFF
45: GO
46: ALTER DATABASE [MyDB] SET RECURSIVE_TRIGGERS OFF
47: GO
48: ALTER DATABASE [MyDB] SET DISABLE_BROKER
49: GO
50: ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
51: GO
52: ALTER DATABASE [MyDB] SET DATE_CORRELATION_OPTIMIZATION OFF
53: GO
54: ALTER DATABASE [MyDB] SET PARAMETERIZATION SIMPLE
55: GO
56: ALTER DATABASE [MyDB] SET READ_COMMITTED_SNAPSHOT OFF
57: GO
58: ALTER DATABASE [MyDB] SET READ_WRITE
59: GO
60: ALTER DATABASE [MyDB] SET RECOVERY SIMPLE
61: GO
62: ALTER DATABASE [MyDB] SET MULTI_USER
63: GO
64: ALTER DATABASE [MyDB] SET PAGE_VERIFY CHECKSUM
65: GO
66: ALTER DATABASE [MyDB] SET TARGET_RECOVERY_TIME = 0 SECONDS
67: GO
68: USE [MyDB]
69: GO
70: IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [MyDB] MODIFY FILEGROUP [PRIMARY] DEFAULT
71: GO
72:
73: --2.建立測試資料表及測試資料
74: CREATE TABLE t1 (c1 int)
75:
76: INSERT INTO t1
77: SELECT 1 UNION ALL
78: SELECT 2 UNION ALL
79: SELECT 3 UNION ALL
80: SELECT 4 UNION ALL
81: SELECT 5
82:
83: GO
84:
85: --3.查詢資料
86: USE MyDB
87: go
88:
89: SELECT *
90: FROM t1
91: GO
92:
93: --4.設定資料庫為SINGLE_USER模式
94: ALTER DATABASE [MyDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
95: GO
96: --5.卸載資料庫
97: EXEC master.dbo.sp_detach_db @dbname = N'MyDB'
98: GO
【執行結果】
或是您透過自行開發的 AP 嘗試卸離資料庫時，發生下列的錯誤訊息：
1: using (SqlConnection con = new SqlConnection(@"Data Source=(localdb)\v11.0;Initial Catalog=MyDB;Integrated Security=True;"))
2: {
3: using (SqlDataAdapter adapter = new SqlDataAdapter("select * from t1", con))
4: {
5: DataSet ds = new DataSet();
6: adapter.Fill(ds, "t1");
7: foreach (DataRow dr in ds.Tables[0].Rows)
8: {
9: Console.WriteLine(String.Format("{0}", dr[0]));
10: }
11: }
12:
13: if (con.State != ConnectionState.Open) con.Open();
14: string strCmd = @"ALTER DATABASE [MyDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
15: EXEC master.dbo.sp_detach_db @dbname = N'MyDB'" ;
16: using (SqlCommand cmd = new SqlCommand(strCmd, con))
17: {
18: cmd.ExecuteNonQuery();
19: }
20: }
21: Console.ReadKey();
【執行結果】
不管您使用哪種方法，問題都出在於您沒辦法卸離仍有連線連接到您要卸離的資料庫，您可以在發生上述情況時，利用 sp_who 或 sp_who2 來查看連線狀況（如下圖）：
只要您把上圖中使用到您要卸離資料庫的 SPID 利用 kill 敘述刪除之後，就可以順利卸離資料庫。或是您也可以在卸離資料庫前，先切換到其他資料庫（像是master）之後再進行卸離，就可以順利完成了，如下列T-SQL 的4.1。
1: --4.設定資料庫為SINGLE_USER模式
2: ALTER DATABASE [MyDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
3: GO
4:
5: --4.1 先將資料庫切換到非要卸離的資料庫
6: USE master
7: GO
8:
9: --5.卸載資料庫
10: EXEC master.dbo.sp_detach_db @dbname = N'MyDB'
在 AP 中也是一樣的做法，如下列程式碼的第 15 列：
1: using (SqlConnection con = new SqlConnection(@"Data Source=(localdb)\v11.0;Initial Catalog=MyDB;Integrated Security=True;"))
2: {
3: using (SqlDataAdapter adapter = new SqlDataAdapter("select * from t1", con))
4: {
5: DataSet ds = new DataSet();
6: adapter.Fill(ds, "t1");
7: foreach (DataRow dr in ds.Tables[0].Rows)
8: {
9: Console.WriteLine(String.Format("{0}", dr[0]));
10: }
11: }
12:
13: if (con.State != ConnectionState.Open) con.Open();
14: string strCmd = @"ALTER DATABASE [MyDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
15: USE master;
16: EXEC master.dbo.sp_detach_db @dbname = N'MyDB'" ;
17: using (SqlCommand cmd = new SqlCommand(strCmd, con))
18: {
19: cmd.ExecuteNonQuery();
20: }
21: }
22: Console.ReadKey();
【參考資料】