sql server connect: Exclusive access to MS SQL Database -- sql server connect

RE: Exclusive access to MS SQL Database

Mike Epprecht (SQL MVP)
2/4/2005 8:23:05 AM

Hi

Kill all the other connections where SPID > 50, except for yours. Put the DB
in Single Use mode, do your processing and then remove Single Use Mode.

Regards
Mike

[quoted text, click to view]

RE: Exclusive access to MS SQL Database

Alejandro Mesa
2/4/2005 10:19:03 AM

use ALTER DATABASE,

Example:

use master
go

alter database northwind
set SINGLE_USER with ROLLBACK IMMEDIATE
go

--do your stuff
go

alter database northwind
set MULTI_USER with NO_WAIT
go

AMB

[quoted text, click to view]

Re: Exclusive access to MS SQL Database

JohnnyAppleseed
2/7/2005 2:44:34 PM

Your application will need to login with system admin rights in order to do
the following:

Kill all connections automatically (no need to kill each indicidually),
rollback any unresolved transactions, and place the database in single user
mode.

ALTER DATABASE API SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Leave the database in read-only mode. This could help optimize performace of
reporting and data transfer processing.

ALTER DATABASE API SET READ_ONLY

Restrict login to the database to only those users belonging to the database
owner (DBO) role.

ALTER DATABASE API SET RESTRICTED_USER

After doing your thing, the following will restore the database back to
normal.

ALTER DATABASE API SET READ_WRITE

ALTER DATABASE API SET MULTI_USER

Also, for periodic reporting, you may want to restore the latest backup to a
dedicated reporting server or database. That way, the production database
would not need to be made unavailable.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server connect group.