db042188


i'd like to block all other processes that interact with my database while my archive process is running on that database. Can this be done




Re: want to block all other db activity while my archive process is running...

hunchback


What about closing all connections and setting the database to single user

alter database your_db

set SINGLE_USER

with ROLLBACK IMMEDIATE;

go

archive process ...

go

alter database your_db

set MULTI_USER

go

AMB






Re: want to block all other db activity while my archive process is running...

Arnie Rowland

Try this:

ALTER DATABASE MyDataBase

SET SINGLE_USER

{WITH ROLLBACK IMMEDIATE}

This will keep other users from connecting BUT will allow currently connected users to complete their work. When all users are finished, this SPID will have the database in single user mode.

IF you wish to immediately terminate currently connected users, you could use the WITH ROLLBACK IMMEDIATE option. Currently connected users will have their current work terminated and any executing code will be terminiated with incomplete transactions rolled back.

Then when you are finished with the archive process:

ALTER DATABASE MyDataBase

SET MULTI_USER