Clear connections of a database

Question

Clear connections of a database

lyang 88644

Old Hand

Points: 331
More actions
March 13, 2012 at 12:41 pm

#252444

Hi there:
I am new to SQL 2008 since I am still using SQL 2000. In SQL 2000, Connections using this database "Clear" button is to clear all the connections to a database. Is there a way to do the same thing in SQL 2008?
Thanks in advance,
Melissa

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Is this in reference to detaching or dropping the database? There is now a checkbox for both of those operations to close any existing connections.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

lyang 88644 Old Hand Points: 331 More actions · Answer 2

Yes. What I wanted to do is to drop the database connections without detaching the database. In SQL 2008 when I checked Drop connections and clicked OK. It dropped the connections and detached the database.

I found the soluction to kill the active connections to a database by using:

ALTER DATABASE databasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE

It works.

Thank you for the quick response.

Melissa

CapnHector SSCoach Points: 16143 More actions · Answer 3

lyang 88644 (3/13/2012)
Yes. What I wanted to do is to drop the database connections without detaching the database. In SQL 2008 when I checked Drop connections and clicked OK. It dropped the connections and detached the database.
I found the soluction to kill the active connections to a database by using:
ALTER DATABASE databasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE
It works.
Thank you for the quick response.
Melissa

After you are done doing what ever requires you to kill the connections make sure to run

ALTER DATABASE dbName SET MULTI_USER

Otherwise only one person at a time will be able to connect to the database.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

as another one on this, single user does exactly what it says allows 1 person access to the DB, if that is to much then you could set the database to offline then bring it online again which will not allow anyone into the database until its back online.

lyang 88644 Old Hand Points: 331 More actions · Answer 5

I switched to allow the mulit users back.

Thank you, everyone.

Melissa