How to lock entire database?

Hi,
Is there a way to lock entire database? What I am supposed to do is kind of
replication:
- a number tables exist in database, these tables should be updated
- so, temporary tables are created and filled with data
- these temporary tables are renamed to final name (using sp_rename)
This is something like 'restore database', but should not terminate database
connections.
During renaming no process should access tables. How to ensure if no
transaction is running on destination tables? I can check sp_lock output for
exclusive locked tables, wait for transactions to finish, than run my
sp_rename script.
However the final question is: how to prevent NEW transactions? Or: how to
queue transactions to run AFTER sp_rename
Regards.
Piotr.Using "ALTER DATABASE" and setting it to SINGLE_USER state.
AMB
"Piotr Strycharz" wrote:

> Hi,
> Is there a way to lock entire database? What I am supposed to do is kind o
f
> replication:
> - a number tables exist in database, these tables should be updated
> - so, temporary tables are created and filled with data
> - these temporary tables are renamed to final name (using sp_rename)
> This is something like 'restore database', but should not terminate databa
se
> connections.
> During renaming no process should access tables. How to ensure if no
> transaction is running on destination tables? I can check sp_lock output f
or
> exclusive locked tables, wait for transactions to finish, than run my
> sp_rename script.
> However the final question is: how to prevent NEW transactions? Or: how to
> queue transactions to run AFTER sp_rename
> Regards.
> Piotr.
>|||U?ytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> said:

> Using "ALTER DATABASE" and setting it to SINGLE_USER state.
This command, however, won't allow change when connections exists (or are
made) to database.
Regards.|||You can use the ROLLBACK option to force out existing users.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Piotr Strycharz" <Piotr.Strycharz@.antispam-account.com> wrote in message
news:d69ip4$m0$1@.nemesis.news.tpi.pl...
> Uzytkownik "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> said
:
>
> This command, however, won't allow change when connections exists (or are
> made) to database.
> Regards.
>