Skip to main content

Posts

Showing posts from May, 2007

Termination option in ALTER DATABASE

This is tried on Yukon.Assume that User1 is connected to the database MyDb. User2 executes the following statement:ALTERDATABASE MyDbSET RESTRICTED_USERWITHROLLBACK IMMEDIATESince Termination option is ROLLBACK IMMEDIATE, it disconnects User1 from database and changes the database to restricted_user mode.Now, let us consider similar scenario. User1 is connected to MyDb. User2 executes the following statement:ALTERDATABASE MyDbSET MULTI_USERWITHROLLBACK IMMEDIATEThis statement fails and returns error as shown below:Msg 1222, Level 16, State 28, Line 1Lock request time out period exceeded.Msg 5069, Level 16, State 1, Line 1ALTER DATABASE statement failed.That means termination option has no effect when database is switching to multi_user mode. As long as users are connected to the database, above statement fails to execute.I’ve no idea about the logic behind this design. Kindly update me if you know.