1

Our application has in-built functionality to update the database it uses (triggered when an app update is downloaded from our server.)

Usually, the update comprises of table and data conversion and then a drop of existing stored procedures and a create of all current stored procedures.

So we do the following from C#:

  1. Create Connection
  2. Set database to Single User Mode
  3. Run upgrade script to upgrade tables and convert data
  4. Run upgrade script to drop existing stored procedures and add new ones
  5. Set database to Multi User Mode
  6. Close connection

Step 2 -5 are done using the same connection. For some clients it occassionaly errors out in the middle of Step3 or Step 4. The log shows:

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

This is very strange because at that time we are already in Single User mode so no other user should be able to connect to the database, let alone somehow put it in Single User mode for its own connection.

We are wondering if some internal SQL Server process is taking over our Single User mode and preventing us from completing the update.

We are aware that if AUTO_UPDATE_STATISTICS_ASYNC is turned ON this may interrupt Single User Mode, but we have verified that it is turned off and it still occurred for the client.

Could a CHECKPOINT operation interrupt it? If so, what do we do about that, because that is a server-wide setting so we cannot modify it.

Roel Vlemmings
  • 359
  • 2
  • 5
  • Look at this link: http://manu-sqlserverblog.blogspot.com/2011/05/cannot-access-database-in-single-user.html – veljasije Apr 09 '14 at 13:10
  • From http://stackoverflow.com/questions/18965980/exit-single-user-mode `SSMS in general uses several connections to the database behind the scenes.` – Bharadwaj Apr 09 '14 at 13:24
  • So during the execution of the script you're switching database context out and back to the MyDB? Anything could grab the connection in between, even reporting services (actually, my money is on RS). – dean Apr 09 '14 at 13:57
  • We had a problem that a back end task service on a server that was supposed to be decommissioned kept grabbing our dev DB when we tried to switch it to single user mode. It made it very frustrating since we were switching to single user mode to correct a server login trigger problem, so we couldn't log in at all in multi-user mode! – Bacon Bits Apr 09 '14 at 14:42
  • @veljasije: I have updated the description to make the problem more clear, please have a look. – Roel Vlemmings Apr 14 '14 at 08:40
  • @BaconBits: I have updated the description to make the problem more clear, please have a look. – Roel Vlemmings Apr 14 '14 at 08:40
  • @dean: I have updated the description to make the problem more clear, please have a look – Roel Vlemmings Apr 14 '14 at 08:41
  • No, it's not a CHECKPOINT, you can test it easily yourself. CHECKPOINT is physical operation only. Did you check the error logs and the default trace? You will have to set up some kind of logging most likely, at least during the time when the job runs. Try changing the audit level to both succesful and failed logins, or use SQL Server audit, or a server-side trace or xevents session to see what's happening. – dean Apr 14 '14 at 08:56

0 Answers0