Set a filegroup to readonly
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)