Menu
Before changing database schema I issue:
ALTER DATABASE SET RESTRICTED_USER
On completion:
ALTER DATABASE SET MULTI_USER
I understand that a running transaction will be permitted to continue until completion.
Q: Is there any way to wait till all regular users are off the database?
Q: Can the regular users issue more transactions? Can they continue working until disconnected from the server?
963
So SET RESTRICTED_USER will wait until all transactions have completed before taking affect. Once in place, regular users cannot issue further transactions, only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles can connect to the database.
This mode is generally used in organizations so that multiple users can access it simultaneously.
AutoClose is a database option or setting – set on a database by database basis (meaning that it can't be controlled at the server level). According to Books Online: When set to ON, the database is shut down cleanly and its resources are freed after the last user exits.
The ROLLBACK IMMEDIATE command tells the SQL Server that if it can't complete the command right away, then the other pending transactions should be rolled back. The NO_WAIT command has the opposite effect as ROLLBACK IMMEDIATE. If the ALTER DATABASE transaction can't be completed then that transaction is terminated.
From SQL Server Books Online
http://msdn.microsoft.com/en-us/library/aa933082(SQL.80).aspx
If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely, until the transactions commit or roll back on their own.
So SET RESTRICTED_USER will wait until all transactions have completed before taking affect.
Once in place, regular users cannot issue further transactions, only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles can connect to the database.
69
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
