How can I clear active connections without disabling the database?

To better explain the question let me build a scenario.
If someone is connected to the database there is an active connection, which does appear in Management Studio. If we try to do a restore of the database, SQL gives us a message saying that it cannot gain exclusive access to the database and the restore fails. To gain exclusive access to the database we need to clear the active connections.
Currently in Management Studio the only ways that I have found to clear the connections are to take the database offline or detach the database completely. What I would like to know is if there is another way to clear the active connections without have to take the database offline or detach it?
In SQL 2000 I could right-click the database, select all tasks, and then select detach database and click clear connections. Of course I would then have to make sure I clicked cancel otherwise I would mistakenly detach the database. This would clear the connections without taking the database offline.
If the only option in Management Studio to clear the connections is to disable the database I would like to request an option under tasks called clear connections. This way the server administrator could click on this to see the current active connections and clear them, either individually, or all of them without having to take the database offline or detach it.
Thanks in advance!

There are several ways you can do this without having to detach the database.
1. run sp_who2 to see all the SPIDs connected to your database. For each SPID, execute kill <spid #>

2. In Object Explorer, Click on Management -> Activity Monitor. RIght-click, select View Processes. From there, you can filter the proccesses any way you want, even by database. After that, you can right-click on each process and select Kill Process.
While it's not a one-shot command, you do have much more control over who gets disconnected.
|||I tried what you suggested and it works. I was curious still though if there are any plans to add a clear connections options that will allow us to clear all connections to a database with one click of a button, similar to SQL 2000.
sql

How asp.net application communicates with MS SQL database

Hi,

I'm interested in how asp.net application communicates with MS SQL database. I mean, is communication encrypted or can it be? I have scenario where database and web application will communicate over Internet, they are not in the local network.

Please share your opinions.

Yes, you can encrypt the communications. It's easier in SQL Server 2005 than 2000.

Don

Hosting website on SQL Sever.

I am planning to develop a website which will use SQL
Server as it's database.
My question are:
(1) Do I need to BUY Server in this scenario because I
have to test my website on my local webserver. Ultimately
the site would be uploaded to third-party's hosting
server. Should I buy Developer Edition or Evaluation
Edition or MSDE would work.
(2) I would be required to update my SQL Server database
at least once in a week. How would I copy my database to
hosting server database. If it were Access, I would have
just copy the access database file to the hosting server.
What steps are required to be taken to copy SQL Server
Database sitting in our SQL Server to hosting server?
(3) What are the recommened hosting server websites which
support SQL Server.
(4) Let's say I plan to buy SQL Server and host the
website on our webserver,then which Edition of SQL Server
should I buy ... and how would the licening thing work in
this situation? For how may users/licences I have to buy
this SQL Server?
*1.)
MSDE would work but you won't have enterprise tools.
however if you are developing in .NET then there are some DB tools there.
There is also a freebie IDE for MSDE.
Hosting sites such as www.discountasp.net offer SQL Server for like $25 per
month--1 database
as far as for testing as a developer, I am sure that you have the right to
use it to develop...for sure if you an MSDN subscriber and maybe just as
vb.net pro owner--not sure.
2.) On discountasp.net I can just connect to my database in enterprise
tools...other hosts are probably the same.
if you are using dialup internet connection, then be aware that you will
probably need to modify the database in Query Analyzer using DDL and SQL
statements as using EM tools is too slow...this isn't too bad though.
3.) I have been real happy with discountasp.net... there are others.
4.) Will be expensive if just for hosting on the internet... If you are to
use it in house and on internet...maybe worth it--else you might want to
just host it for $25per month. Average SQL Server, last time I checked
(it's been a while) was $5000 for the server and a decent number of CALS.
And then I know years ago you had to purchase an internet connection license
and it used to be about $1500 per processor for you server. So you can see
it gets expensive...
for more prices I suggest you visit Microsoft's website and read.
HTH,
Shane
<anonymous@.discussions.microsoft.com> wrote in message
news:0e0d01c47b47$f171fb90$a301280a@.phx.gbl...
> I am planning to develop a website which will use SQL
> Server as it's database.
> My question are:
> (1) Do I need to BUY Server in this scenario because I
> have to test my website on my local webserver. Ultimately
> the site would be uploaded to third-party's hosting
> server. Should I buy Developer Edition or Evaluation
> Edition or MSDE would work.
> (2) I would be required to update my SQL Server database
> at least once in a week. How would I copy my database to
> hosting server database. If it were Access, I would have
> just copy the access database file to the hosting server.
> What steps are required to be taken to copy SQL Server
> Database sitting in our SQL Server to hosting server?
> (3) What are the recommened hosting server websites which
> support SQL Server.
> (4) Let's say I plan to buy SQL Server and host the
> website on our webserver,then which Edition of SQL Server
> should I buy ... and how would the licening thing work in
> this situation? For how may users/licences I have to buy
> this SQL Server?
> *|||Regarding buying SQL if your internet guy is hosting it... The answer is
NO... The internet company would have purchased SQL Server that you are
using on the hosted web site...
You would be responsible for your test server, and MSDE is appropriate and
intended just for that purpose...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:0e0d01c47b47$f171fb90$a301280a@.phx.gbl...
> I am planning to develop a website which will use SQL
> Server as it's database.
> My question are:
> (1) Do I need to BUY Server in this scenario because I
> have to test my website on my local webserver. Ultimately
> the site would be uploaded to third-party's hosting
> server. Should I buy Developer Edition or Evaluation
> Edition or MSDE would work.
> (2) I would be required to update my SQL Server database
> at least once in a week. How would I copy my database to
> hosting server database. If it were Access, I would have
> just copy the access database file to the hosting server.
> What steps are required to be taken to copy SQL Server
> Database sitting in our SQL Server to hosting server?
> (3) What are the recommened hosting server websites which
> support SQL Server.
> (4) Let's say I plan to buy SQL Server and host the
> website on our webserver,then which Edition of SQL Server
> should I buy ... and how would the licening thing work in
> this situation? For how may users/licences I have to buy
> this SQL Server?
> *

Hosting website on SQL Sever.

I am planning to develop a website which will use SQL
Server as it's database.
My question are:
(1) Do I need to BUY Server in this scenario because I
have to test my website on my local webserver. Ultimately
the site would be uploaded to third-party's hosting
server. Should I buy Developer Edition or Evaluation
Edition or MSDE would work.
(2) I would be required to update my SQL Server database
at least once in a week. How would I copy my database to
hosting server database. If it were Access, I would have
just copy the access database file to the hosting server.
What steps are required to be taken to copy SQL Server
Database sitting in our SQL Server to hosting server?
(3) What are the recommened hosting server websites which
support SQL Server.
(4) Let's say I plan to buy SQL Server and host the
website on our webserver,then which Edition of SQL Server
should I buy ... and how would the licening thing work in
this situation? For how may users/licences I have to buy
this SQL Server?
*1.)
MSDE would work but you won't have enterprise tools.
however if you are developing in .NET then there are some DB tools there.
There is also a freebie IDE for MSDE.
Hosting sites such as www.discountasp.net offer SQL Server for like $25 per
month--1 database
as far as for testing as a developer, I am sure that you have the right to
use it to develop...for sure if you an MSDN subscriber and maybe just as
vb.net pro owner--not sure.
2.) On discountasp.net I can just connect to my database in enterprise
tools...other hosts are probably the same.
if you are using dialup internet connection, then be aware that you will
probably need to modify the database in Query Analyzer using DDL and SQL
statements as using EM tools is too slow...this isn't too bad though.
3.) I have been real happy with discountasp.net... there are others.
4.) Will be expensive if just for hosting on the internet... If you are to
use it in house and on internet...maybe worth it--else you might want to
just host it for $25per month. Average SQL Server, last time I checked
(it's been a while) was $5000 for the server and a decent number of CALS.
And then I know years ago you had to purchase an internet connection license
and it used to be about $1500 per processor for you server. So you can see
it gets expensive...
for more prices I suggest you visit Microsoft's website and read.
HTH,
Shane
<anonymous@.discussions.microsoft.com> wrote in message
news:0e0d01c47b47$f171fb90$a301280a@.phx.gbl...
> I am planning to develop a website which will use SQL
> Server as it's database.
> My question are:
> (1) Do I need to BUY Server in this scenario because I
> have to test my website on my local webserver. Ultimately
> the site would be uploaded to third-party's hosting
> server. Should I buy Developer Edition or Evaluation
> Edition or MSDE would work.
> (2) I would be required to update my SQL Server database
> at least once in a week. How would I copy my database to
> hosting server database. If it were Access, I would have
> just copy the access database file to the hosting server.
> What steps are required to be taken to copy SQL Server
> Database sitting in our SQL Server to hosting server?
> (3) What are the recommened hosting server websites which
> support SQL Server.
> (4) Let's say I plan to buy SQL Server and host the
> website on our webserver,then which Edition of SQL Server
> should I buy ... and how would the licening thing work in
> this situation? For how may users/licences I have to buy
> this SQL Server?
> *|||Regarding buying SQL if your internet guy is hosting it... The answer is
NO... The internet company would have purchased SQL Server that you are
using on the hosted web site...
You would be responsible for your test server, and MSDE is appropriate and
intended just for that purpose...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:0e0d01c47b47$f171fb90$a301280a@.phx.gbl...
> I am planning to develop a website which will use SQL
> Server as it's database.
> My question are:
> (1) Do I need to BUY Server in this scenario because I
> have to test my website on my local webserver. Ultimately
> the site would be uploaded to third-party's hosting
> server. Should I buy Developer Edition or Evaluation
> Edition or MSDE would work.
> (2) I would be required to update my SQL Server database
> at least once in a week. How would I copy my database to
> hosting server database. If it were Access, I would have
> just copy the access database file to the hosting server.
> What steps are required to be taken to copy SQL Server
> Database sitting in our SQL Server to hosting server?
> (3) What are the recommened hosting server websites which
> support SQL Server.
> (4) Let's say I plan to buy SQL Server and host the
> website on our webserver,then which Edition of SQL Server
> should I buy ... and how would the licening thing work in
> this situation? For how may users/licences I have to buy
> this SQL Server?
> *

Hosting website on SQL Sever.

I am planning to develop a website which will use SQL
Server as it's database.
My question are:
(1) Do I need to BUY Server in this scenario because I
have to test my website on my local webserver. Ultimately
the site would be uploaded to third-party's hosting
server. Should I buy Developer Edition or Evaluation
Edition or MSDE would work.
(2) I would be required to update my SQL Server database
at least once in a week. How would I copy my database to
hosting server database. If it were Access, I would have
just copy the access database file to the hosting server.
What steps are required to be taken to copy SQL Server
Database sitting in our SQL Server to hosting server?
(3) What are the recommened hosting server websites which
support SQL Server.
(4) Let's say I plan to buy SQL Server and host the
website on our webserver,then which Edition of SQL Server
should I buy ... and how would the licening thing work in
this situation? For how may users/licences I have to buy
this SQL Server?
*
1.)
MSDE would work but you won't have enterprise tools.
however if you are developing in .NET then there are some DB tools there.
There is also a freebie IDE for MSDE.
Hosting sites such as www.discountasp.net offer SQL Server for like $25 per
month--1 database
as far as for testing as a developer, I am sure that you have the right to
use it to develop...for sure if you an MSDN subscriber and maybe just as
vb.net pro owner--not sure.
2.) On discountasp.net I can just connect to my database in enterprise
tools...other hosts are probably the same.
if you are using dialup internet connection, then be aware that you will
probably need to modify the database in Query Analyzer using DDL and SQL
statements as using EM tools is too slow...this isn't too bad though.
3.) I have been real happy with discountasp.net... there are others.
4.) Will be expensive if just for hosting on the internet... If you are to
use it in house and on internet...maybe worth it--else you might want to
just host it for $25per month. Average SQL Server, last time I checked
(it's been a while) was $5000 for the server and a decent number of CALS.
And then I know years ago you had to purchase an internet connection license
and it used to be about $1500 per processor for you server. So you can see
it gets expensive...
for more prices I suggest you visit Microsoft's website and read.
HTH,
Shane
<anonymous@.discussions.microsoft.com> wrote in message
news:0e0d01c47b47$f171fb90$a301280a@.phx.gbl...
> I am planning to develop a website which will use SQL
> Server as it's database.
> My question are:
> (1) Do I need to BUY Server in this scenario because I
> have to test my website on my local webserver. Ultimately
> the site would be uploaded to third-party's hosting
> server. Should I buy Developer Edition or Evaluation
> Edition or MSDE would work.
> (2) I would be required to update my SQL Server database
> at least once in a week. How would I copy my database to
> hosting server database. If it were Access, I would have
> just copy the access database file to the hosting server.
> What steps are required to be taken to copy SQL Server
> Database sitting in our SQL Server to hosting server?
> (3) What are the recommened hosting server websites which
> support SQL Server.
> (4) Let's say I plan to buy SQL Server and host the
> website on our webserver,then which Edition of SQL Server
> should I buy ... and how would the licening thing work in
> this situation? For how may users/licences I have to buy
> this SQL Server?
> *
|||Regarding buying SQL if your internet guy is hosting it... The answer is
NO... The internet company would have purchased SQL Server that you are
using on the hosted web site...
You would be responsible for your test server, and MSDE is appropriate and
intended just for that purpose...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:0e0d01c47b47$f171fb90$a301280a@.phx.gbl...
> I am planning to develop a website which will use SQL
> Server as it's database.
> My question are:
> (1) Do I need to BUY Server in this scenario because I
> have to test my website on my local webserver. Ultimately
> the site would be uploaded to third-party's hosting
> server. Should I buy Developer Edition or Evaluation
> Edition or MSDE would work.
> (2) I would be required to update my SQL Server database
> at least once in a week. How would I copy my database to
> hosting server database. If it were Access, I would have
> just copy the access database file to the hosting server.
> What steps are required to be taken to copy SQL Server
> Database sitting in our SQL Server to hosting server?
> (3) What are the recommened hosting server websites which
> support SQL Server.
> (4) Let's say I plan to buy SQL Server and host the
> website on our webserver,then which Edition of SQL Server
> should I buy ... and how would the licening thing work in
> this situation? For how may users/licences I have to buy
> this SQL Server?
> *