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.

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.

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.

ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK AFTER n

Let's assume I issue the following command: ALTER DATABASE [dbname] SET
SINGLE_USER WITH ROLLBACK AFTER 60.
Can someone tell me if the follow assumptions are correct:
1) SQL Server will wait 60 seconds for all open transactions to commit or
rollback before putting the database in single user mode.
2) Any transactions not committed or rolled back within 60 seconds will
automatically be rolled back after 60 seconds.
3) Any transaction started after the above statement is issued but is not
committed or rolled back within the 60 seconds gets rolled back.
Thanks - Amos.
On Tue, 26 Sep 2006 11:27:52 -0400, Amos Soma wrote:

>Let's assume I issue the following command: ALTER DATABASE [dbname] SET
>SINGLE_USER WITH ROLLBACK AFTER 60.
>Can someone tell me if the follow assumptions are correct:
>1) SQL Server will wait 60 seconds for all open transactions to commit or
>rollback before putting the database in single user mode.
It will wait for _AT MOST_ 60 seconds for open connections to close. If
there are no open connections, the database is put in single user mode
immediately. If all other connections disconnect after 20 seconds, the
DB will be single user after 20 seconds.

>2) Any transactions not committed or rolled back within 60 seconds will
>automatically be rolled back after 60 seconds.
Yes. And all connections get disconnected.

>3) Any transaction started after the above statement is issued but is not
>committed or rolled back within the 60 seconds gets rolled back.
Only from connections that are already connected to the database
[dbname]. New connections are not accepted - you'll get this error:
Msg 952, Level 16, State 1, Line 1
Database 'dbname' is in transition. Try the statement later.
Hugo Kornelis, SQL Server MVP
|||Hugo,
That was very helpful - thank you.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:hu6jh2ls21ckm5hbv7i8et0a6irud11lcv@.4ax.com...
> On Tue, 26 Sep 2006 11:27:52 -0400, Amos Soma wrote:
>
> It will wait for _AT MOST_ 60 seconds for open connections to close. If
> there are no open connections, the database is put in single user mode
> immediately. If all other connections disconnect after 20 seconds, the
> DB will be single user after 20 seconds.
>
> Yes. And all connections get disconnected.
>
> Only from connections that are already connected to the database
> [dbname]. New connections are not accepted - you'll get this error:
> Msg 952, Level 16, State 1, Line 1
> Database 'dbname' is in transition. Try the statement later.
> --
> Hugo Kornelis, SQL Server MVP

ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK AFTER n

Let's assume I issue the following command: ALTER DATABASE [dbname] SET
SINGLE_USER WITH ROLLBACK AFTER 60.
Can someone tell me if the follow assumptions are correct:
1) SQL Server will wait 60 seconds for all open transactions to commit or
rollback before putting the database in single user mode.
2) Any transactions not committed or rolled back within 60 seconds will
automatically be rolled back after 60 seconds.
3) Any transaction started after the above statement is issued but is not
committed or rolled back within the 60 seconds gets rolled back.
Thanks - Amos.On Tue, 26 Sep 2006 11:27:52 -0400, Amos Soma wrote:
>Let's assume I issue the following command: ALTER DATABASE [dbname] SET
>SINGLE_USER WITH ROLLBACK AFTER 60.
>Can someone tell me if the follow assumptions are correct:
>1) SQL Server will wait 60 seconds for all open transactions to commit or
>rollback before putting the database in single user mode.
It will wait for _AT MOST_ 60 seconds for open connections to close. If
there are no open connections, the database is put in single user mode
immediately. If all other connections disconnect after 20 seconds, the
DB will be single user after 20 seconds.
>2) Any transactions not committed or rolled back within 60 seconds will
>automatically be rolled back after 60 seconds.
Yes. And all connections get disconnected.
>3) Any transaction started after the above statement is issued but is not
>committed or rolled back within the 60 seconds gets rolled back.
Only from connections that are already connected to the database
[dbname]. New connections are not accepted - you'll get this error:
Msg 952, Level 16, State 1, Line 1
Database 'dbname' is in transition. Try the statement later.
--
Hugo Kornelis, SQL Server MVP|||Hugo,
That was very helpful - thank you.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:hu6jh2ls21ckm5hbv7i8et0a6irud11lcv@.4ax.com...
> On Tue, 26 Sep 2006 11:27:52 -0400, Amos Soma wrote:
>>Let's assume I issue the following command: ALTER DATABASE [dbname] SET
>>SINGLE_USER WITH ROLLBACK AFTER 60.
>>Can someone tell me if the follow assumptions are correct:
>>1) SQL Server will wait 60 seconds for all open transactions to commit or
>>rollback before putting the database in single user mode.
> It will wait for _AT MOST_ 60 seconds for open connections to close. If
> there are no open connections, the database is put in single user mode
> immediately. If all other connections disconnect after 20 seconds, the
> DB will be single user after 20 seconds.
>>2) Any transactions not committed or rolled back within 60 seconds will
>>automatically be rolled back after 60 seconds.
> Yes. And all connections get disconnected.
>>3) Any transaction started after the above statement is issued but is not
>>committed or rolled back within the 60 seconds gets rolled back.
> Only from connections that are already connected to the database
> [dbname]. New connections are not accepted - you'll get this error:
> Msg 952, Level 16, State 1, Line 1
> Database 'dbname' is in transition. Try the statement later.
> --
> Hugo Kornelis, SQL Server MVP

ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK AFTER n

Let's assume I issue the following command: ALTER DATABASE [dbname] SET
SINGLE_USER WITH ROLLBACK AFTER 60.
Can someone tell me if the follow assumptions are correct:
1) SQL Server will wait 60 seconds for all open transactions to commit or
rollback before putting the database in single user mode.
2) Any transactions not committed or rolled back within 60 seconds will
automatically be rolled back after 60 seconds.
3) Any transaction started after the above statement is issued but is not
committed or rolled back within the 60 seconds gets rolled back.
Thanks - Amos.On Tue, 26 Sep 2006 11:27:52 -0400, Amos Soma wrote:

>Let's assume I issue the following command: ALTER DATABASE [dbname] SET
>SINGLE_USER WITH ROLLBACK AFTER 60.
>Can someone tell me if the follow assumptions are correct:
>1) SQL Server will wait 60 seconds for all open transactions to commit or
>rollback before putting the database in single user mode.
It will wait for _AT MOST_ 60 seconds for open connections to close. If
there are no open connections, the database is put in single user mode
immediately. If all other connections disconnect after 20 seconds, the
DB will be single user after 20 seconds.

>2) Any transactions not committed or rolled back within 60 seconds will
>automatically be rolled back after 60 seconds.
Yes. And all connections get disconnected.

>3) Any transaction started after the above statement is issued but is not
>committed or rolled back within the 60 seconds gets rolled back.
Only from connections that are already connected to the database
[dbname]. New connections are not accepted - you'll get this error:
Msg 952, Level 16, State 1, Line 1
Database 'dbname' is in transition. Try the statement later.
Hugo Kornelis, SQL Server MVP|||Hugo,
That was very helpful - thank you.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:hu6jh2ls21ckm5hbv7i8et0a6irud11lcv@.
4ax.com...
> On Tue, 26 Sep 2006 11:27:52 -0400, Amos Soma wrote:
>
> It will wait for _AT MOST_ 60 seconds for open connections to close. If
> there are no open connections, the database is put in single user mode
> immediately. If all other connections disconnect after 20 seconds, the
> DB will be single user after 20 seconds.
>
> Yes. And all connections get disconnected.
>
> Only from connections that are already connected to the database
> [dbname]. New connections are not accepted - you'll get this error:
> Msg 952, Level 16, State 1, Line 1
> Database 'dbname' is in transition. Try the statement later.
> --
> Hugo Kornelis, SQL Server MVP

alter

ALTER DATABASE <bd> SET SINGLE_USER with rollback immediate
and then
ALTER DATABASE <bd> Set MULTI_USERGot it, thanks.
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:E1D27BFF-57E1-4DEC-8D29-D9DF90A799DD@.microsoft.com...
> ALTER DATABASE <bd> SET SINGLE_USER with rollback immediate
> and then
> ALTER DATABASE <bd> Set MULTI_USER
>