Single User 1

[BoydMT]

I need to put a database into single-user mode so I can run DBCC CHECKDB with repairs.
This database is a distributor for replication.
How can I do this?

Thanks,
Mark

 

[mikeyb540]

Right click on the database and under the Options tab there is a check box called Single User.

Or

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

 

[BoydMT]

Thanks for the response.
I don't think I was totally clear in my initial request. When I try putting the database in 'single user' mode, I receive an error message stating the option can't be changed while another user is in the database.
I'm pretty sure there is no one else in this database, but I have it set up for replication with another server. Is this why I am getting the error message?

I looked at the article on sysdatabases, and it seems I can change the 'status' field to 4096 to make the database 'single user'. This field currently has a value of 12. Any idea what this means?

Thanks,
Mark

 

[JamesLean]

Use this command to put the DB into single user mode:

ALTER DATABASE mydb
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

This will disconnect any existing user connections and rollback any outstanding transactions.

--James

 

[BoydMT]

James -
Thanks for the code.
I am trying to use your code for single user mode, and am getting the following error: Incorrect syntax near the keyword 'SET'. Any ideas?
I am using SQL7.

Thanks,
Mark

 

[mikeyb540]

James that works on SQL2000

Check who is connected to your DB:
sp_who

Look at the SPID from SP_WHO then
KILL spid

Turn to Single:
sp_dboption 'YOUR_DB_NAME', 'single user', 'on'

To turn it back
sp_dboption 'YOUR_DB_NAME', 'single user', 'off'

 

[BoydMT]

Thanks mikeyb540.
This is exactly what I was looking for.

Mark

 

[mikeyb540]

No Problem.

Glad I was of help