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

Forcefully close and remove a database

Does anyone know if it is possible (if I have a very long running query) to
...
1. Kill the running spids in the database (without having them
rollback...which itself would take an eternity). I don't care at this point
whether the database becomes suspect (which off course it probably would).
2. I would then Drop the database.
The only way I have managed to do this in the past is to restart the SQL
Service -which is not a very elegant solution and should be unnecessary (in
theory).
Am I missing a command or an undocumented kill parameter.
Many thanks,
Mark."news.microsoft.com" <nospam@.nospam.com> wrote in message
news:e3VCpplNIHA.4740@.TK2MSFTNGP02.phx.gbl...
> Does anyone know if it is possible (if I have a very long running query)
> to ...
> 1. Kill the running spids in the database (without having them
> rollback...which itself would take an eternity). I don't care at this
> point whether the database becomes suspect (which off course it probably
> would).
> 2. I would then Drop the database.
> The only way I have managed to do this in the past is to restart the SQL
> Service -which is not a very elegant solution and should be unnecessary
> (in theory).
> Am I missing a command or an undocumented kill parameter.
> Many thanks,
> Mark.
>
Try:
ALTER DATABASE db1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--
David Portas

Forcefully close and remove a database

Does anyone know if it is possible (if I have a very long running query) to
...
1. Kill the running spids in the database (without having them
rollback...which itself would take an eternity). I don't care at this point
whether the database becomes suspect (which off course it probably would).
2. I would then Drop the database.
The only way I have managed to do this in the past is to restart the SQL
Service -which is not a very elegant solution and should be unnecessary (in
theory).
Am I missing a command or an undocumented kill parameter.
Many thanks,
Mark.
"news.microsoft.com" <nospam@.nospam.com> wrote in message
news:e3VCpplNIHA.4740@.TK2MSFTNGP02.phx.gbl...
> Does anyone know if it is possible (if I have a very long running query)
> to ...
> 1. Kill the running spids in the database (without having them
> rollback...which itself would take an eternity). I don't care at this
> point whether the database becomes suspect (which off course it probably
> would).
> 2. I would then Drop the database.
> The only way I have managed to do this in the past is to restart the SQL
> Service -which is not a very elegant solution and should be unnecessary
> (in theory).
> Am I missing a command or an undocumented kill parameter.
> Many thanks,
> Mark.
>
Try:
ALTER DATABASE db1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
David Portas

Forcefully close and remove a database

Does anyone know if it is possible (if I have a very long running query) to
...
1. Kill the running spids in the database (without having them
rollback...which itself would take an eternity). I don't care at this point
whether the database becomes suspect (which off course it probably would).
2. I would then Drop the database.
The only way I have managed to do this in the past is to restart the SQL
Service -which is not a very elegant solution and should be unnecessary (in
theory).
Am I missing a command or an undocumented kill parameter.
Many thanks,
Mark."news.microsoft.com" <nospam@.nospam.com> wrote in message
news:e3VCpplNIHA.4740@.TK2MSFTNGP02.phx.gbl...
> Does anyone know if it is possible (if I have a very long running query)
> to ...
> 1. Kill the running spids in the database (without having them
> rollback...which itself would take an eternity). I don't care at this
> point whether the database becomes suspect (which off course it probably
> would).
> 2. I would then Drop the database.
> The only way I have managed to do this in the past is to restart the SQL
> Service -which is not a very elegant solution and should be unnecessary
> (in theory).
> Am I missing a command or an undocumented kill parameter.
> Many thanks,
> Mark.
>
Try:
ALTER DATABASE db1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
David Portas