3

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

Improve this question

1 Answer 1

Reset to default
3

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:

Improve this answer
9
  • Interesting, gonna try that. I don't really wanna drop other connections, it'd be troubling if other ETL wouldn't run. By doing ALTER DATABASE MyDB SET SINGLE_USER, will it lock the DB and make other transactions wait, and mine will be able to run and then release lock back so they can start?
    – Hikari
    Mar 24, 2016 at 2:26
  • No, it won't have any way to prevent other new transactions from starting until all transactions clear.
    – Aaron Bertrand
    Mar 24, 2016 at 3:14
  • Thanks a lot. I have a query that shows current operations and their session ID, but I'd need to read their query text to see what they do. Would there be a way to see those operations that are using a specific DB and stopping the alter table from running?
    – Hikari
    Mar 24, 2016 at 15:30
  • I just ran the alter database command. With the rollback flag it worked. But the DB wasn't locked to my user. It seems that when my transaction ends, any other transacion can grab the DB and keep it. That's VERY troubling, because if a transaction grabs the DB while in single user mode, mine won't be able to complete and set it back to multi user. Is there any way to define it must be single user and only mine?
    – Hikari
    Mar 24, 2016 at 15:34
  • You can set to restricted_user first. Your end users aren't dbo/sysadmin right?
    – Aaron Bertrand
    Mar 24, 2016 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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