locked
Does ALTER DATABASE SET RESTRICTED_USER block until connections are killed? RRS feed

  • Question

  • Hi, we have a database restore script that runs an ALTER DATABASE [Database] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE command.  The next statement is a WAITFOR DELAY with 30 seconds.  This is to ensure any non compliant connections are killed before continuing.

    However, I'm wondering if the WAITFOR is actually necessary?  Would the ALTER DATABASE command not block until all connections have been killed?

    Thanks,
    Tom

    Wednesday, October 27, 2010 3:34 PM

Answers

  • I think WITH ROLLBACK IMMEDIATE  kills all connections, means you do not need WAITFOR command. I rarely use SET RESTRICTED_USER  , can you explain what you are trying to achive?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, October 28, 2010 8:30 AM
  • I agree with Uri. WITH ROLLBACK IMMEDIATE is a synchronous operation which means that it has to wait for all connections closed before completion.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, October 28, 2010 10:01 AM

All replies

  • I think WITH ROLLBACK IMMEDIATE  kills all connections, means you do not need WAITFOR command. I rarely use SET RESTRICTED_USER  , can you explain what you are trying to achive?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, October 28, 2010 8:30 AM
  • I agree with Uri. WITH ROLLBACK IMMEDIATE is a synchronous operation which means that it has to wait for all connections closed before completion.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, October 28, 2010 10:01 AM