January 24, 2006 at 11:41 am
Dear everyone,
Please help me to writing the code, how can i change my database to sigle user only and then put back to multiuser, after performing some maintenance.
please give me the code...
Thanks
Nomi
January 24, 2006 at 12:12 pm
You have to make sure that there are not any users in the DB first. If there are, you need to kick them out or run a script to do so. If you need a script to do this, search the forums, I know that I've seen a few. After that, you can run sp_dboption from the Master DB to set your user DB to single user. Example: sp_dboption 'DatabaseName', SINGLE_USER, true. The script you are running that contains this call to sp_dboption must be run from the Master DB. If you have your QA session connected to your user database, you cannot set it to single user. Hope this helps.
January 25, 2006 at 3:00 am
BOL recomends you use ALTER DATABASE instead of sp_dboption (supported for backward compatability).
ALTER DATABASE <DB Name> SET SINGLE_USER
I don't think you have to be in master?
January 25, 2006 at 9:07 am
Thanks but i have tried but it failed and show the message like:
there are some users logg on with that database..
but i know there is no user using that database when i use this option. what would be the next step.??
Thanks
Noman
January 25, 2006 at 9:26 am
Whichever method you choose, you cannot be logged into the database that you are trying to set to single user mode. I said use master, not necessarily because you have to be in master, but becasue you cannot be in the user database that you are setting to single user. The message you are getting is because you are in the database. Make sure that when you open your QA session, you are not in your user database.
January 25, 2006 at 9:30 am
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply