C# How to drop connection when detaching database

Question

I currently have the following Command

SqlCommand command = new SqlCommand(@"sys.sp_detach_db 'DBname'", conn);

to detach a database, but it throws an exception when I execute it. Saying that the database is in use. How can I drop the connection when or before I detach it?

Update: I am currently using SMO but it's still not working:

bool DetachBackup(string backupDBName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            var builder = new SqlConnectionStringBuilder(connectionString);
            string serverName = builder.DataSource; 
            string dbName = builder.InitialCatalog;
            try
            {
                Server smoServer = new Server(serverName);
                smoServer.DetachDatabase(backupDBName + DateTime.Now.ToString("yyyyMMdd"), false);
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return false;
            }
        }

could you please post the code that you are using for `detach the database` — sujith karivelil, Oct 06 '15 at 09:58
I would suggest you try setting the DB to single user as mentioned by marc_s. If that fails, run the sp_who stored procedure and dump the results into a temp table, select all the connections that match your database name and iterate through the list and disconnect each user. You can ofcourse check if the "status" is active and wherether or not the "hostname" is an actual user and not some sql service etc. Also, see my answer below — TheDanMan, Oct 06 '15 at 10:11
Oh, and don't forget that your current connection also counts as "in use", in other words, you also need to disconnect from the DB to detach — TheDanMan, Oct 06 '15 at 10:17
Yeah, it counts. So I would suggest you disconnect from your DB and connect to the "master" DB. Then run the detach code. See my answer for a simpler and cleaner detach query — TheDanMan, Oct 06 '15 at 10:26

score 3 · Answer 1 · answered Oct 06 '15 at 05:33

3

You'll need to execute this SQL statement first, before detaching:

ALTER DATABASE YourDbNameHere
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

It sets the database into single user mode, and tosses out any active connection immediately.

answered Oct 06 '15 at 05:33

marc_s

732,580
175
1,330
1,459

This helped but Rahul was spot-on. I still thank you very much @marc_s. – J.P Masangcay Oct 06 '15 at 10:47

sujith karivelil · Answer 2 · 2015-10-06T10:58:14.887

2

Make use of using statement.

"using" statement is to ensure that the object is disposed as soon as it goes out of scope, and it doesn't require explicit code to ensure that this happens.

using(SqlCommand command = new SqlCommand(@"sys.sp_detach_db 'DBname'", conn))
{
//Processing command
}//Here it detach the connection and dispose the command

Updates

In your case the problem is that You cannot detach or reattach the database when it is being used. Here is an example from MSDN, This will be helpful for you to solve the issue

The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL Server. Detaching and attaching a database is useful if you want to change the database to a different instance of SQL Server on the same computer, or if you want to move the database.

edited Oct 06 '15 at 10:58

answered Oct 06 '15 at 05:27

sujith karivelil

28,671
6
55
88

2

this doesn't answer the question. OP's issue is that the DATABASE is in use, not that the connection isn't disposed. So essentially some other SQL user or service is accessing the database whilst OP is trying to detach it. I honestly think this is more an SQL question than it is a C# question. – TheDanMan Oct 06 '15 at 09:55
@TheDanMan Right, disposing still has the connection in session. Its really frustrating figuring this out. – J.P Masangcay Oct 06 '15 at 10:00

Rahul Tripathi · Accepted Answer · 2015-10-06T09:42:28.303

2

You can use SMO

Detaches the specified database from the instance of SQL Server with the option to update statistics before the database is detached.

using Microsoft.SqlServer.Management.Smo;

 void DetachDatabase()
 {
      Server smoServer = new Server("MSSQLSERVER2008");
      smoServer.DetachDatabase("Yourdatabasename", False);
 }

To get server name from the app.config you can try like this:

string connectString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectString);  
string serverName = builder.DataSource;   //Server name
string dbName = builder.InitialCatalog; //Database name

edited Oct 06 '15 at 09:42

answered Oct 06 '15 at 05:36

Rahul Tripathi

168,305
31
280
331

My Server name won't be static and it is defined in app.config. How will I be able to reference that to Server()? – J.P Masangcay Oct 06 '15 at 09:36
@JoshuaMasangcay:- Updated my answer. Please check. – Rahul Tripathi Oct 06 '15 at 09:42
It didn't work unfortunately -- the solution, I mean. It still says that the database is in use. – J.P Masangcay Oct 06 '15 at 10:01
@JoshuaMasangcay:- The .mdf file of your database will be in use by SQL Server Management Studio. So until you are not going to detach it from there, I don't think you can get rid of this error. You can also refe: [Database Connection Error “file in use”](http://stackoverflow.com/questions/13215172/database-connection-error-file-in-use) The file is in use by SQL Server Management Studio not by your application. So when you are trying to detach the database from your application it gives your the error. – Rahul Tripathi Oct 06 '15 at 10:04
@JoshuaMasangcay:- Also refer this: http://www.codeproject.com/Articles/72465/Programmatically-Enumerating-Attaching-and-Detachi – Rahul Tripathi Oct 06 '15 at 10:09

score 0 · Answer 4 · edited Apr 13 '17 at 12:42

0

Try the following

USE master; -- get out of dbname myself
GO
-- kick all other users out:
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- prevent sessions from re-establishing connection:
ALTER DATABASE [dbname] SET OFFLINE;

As mentioned by Aaron Bertrand on our sister site on this question

edited Apr 13 '17 at 12:42

Community

1
1

answered Oct 06 '15 at 10:12

TheDanMan

1,746
1
17
22

Thank you TheDanMan. But I chose Rahul's answer since I will be using SMO. But I thank you very much. – J.P Masangcay Oct 06 '15 at 10:48

C# How to drop connection when detaching database

4 Answers4