Post reply

Error: Database is already open and can only have one user at a time

  • July 15, 2019 at 12:56 pm

    #3662964

    Any idea how to resolve this error? seemingly a nightly process loads data into my joebloggs_DB database, its then copied from jobbloggs_DB database to LIVE DB. Issue is sometimes the process fails with the following error:

    Error: Database 'JoeBloggs' is already open and can only have one user at a time.

    It looks like the database JoBloggs is put into single user mode and then sometimes has difficulties taking it out of single user mode into multi user mode.

    To resolve this issue manually in a live situation I would check and see what process has locked the DB and kill it.

    Use [master]

    SELECT request_session_id

    FROM   sys.dm_tran_locks

    WHERE  resource_database_id = DB_ID('Joebloggs_DB')

     

    However this is happening intermittently at night. Is there any way to ensure it does not occur?

    thanks in advance

  • July 15, 2019 at 1:10 pm

    #3663000

    Why is the database being put into Single_user mode?

    Gethyn Elliswww.gethynellis.com

  • July 15, 2019 at 1:42 pm

    #3663019

    Yeah good question. It must be part of some job than runs which I do not have visibility of yet.  In the error log I see the database put into single user mode, then the next message is an error stating it cannot backup the database.

  • July 15, 2019 at 1:49 pm

    #3663023

    caz100 wrote:

    Yeah good question. It must be part of some job than runs which I do not have visibility of yet. 

    it can be found out using default trace - see replies of Lowell & Gail Shaw :

    https://www.sqlservercentral.com/forums/topic/db-randomly-sets-in-single-user-mode

  • July 15, 2019 at 2:01 pm

    #3663034

    So as part of some data load ...the DB in question is put into single user mode. I presume this is to ensure no more data is added to the database prior to it being backed up and copied to another database.

    @GRE -  I am finding out if this step of putting the DB into single user mode can be removed. Or have you any other suggestion?

  • July 15, 2019 at 2:12 pm

    #3663046

    Yeah it is being purposely put into single user mode as part of a nightly refresh, the backup happens directly after the single user mode has been set and it then fails. It only fails intermittently though, not every night. So I suspect on some occasions some other process has taken exclusive access to the DB.

  • July 15, 2019 at 3:12 pm

    #3663059

    Best to avoid making the database single-user in the first place.  But if you can't avoid it, you might try this:

    ALTER DATABASE MyDatabase SET MULTI_USER WITH ROLLBACK IMMEDIATE;

    John

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

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