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

    Вопрос

  • Hi,

    I have created a dynamic sql which takes a database in single user mode, performs log backup, sets the database in bulk recovery mode, rebuilds and reorganizes required indexes, sets the db in full recovery mode, takes log backup again and finally sets the db in multi user mode.

    Sometimes the process just run fine but sometime I get an error: Database is already open and can only have one user at a time. Many indexes just gets rebuilt fine but it just stops at some point raising the error.

    I am not finding a clue on why is this happening intermittently. I am not changing sessions or trying to open new connection during the query execution. Does SQL Server automatically does that ?

    Any suggestions would be hightly appreciated.

    Thanks,

    12 января 2012 г. 5:00

Ответы

  • I don't think it's the CHECKPOINT process. More likely it is an Agent job that hijacks the database. It could also be a user who is trying to get in or some other background processes.

    You can keep users out by also setting RESTRICTED_USER for the database; this setting disallows anyonw who is not in the db_owner role to connect to the databaes. Since Agent jobs often runs as dbo, it will not stop them.

    I the best solution is to never to "USE master", but execute everything that needs to be done in master through dynamic SQL, this way:

    EXEC master..sp_executesql @sql


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    6 июня 2012 г. 13:54

Все ответы

  • Nadir,

    Try executing this query to confirm, before and after executing your dynamic sql.

    select db_name(dbid) 'DbName',count(*) 'No of Users' from master..sysprocesses where spid > 49 group by db_name(dbid)
    

     


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    • Предложено в качестве ответа Sandip Shinde 12 января 2012 г. 6:05
    • Помечено в качестве ответа KJian_ 19 января 2012 г. 2:06
    • Снята пометка об ответе KJian_ 19 января 2012 г. 2:06
    • Отменено предложение в качестве ответа Kalman TothModerator 19 января 2012 г. 14:07
    • Помечено в качестве ответа Kalman TothModerator 24 января 2012 г. 7:05
    • Снята пометка об ответе nadirsql 27 января 2012 г. 16:39
    12 января 2012 г. 5:19
  • Hi,

    I have created a dynamic sql which takes a database in single user mode, performs log backup, sets the database in bulk recovery mode, rebuilds and reorganizes required indexes, sets the db in full recovery mode, takes log backup again and finally sets the db in multi user mode.

    Sometimes the process just run fine but sometime I get an error: Database is already open and can only have one user at a time. Many indexes just gets rebuilt fine but it just stops at some point raising the error.

    I am not finding a clue on why is this happening intermittently. I am not changing sessions or trying to open new connection during the query execution. Does SQL Server automatically does that ?

    This is not a safe approach.

    What if a SQL Server Agent job starts up in the middle of your process? Probably that is what's happening.

    Use the Maintenance Plan Wizard to create a database Maintenance Plan. 


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Помечено в качестве ответа Kalman TothModerator 24 января 2012 г. 7:04
    • Снята пометка об ответе nadirsql 27 января 2012 г. 16:39
    • Изменено Kalman TothModerator 15 октября 2012 г. 20:28
    19 января 2012 г. 14:07
    Модератор
  • This is more general problem.

    We have a few production servers with MS SQL Server 2005.

    A few times in a year we prepare update sql script for the servers.

    The script begins with the command

    ALTER DATABASE [bd_sh] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

    and ends with the command

    ALTER DATABASE [bd_sh] SET  MULTI_USER WITH ROLLBACK IMMEDIATE

    to prevent changes by other users in the database during the update.

    Between these lines we have statements that create new tables, add new columns, drop/create functions and procedures, set permissions, change data and so on.

    And very often an error occurs "Database 'OurDB' is already open and can only have one user at a time "

    This error occurs at random positions in the script, and DB context sometimes switches to master database so some portions of the script are executed against master.

    Someone in the forums suggested that this problem is caused by CHECKPOINT background process.

    Is this possible? And if so, how to prevent this? Is it possible to disable this process or delay it during our update?

    I look forward to any help.

    Thanks, Nikolay

    6 июня 2012 г. 12:35
  • I don't think it's the CHECKPOINT process. More likely it is an Agent job that hijacks the database. It could also be a user who is trying to get in or some other background processes.

    You can keep users out by also setting RESTRICTED_USER for the database; this setting disallows anyonw who is not in the db_owner role to connect to the databaes. Since Agent jobs often runs as dbo, it will not stop them.

    I the best solution is to never to "USE master", but execute everything that needs to be done in master through dynamic SQL, this way:

    EXEC master..sp_executesql @sql


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    6 июня 2012 г. 13:54
  • No, we don't switch to master usually, only in rare cases.

    All changes which makes the script run in our production database.

    The error occurs often with dynamic SQL, using both methods: exec(@sql) and sp_executesql @sql

    But sometimes it happens with "drop/create function/procedure", sometimes in triggers while updating big tables.

    The probability of the error can be increased if during the execution of the script the server runs a resource-intensive process, such as copying a large file.

    By the way, we did not have this problem on MS SQL Server 2000.

    6 июня 2012 г. 14:54
  • The only other thing I can think of is a setting in SSMS which you find under Tools->Options->Query Execution->Advanced. "Disconnect after the query executes". This setting is unechecked by default, but check that it's not checked on the machine you are running from.

    What you can do is run a trace to see what processes that access the database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    6 июня 2012 г. 15:12
  • Do you have recommendations which events and columns should be selected for the trace?

    I think the default settings will not show something useful.

    Thanks for the hint.

    6 июня 2012 г. 15:32
  • I didn't say that, because I did not have the possible events in my head, and I figured that just as well as I can could read Books Online, you could.

    But the obvious events are Audit Login, RPC:Starting and SQL:BatchStarting, which all can be filtered for database ID or Name. Also, add a filter for <> the spid of your management process.

    I tried to find a specific event to capture "USE Database" but at a glance, I could not find any. It is also possible that the access is through a query with three-part notation. There are some events to trace object access, but they are likely to be expensive, so only try this if nothing else comes up.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    6 июня 2012 г. 16:53
  • Hi,

    I traced our test DB a few hours during last days with different filters and events, sometimes there was this error, sometimes wasn't, but nothing useful was found.

    Also, I found these articles:

    http://support.microsoft.com/kb/241363/ - (FIX: User Logged Into DB in Single User Mode May Encounter 924 Error)

    http://support.microsoft.com/kb/262699/ - (BUG: Error 924 When Cursor is Executed in DB in Single-User Mode)

    but they are related to MS SQL Server 7.0.

    Yes, some our tables have triggers with cursors and it seems the error happens with these tables.

    So, possible solution is to disable triggers during updates in single-user mode.

    • Предложено в качестве ответа Nikolay A. Sakharov 9 июня 2012 г. 12:42
    • Отменено предложение в качестве ответа Nikolay A. Sakharov 9 июня 2012 г. 12:43
    • Предложено в качестве ответа Nikolay A. Sakharov 9 июня 2012 г. 12:43
    • Отменено предложение в качестве ответа Nikolay A. Sakharov 9 июня 2012 г. 12:47
    9 июня 2012 г. 12:42
  • Out of the blue: you are not having any code that performs a loopback connection, do you?

    It is not completely crazy as it sounds; we have this in the system I mainly work with. We write errors and other things to a log table, and if there is a transaction in progress we make a loopback connection through an extended stored procedure (soon to be replaced by a CLR procedure). Could you happen to have something like that?

    Another thing to look for are loopback queries through OPENQUERY. Sometimes people do this be able use the result set from a stored procedure.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Предложено в качестве ответа Naomi NModerator 10 июня 2012 г. 3:20
    9 июня 2012 г. 17:40
  • Out of the blue: you are not having any code that performs a loopback connection, do you?

    I'm not sure, that I have properly understood this. Do you mean using OPENQUERY or OPENDATASOURCE? No, we don't use such functions in our update script. 

    In the script there are table updates and as I previously wrote, some tables have triggers with cursors.

    Also the script contains a call of a procedure which builds a dynamic SQL and executes it with sp_executesql procedure against updated database. 

    Now we separated our script to 3 subscripts: 

    1 - tables structure modifications (executes in single-user mode)

    2 - functions & procedures (executes in restricted-user mode)

    3 - data modifications (executes in single-user mode, but sometimes the error happens, it seems because of cursors in tables triggers)

    So, the problem in MS SQL Server 2005 with a single-user mode is with cursors in triggers and with using exec or sp_executesql

    12 июня 2012 г. 18:24
  • Loopback connections can occur with OPENQUERY, or extended stored procedures or CLR procedures. That is, you make a new connection to the server from the code running. I was mainly thinking whether you could have them in triggers.

    Of course, if you have set the database into single-user mode, a loopback connection will not succeed.

    Depending on how the application is access, RESTRICTED_USER may be a better option.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    12 июня 2012 г. 22:18
  • Loopback connections can occur with OPENQUERY, or extended stored procedures or CLR procedures. That is, you make a new connection to the server from the code running. I was mainly thinking whether you could have them in triggers.

    Of course, if you have set the database into single-user mode, a loopback connection will not succeed.

    Depending on how the application is access, RESTRICTED_USER may be a better option.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Hi, I encounter the same problem, http://social.msdn.microsoft.com/Forums/zh-CN/transactsql/thread/0277425e-2f78-481b-a08e-70efb7cc1656, could you help me to have a look, please?

    Thanks.

    5 января 2013 г. 1:53
  • Out of the blue: you are not having any code that performs a loopback connection, do you?

    I'm not sure, that I have properly understood this. Do you mean using OPENQUERY or OPENDATASOURCE? No, we don't use such functions in our update script. 

    In the script there are table updates and as I previously wrote, some tables have triggers with cursors.

    Also the script contains a call of a procedure which builds a dynamic SQL and executes it with sp_executesql procedure against updated database. 

    Now we separated our script to 3 subscripts: 

    1 - tables structure modifications (executes in single-user mode)

    2 - functions & procedures (executes in restricted-user mode)

    3 - data modifications (executes in single-user mode, but sometimes the error happens, it seems because of cursors in tables triggers)

    So, the problem in MS SQL Server 2005 with a single-user mode is with cursors in triggers and with using exec or sp_executesql


    Hi, I encounter the same problem with you, http://social.msdn.microsoft.com/Forums/zh-CN/transactsql/thread/0277425e-2f78-481b-a08e-70efb7cc1656, could you help me to have a look, please?

    Thanks.

    5 января 2013 г. 1:54
  • So what is on line 20 in this function?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    5 января 2013 г. 11:02