Post reply

SINGLE_USER MODE ISSUE

  • January 26, 2012 at 3:19 am

    #251983

    I am setting a db to SINGLE_USER mode in order to then rename some databases:

    -- Set single User

    alter database DB1 set single_User with rollback immediate

    alter database DB2 set single_User with rollback immediate

    exec sp_renamedb 'DB1', 'DBPre' --> Fails here I think

    exec sp_renamedb 'DB2', 'DB1'

    exec sp_renamedb 'DBPre', 'DB2'

    -- Set multi User

    alter database DB1 set multi_user with rollback immediate

    alter database DB2set multi_user with rollback immediate

    However, the job fails where indicated with error 'DB1' is already open and can only have one user at a time". But I am the user!!??

    This is being run from a SQL job as TSQL. Should the session I'm running be the only one allowed to then rename etc and not allow other connections?

    Any help greatly appreciated.

    Thanks

    Steve

  • January 26, 2012 at 3:34 am

    #1438652

    Are you connected to the DB1 database at the time, or another database on the server?

    Theoretically, I guess that another user could steal the 1 connection.

    Also, have you read this from the BOL article on setting SINGLE_USER:

    Before you set the database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. To view the status of this option, query the is_auto_update_stats_async_on column in the sys.databases catalog view. If the option is set to ON, perform the following tasks:

    Set AUTO_UPDATE_STATISTICS_ASYNC to OFF.

    Check for active asynchronous statistics jobs by querying the sys.dm_exec_background_job_queue dynamic management view.

    If there are active jobs, either allow the jobs to complete or manually terminate them by using KILL STATS JOB.

    http://msdn.microsoft.com/en-us/library/bb522682.aspx

  • January 26, 2012 at 3:47 am

    #1438659

    Yes I'm connected to DB1, but I'll take a look at your option.

    BTW, this works fine in SSMS, it's only when the code is run from a SQL job step??

  • January 26, 2012 at 4:50 pm

    #1439089

    I would say, simply add "USE master" on top of your code in the job and it must work, provided your db options are correct as mentioned above.

    if this is in a job then why you are connected to the db?

    please try.

  • January 27, 2012 at 3:28 am

    #1439240

    Sorry yes I am connected to master when I execute this code.

    I also checked the AUTO_UPDATE_STATISTICS_ASYNC option and it was already set to OFF anyway.

    I still don't understand how another connection can jump in after this statement:

    alter database DB2 set single_User with rollback immediate

    Thanks

    Steve

  • January 27, 2012 at 3:43 am

    #1439244

    Try sticking something like this between setting the DB as single user and renaming it:

    SELECT * INTO tempdb.dbo.temp_connected_sessions FROM sys.sysprocesses

    WHERE dbid=DB_ID('DB1')

    Then check the table after it's failed. Should give a clue as to which session is grabbing the one connection

Viewing 6 posts - 1 through 5 (of 5 total)

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