Post reply

Detach_Attach Db

  • 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

  • January 23, 2007 at 10:31 am

    #684994

    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

  • January 23, 2007 at 10:37 am

    #684999

    Hi victord,

    Found this in the sqlservercentral scripts

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

    Rgds

    Ian G

  • January 23, 2007 at 10:38 am

    #685000

    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

  • January 23, 2007 at 10:49 am

    #685004

    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

  • January 23, 2007 at 12:16 pm

    #685025

    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

  • January 23, 2007 at 12:17 pm

    #685026

    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

     

  • January 24, 2007 at 5:05 am

    #685158

    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

  • January 24, 2007 at 7:31 am

    #685204

    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

  • January 24, 2007 at 8:18 am

    #685236

    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.

     

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

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