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.

  • Just take a maintenance window and run index rebuild during that window and users should be informed that under this windows it would be application downtime. – Shanky Aug 16 '16 at 18:31
  • @Shanky I can't trust the users to not do that. Last outage we had we sent 3 notifications, including one 15 minutes before it started and users still tried to access the system. – Matthew Verstraete Aug 16 '16 at 18:38
  • Is this relevant? stackoverflow.com/questions/11989526/… – MonkeyZeus Aug 16 '16 at 20:44
  • Maybe I'm too mean, but I feel like the database state is irrelevant, this is what the firewall is for. Down for maintenance means down. If you have physical access to the server you can unplug the sucker to be on the safe side ;-) – Steve Jessop Aug 16 '16 at 23:21
USE master;
USE YourDatabase;
--Run index maint scripts...
| improve this answer | |
  • 2
    If auto_update_stats is ON then a background thread will grab the connection. I would put the database in restricted_user mode. – Kin Shah Aug 16 '16 at 19:00
  • 1
    This link references a method to make sure the auto_update_stats gets disabled before putting the database in single user mode and then re-enables it after the maintenance is done. msdn.microsoft.com/en-us/library/ms345598.aspx#Restrictions – Scott Hodgin Aug 16 '16 at 19:14

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;
| improve this answer | |

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.



Don't forget to enable them when you are done! Example:

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.