locked
"Msg 924, Level 14, State 1" error during running script in database in single user mode for SQL Server 2005 RRS feed

  • Question

  • I have SQL  script in this format...

    1. set database in single user mode WITH ROLLBACK IMMEDIATE
    2. execute some DML and DDL statements
    3. set the database back in multiuser mode

    Now what happens is, during step#2, I'm getting this error...
    Msg 924, Level 14, State 1, Server <servername here>, Procedure <Procedurename here>, Line 48
    Database <database name> is already open and can only have one user at a time.

    First, as the database is in single user mode, and this is the only connection open, it should not allow anyone else to connect. And surely, the error should be thrown for other connections!! We shouldn't get this error at least in this connection. And interstingly, the procedure name in the error is not being executed by step2. It's being ALTERed instead.

    Also note that there is a windows service which keeps on trying to connect to this database. So when we put it in single user mode, the service will be denied the connection.

    I searched a bit and found an issue in SQL 7...
    http://support.microsoft.com/kb/241363

    And I checked and found that there really were cursors in step 2. So, even though the bug is reported only for SQL 7 and not for SQL 2005, I still went ahead and replaced the cursor with another equivalent WHILE loop.

    But that too didn't work and we are stuck with this error. And it's not happening consistently. The <procedure name> will be different each time.

    From the look of it, it seems when the windows service tries a connection, the error of 'can have only one connection' is thrown in the already open connection and not to windows service.

    Anyone knows more about this?

    Also note that in the step#2, there are some dynamic sql statements being executed with sp_executesql if that's of any use. Also, there are some statements to change the job steps which are referring to msdb.
    • Moved by Tom Phillips Friday, June 11, 2010 1:41 PM Possibly better answer from TSQL forum (From:SQL Server Database Engine)
    Friday, June 11, 2010 9:29 AM

All replies

  • You need to already be using the database when you:

    ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    That way you have captured the connection, since you will not kick yourself out the database with the switch.  Even a few microseconds of delay will give some other process the opportunity to grab the single connection.  You symptom says that something is getting in ahead of you. 

    So:

    USE dbname
    ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    Exec your DDL and DML code
    ALTER DATABASE dbname SET MULTI_USER

    If at any time during this process you "USE anotherdb" then you will likewise risk losing the lock on "dbname".  You can, of course, execute code that affects other databases as long as you qualify it, such as: 

    EXEC anotherdb.dbo.procname

    RLF

     

    • Proposed as answer by Kalman Toth Monday, September 23, 2013 3:36 PM
    Friday, June 11, 2010 3:08 PM
  • Yes. I'm already using the same database when I set it to SINGLE USER.

    I'm not doing USE anotherdatabase anytime during the script execution.

    By the way, I've raised a service request at the "contact support" page. But I haven't received any response till now. Worst, I can not find a place where I can go and check the status of it with the service request number I have got. Can you please point me to the right place..of course if you know it?

    Monday, June 14, 2010 5:43 AM
  • Did you ever manage to resolve this?  I'm having exactly the same problem on SQL Server 2008 R2. 

    In my case we have a Visual Studio Database Project and are deploying changes.  It generates and runs a change script.  Part way through altering a bunch of triggers it will fail with MSG 924.  It fails on a different trigger each time.  Many triggers have been succesfully altered before the failure.

    I've tried profiling and can see that it's the same spid executing all of the change scripts.  Unlike the original questioner, we don't have another service that is connecting to this database. 

    I'm at a loss to explain what's happening.

    Cheers,

    Darren

    Wednesday, August 21, 2013 5:47 AM
  • I am having a similar issue. Did you resolve it?

    Saturday, September 21, 2013 2:56 PM
  • This thread is over 3 years old and the OP has no recent activity.  In general, it is not very useful to post a "me too" response to an old thread - which is the idea you should have drawn from the previous "me too" response in August.  Before you start your own thread with relevent information, I suggest that you use the profiler to help isolate the issue.  Single user mode only guarantees a single connection; it does not guarantee that a script which initially captures the database does not lose access if it changes the current database (allowing another connection to then capture it) while doing something else.
    • Proposed as answer by Kalman Toth Monday, September 23, 2013 3:35 PM
    Monday, September 23, 2013 1:36 PM
  • Hi,

    Before you set the database to SINGLE_USER, verify that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When this option is set to ON, the background thread that is used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode.

    Reference:

    http://technet.microsoft.com/en-us/library/ms345598.aspx

    Thanks

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    • Proposed as answer by Kalman Toth Monday, September 23, 2013 3:37 PM
    Monday, September 23, 2013 2:29 PM
  • If you are still facing “SQL database error 924” then try T-SQL commands in SSMS (answer suggested by @SQLWORKS). If this method doesn’t work then try other methods:

    1. DBCC CHECKDB Repair Options

    Use these commands:
    DBCC CHECKDB(‘xyz‘,REPAIR_REBUILD)

    2. Restore the Database from Backup
    If you have an updated backup then restore the database from backup file. 

    3. Check SQL Server Services
    Restart your SQL Server Services.

    If all these solutions doesn’t work properly then try these references:
    https://community.spiceworks.com/topic/751250-how-to-recover-sql-server-database-from-corrupt-mdf-file
    https://www.stellarinfo.com/sql-database-repair.php
    Tuesday, January 8, 2019 3:04 AM