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).