0

We have some scripts that are executed during the installation of our application.

One of them is used for setting Database Collation, the other one is used for setting compatibility level. We use the following construction for these scripts:

ALTER DATABASE [DB_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE [DB_NAME] SET COMPATIBILITY_LEVEL = DB_COMPATIBILITY_LEVEL
-- ALTER DATABASE [DB_NAME] COLLATE 'Collation Name'
GO

ALTER DATABASE [DB_NAME] SET MULTI_USER
GO

The question is:

Is it possible for some background process to get an access after setting single user mode? In this case our script couldn't be executed. I saw this issue and it seems similar, so I decided to ask a question.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/950c9b85-67f9-4272-8dff-14aa3590bc8a/single-user-mode-session-lost-after-backgound-processes-jump-in?forum=sqldatabaseengine

This issue was related to restoring database.

alter database [test-db] set single_user with rollback immediate;   --This sql is run using test-db
use master;restore database [test-db] from database_snapshot = 'snapshot_test-db';
alter database [test-db] set multi_user;

But I don't fully understand the difference between restoring and altering the database in the single user mode.

In the first case will be enough to set the database offline.

Any help will be appreciated.

Thanks in advance.

Alex Trueman
  • 19
  • 1
  • If you are asking, if there a chance that after you have put a database into `SINGLE_USER` and then you immediate connect to it (using `USE`) in the same batch/set of batches, is there a chance that someone else could get that connection, then the answer is "yes". I am sure there is a remark/warning in the documentation somewhere to confirm this, but I currently can't find it. If i find it, I will post and answer and cite it. – Thom A Apr 02 '20 at 13:33
  • it is a bit contradictory to set the db in single user mode and then try to restore it from a snapshot (the restore process has to access the db itself as the snapshot has only the original pages which have been changed since its creation). Have you tried, setting the db to single user (to kick everyone out), then immediately change it to multi-user and fire up the restore from snapshot? the db wont be accessible when in recovery/during the restore. – lptr Apr 02 '20 at 15:07
  • @lptr Thanks for the reply. The issue with the restoring database was an example that I found. For me, it's more important to know is there a chance to have an issue during setting collation in single-user mode (altering database, not restoring/dropping). Becasue for restoring database offline mode works pretty well. – Alex Trueman Apr 03 '20 at 10:36

0 Answers0