Database Update 3

[wg26]

Hi Everyone:

I need to modify my database, such as adding some columns and moving some data among tables...I have written T_sql code to do this...my question is that when I am running my sql code, I need to stop all current connections to my database and stop user from reading from or writing into the database and also disallow any new connection to the database until my sql code running is done. I am wondering if there is a sp or any way to do this? Can anyone please give me some suggestions how I should do it? Thanks alot...

 

[kudithipudi]

Am not sure if this is the correct answer for your question. But I think you can bring SQL server to a single user mode by starting it with a "-m" switch.

- V.

 

[wg26]

Thank you Kudithipudi for you reply...

I have already checked that option. I think it does not fit my situation. "-m" switch is used for when you just start your database. But my situation is that my database is already started and running in multi_user mode.

 

[sunila7]

Hi,

Have a look at this link

http://vyaskn.tripod.com/administration_faq.htm#q16

http://vyaskn.tripod.com/code/sp_dboption2.txt

This is an SP written by Narayana Vyas Kondireddi which kills all the current connections and can be used to set the db to single user mode...

hope it helps

Sunil

 

[sunila7]

Hi,

Alternatively, if u r using SQL 2000 u can use ALTER DATABASE command to do it...
Have a look at msdn on syntax....

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_4e5h.asp

Hope it helps

Sunil

 

[wg26]

Thanks Sunila7.

I have tried to change the connection to my database to Single User Mode...but I could not change it by using alter database command when there are multi connections currently connecting to the database. Can anyone please give me more suggestions? Thanks...

 

[SQLSister]

That script Sunila gave you in her first post should work.

If you are in a client server environment, you can make a general announcement as to when the database is going down(or do it after hours if possible) and get most users to log out them selves. Make sure that it is a good time in the business cycle for you to take the system down. You wouldn't want to take the products database down for maintenance and have a problem and take several hours to get it back up just when the users need the information for a multimillion dollar proposal they are preparing which is due the next day. Well not if you value your job, you wouldn't.

In a web environment this is harder, but you could put a notice on your home page scheduling the change for some low useage time like say 4:00 am. Then hopefully most users will already be off.

Letting users know ahead of time that the system will be down for routine maintenance is every bit as important as knowing how to kill user connections to go to single user mode.