Set a filegroup to readonly I have a set of dimensions which are updated only during specific times.

I have moved these tables to a specific filegroup named Dimensions. I wanna keep this filegroup as readonly. When ETL runs, it will set the filegroup to read-write, run, then set it back to readonly.

But when I run ALTER DATABASE MYDB MODIFY FILEGROUP Dimensions READ_ONLY, I get the message Database state cannot be changed while other users are using the database 'MYDB'.

What's the least intrusive way of avoiding new transactions, waiting current ones to finish, then run this command? Is there a way to make a DB single user for some time, or make it offline, whatever?

UPDATE: I'm basing myself on the article The Read Uncommitted Isolation Level:


  For scenarios that demand the very highest levels of consistency guarantees, serializable remains the only safe choice. For performance-critical operations on read-only data (for example, large databases that are effectively read-only between ETL windows), explicitly setting the database to READ_ONLY can be a good choice as well (shared locks are not taken when the database is read only, and there is no risk of inconsistency).
Usually people just want to kick all users out right away, so they use:

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;


In your case, if you want all existing activity to continue until you get your turn, this will wait indefinitely:

ALTER DATABASE MyDB SET SINGLE_USER;
-- alter filegroup code here
ALTER DATABASE MyDB SET MULTI_USER;


If you want to give some time but not wait forever, you can say something like:

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK AFTER 300 SECONDS;


Which will wait 5 minutes and then roll back any remaining transactions. RESTRICTED_USER might be an option for you as well, in case SINGLE_USER allows agent or other background threads from taking your connection.

I don't know of any way to set the state of the database so that existing transactions are allowed to finish and no new connections (or new transactions from existing connections) are possible.

Some information here:


Set a Database to Single-user Mode (MSDN)
ALTER DATABASE SET Options (MSDN)