I need to rebuild the indexes in two very large (almost 750 table) database but want to prevent users from accessing the database while this happens. Is there a state I can put the database in that prevents the users from accessing it but still allows me to rebuild the indexes? For reference I plan on the script provided at http://instadba.com/quick-script-to-defragment-your-sql-server-indexes/ to do the rebuild.
Depending on the edition of sql server, if you are using Enterprise edition then you can do index rebuild as online operation.
Also, the script that you have referenced just blindly does rebuild of indexes which is a horrible idea (Blindly rebuild all indexes and update stats ??) Instead use a much proven and intelligent script.
I would not put the database in single user mode as alluded by Scott as if somehow e.g. sql agent connects to the database, then its difficult to kill the connection unless you connect using DAC or if
AUTO_UPDATE_STATISTICS_ASYNC is ON then background thread will grab the connection and you will be out of luck !
Instead put database in restricted mode - so that only users with dbo rights on database allowed (e.g. db_owner, dbcreator, sysadmin). This assumes that your end users are not allowed to connect as db_owner or sysadmin.
Remember that Index rebuild is an offline operation (unless you are using Enterprise edition with REBUILD = ONLINE option).
Command to put database in restricted user mode & back to multi user :
alter database databasename set restricted_user with rollback immediate; -- rebuild / reorg indexes using Ola's scripts ALTER DATABASE databasename SET MULTI_USER with rollback immediate;
In the past when I've wanted to restrict access to a database, I did use RESTRICTED_USER mode. However, that doesn't work if your users are members of the db_owner role. I realize this is not a recommended role for users to be in, but sometimes this is what you end up with. I standardized to disabling the login during the maintenance windows so that I didn't have to worry about users with elevated access.
ALTER LOGIN User1 DISABLE;
Don't forget to enable them when you are done! Example:
ALTER LOGIN User1 ENABLE;