Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

sql

Force users/connections to disconnect from db

Hello,
I need to attach and detach 50 DB's, for which I have wrote a simple script. How can I make sure that there are no connections or users connected to the DB's, if there any users how can I forcefully disconnect them.
Thanks
DakkiOriginally posted by Dakki
Hello,

I need to attach and detach 50 DB's, for which I have wrote a simple script. How can I make sure that there are no connections or users connected to the DB's, if there any users how can I forcefully disconnect them.

Thanks
Dakki

sp_detachDB does detach a db even if users are logged in. Only if transactions are running, dettach will fail. sp_who shows you for every db on this server all logged in users.

Hope this helps
Peter|||Originally posted by peterdbd
sp_detachDB does detach a db even if users are logged in. Only if transactions are running, dettach will fail. sp_who shows you for every db on this server all logged in users.

Hope this helps
Peter

Excellent, I shall try this...

many thanks

dakki|||peterdbd,

i don't think it's an accurate statement, because you'll get this error if there is at least 1 connection open for the database you're trying to detach, even if this connection has not performed a single operation:

Server: Msg 3701, Level 16, State 1, Line 1
Cannot detach the database 'db_name' because it is currently in use.|||guru, you are right, I had to re run the script, since some of the db's had connections to it...

thanks again, to you both ...|||list all users with sp_who2
and find out sid of all the db u r looking at

using sa , kill all sid

eg:- kill sid

then proceed with detach.

Note:-
Do not any sid doing updates...|||Take help from this link (http://www.sql-server-performance.com/q&a37.asp) which includes the script to kill are users that are connected to a database that needs to be dropped/detached etc. etc.:cool:|||there are 3 problems with the script:

- based on stored procedure. i wouldn't recommend leaving such tool so handy

- based on cursor, - simply no need

- does not take into account the possibility of attempting to kill yourself (not that you'll succeed though)

there is a simpler way:

declare @.cmd varchar(100)
while (select count(*)
from master.dbo.sysprocesses (nolock)
where spid != @.@.spid and db_name(dbid) = 'your_db_name') > 0 begin
set @.cmd = 'kill ' +(select cast(min(spid) as varchar(25))
from master.dbo.sysprocesses (nolock)
where spid != @.@.spid and db_name(dbid) = 'mci2k')
exec ( @.cmd )
end