Detach_Attach Db

Question

Post reply

Detach_Attach Db

victord

Valued Member

Points: 71
More actions
January 23, 2007 at 9:02 am

#176492

Hello,
How else can i put a database in single user mode appart from the one below.
Every time i try to do this it comes up with error saying the database cannot be put in single user mode cause it is in use.
Please check the code below to see if i am doing something wrong.
use Sostenutotest
EXEC sp_dboption 'Sostenutotest ', 'single user', true
go
EXEC sp_detach_db 'Sostenutotest', 'true'
go
EXEC sp_attach_db @dbname = N'Sostenutotest',
@filename1 = N'X:\Microsoft SQL Server\MSSQL\Data\Sostenutotest\Sostenutotest.MDF',
@filename2 = N'X:\Microsoft SQL Server\MSSQL\Data\Sostenutotest\Sostenutotest_1.MDF',
@filename3 = N'X:\Microsoft SQL Server\MSSQL\Data\Sostenutotest\Sostenutotest_2.MDF',
@filename4 = N'X:\Microsoft SQL Server\MSSQL\Data\Sostenutotest\Sostenutotest_3.MDF',
@filename5 = N'X:\Microsoft SQL Server\MSSQL\Data\Sostenutotest\Sostenutotest_Log.LDF'
EXEC sp_dboption 'Sostenutotest ', 'single user', false

vdavid

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

humbleDBA Hall of Fame Points: 3540 More actions · Answer 1

Hello victord,

You need to kill all but your own spid to then be able to set it to 'single user' mode.

I haven't got the script handy, but I think there must be something in sqlservercentral's script resources somewhere that will allow you to cycle through all those you need to 'kill.' You would run this script in advance of the script you want, above. Something like:

1. find users in db

2. kill the spid's that map to these users (be careful with system accounts)

3. set db in 'single user' mode

...

Hope this helps.

Ian G

humbleDBA Hall of Fame Points: 3540 More actions · Answer 2

Hi victord,

Found this in the sqlservercentral scripts

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=30

Rgds

Ian G

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Here are some things to consider when putting a database into single user mode.

1. It's not REALLY users. It's connections. One user with two or more connections is not single user mode.

2. If you have Enterprise Manager and Query Analyzer open - that is two connections. Even if you open QA from Enterprise Manager.

3. SQL Server Agent/Jobs. Jobs are users. Good advice is to stop/disable SQL Server Agent service while you are putting the database into Single User mode. Just remember to enable/start it once you are finished.

-SQLBill

humbleDBA Hall of Fame Points: 3540 More actions · Answer 4

Ok, SQLBill is right, sorry (using the wrong terms), it's actually process IDs that you'll kill, and yes, you need to make sure that you have just one Process ID connected to the DB you want to set to 'single user' mode . I would normally do this via QA and make sure the EM is not connected to that DB.

No heard the one about shutting down SQL Agent service, and would be careful of this if you have backup jobs, replication, etc., running on the same box for other DBs. Would disable all jobs for that DB though

Rgds

Ian G

gts31 Ten Centuries Points: 1294 More actions · Answer 5

If you are using SQL Server 2000 or later you can use the ALTER DATABASE command. USE the database and then issue ALTER DATABASE SET SINGLE_USER. Then USE another database (like master) and run sp_detach_db.

George

dclark SSC Eights! Points: 843 More actions · Answer 6

If you just want to kick everyone out of the database before detaching it, use this command (in SQL 2000)

alter database Sostenutotest 
set offline with rollback immediate

victord Valued Member Points: 71 More actions · Answer 7

Thanks all for your input. The last one that is the set offline with rollback immediate. But what does it really mean and is that all i need to do. I mean do i have to do anything else after i have detached and attached the database.

vdavid

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 8

It just kicks everyone out of the database so you can issue your dettach/attach commands. When you attach, you might have to bring the db online with the alter database command.

Tom

dclark SSC Eights! Points: 843 More actions · Answer 9

It kicks everyone out of the database and sets the database offline so that no one can connect to it. You can read about it in SQL Server Books Online.

When you attach the database, it will be online.