Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ann06
Posting Yak Master
171 Posts |
 Posted - 2008-06-25 : 07:58:07
|
| hi folks,can anybody explain to me please what does this option mean (with rollback immediate) if used here ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATEthanks for the supports |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-25 : 08:15:48
|
| did you had a look at ALTER DATABASE in MSDN/books online?http://technet.microsoft.com/en-us/library/ms174269.aspx |
 |
|
|
ann06
Posting Yak Master
171 Posts |
Posted - 2008-06-25 : 08:32:29
|
| i read it but didnt know it will rollback what? and why to set to immediate? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-25 : 08:41:34
|
quote:
Originally posted by ann06
i read it but didnt know it will rollback what? and why to set to immediate?
this is what MSDN statesSINGLE_USERSpecifies that only one user at a time can access the database. If SINGLE_USER is specified and there are other users connected to the database the ALTER DATABASE statement will be blocked until all users disconnect from the specified database. To override this behavior, see the WITH <termination> clause. The database remains in SINGLE_USER mode even if the user that set the option logs off. At that point, a different user, but only one, can connect to the database.Before you set the database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. To view the status of this option, query the is_auto_update_stats_async_on column in the sys.databases catalog view. If the option is set to ON, perform the following tasks:Set AUTO_UPDATE_STATISTICS_ASYNC to OFF.Check for active asynchronous statistics jobs by querying the sys.dm_exec_background_job_queue dynamic management view.If there are active jobs, either allow the jobs to complete or manually terminate them by using KILL STATS JOB.ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATESpecifies whether to roll back after the specified number of seconds or immediately. |
|
|
|
ann06
Posting Yak Master
171 Posts |
Posted - 2008-06-25 : 09:50:57
|
| my friend the one in blue is clear what am not getting and its not well explained is the rollback option why there is rollback?thank you |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-06-25 : 11:35:38
|
| Though it wasn't explicitly stated, I always assumed it meant that once the ALTER database command was issued, any open transaction would be rolled back. You can't have the DB in single_user mode if other users have open transactions, right? If you specified an integer for SECONDS then sql would not allow any more new transactions and it would wait for that many seconds for any existing open transactions to commit. Afterwards, any existing transactions still open would be rolled back.Be One with the OptimizerTG |
|
|
|
ann06
Posting Yak Master
171 Posts |
Posted - 2008-06-26 : 02:02:07
|
| mmm thank you very much now i got it :Dthank again |
|
|
|
|
|
|
|
|
rollback immediate in alter database ?