ALTER DATABASE WITH ROLLBACK times out

If I execute the command ALTER DATASE SET MULTI_USER WITH ROLLBACK IMMEDIATE and there are any connections to the database, the command fails with a "Lock request time out period exceeded." message. If I use SET RESTRICTED_USER, the command succeeds with the following message: "Nonqualified transactions are being rolled back. Estimated rollback completion: 100%." This seems to be a bug.

What's even more annoying is that in SQL 2005 I could set MULTI_USER (using sp_dboption) even if there were active connections.I meant to say that in SQL 2000, the SET MULTI_USER option worked even if there were connections.|||I am experiencing the same issue and this causes major problems/delays with our batch cycle. Is thete any way to take a DB out of RESTRICTED mode without terminating connections like SQL 2000 did?|||

Dug around our bug database and found this is a known issue with SQL 2005. According to bug report the workaround is:

ALTER DATABASE pubs SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE pubs SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Have not tested this.

|||

Yes, the workaround works. It creates a slight window where people who should be able to connect to the database can't, but on the scale of incompatibilities introduced by SQL 2005, it's pretty minor.

A better question is why I can't go from restricted to multi_user without disconnecting people. OK, I know the answer - it was easier to code - but it really is a loss of functionality from earlier versions. Another quibble is that it really should be called WITH DISCONNECTION_IMMEDIATE, since that's what it really does.