0

So just a quick background, we are trying to update the database design, in a production environment. But we want to be sure, no users try to login during that time. So we started looking into single user mode, but that gave us some trouble, sometimes we would lose the connection in the middle of the update. So we setup a test environment to replicate the behavior.

We are using Microsoft SQL server 2017, with the AdventureWorks2017 database to replicate the issue. On the database we have turned off Auto close and Auto Update Statistics Asynchronously

If we then have two connections to the server, using the master database. Tell one of them to run this script

USE MASTER
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [AdventureWorks2017] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DECLARE @kill varchar(max) = '';
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(10), spid) + '; '
FROM master..sysprocesses 
WHERE spid > 50 AND dbid = DB_ID('AdventureWorks2017')
EXEC(@kill);

USE AdventureWorks2017
GO

DECLARE @cnt INT = 0;
WHILE @cnt < 10000
BEGIN
  SELECT TOP 1000 * from Person.Person;
   SET @cnt = @cnt + 1;
end;

And then on the other repeatedly run

SELECT TOP 1000 * FROM AdventureWorks2017.Person.Person;
GO;

At some point the first script stops working, and complains with an error

Database 'AdventureWorks2017' is already open and can only have one user at a time.

But to our understanding, this should not happen cause it still has the connection. Note this doesn't happen all the time. But it's still fairly consistent.

Is there anything that we are missing, or can this be an issue with the SQL server?

  • Sounds like an issue with SQL Server to me. I tried on 2019 and couldn't repro it. Perhaps path to current level and report ti MS is problem persist? Just a comment: Don't use spid > 50 when you kick out users. SQL server's own sessions can bleed over 50. Use sys.dm_exec_sessions instead and the column: is_user_process. – Tibor Karaszi Jul 1 at 9:53
  • Rather than using Single user mode, can you disable TCP/IP from SQL Server Config Mgr and connect locally to do your updates? – nkdbajoe Jul 1 at 15:23
1

When you set a database to single_user, you don't have any control over which session gets to connect, unless you're already connected to the database.

So simply connect before setting single_user to eliminate the race condition:

USE AdventureWorks2017
ALTER DATABASE CURRENT SET SINGLE_USER WITH ROLLBACK IMMEDIATE
| improve this answer | |
  • So am i to understand i cant have two databases in single user mode. In the same connection? – rareinator Jul 2 at 7:58
  • A session can set as many databases to single_user as you like, but the session can only be connected to a single database at a time. As an alternative you can set databases to RESTRICTED_USER instead. docs.microsoft.com/en-us/sql/t-sql/statements/… – David Browne - Microsoft Jul 2 at 16:55
  • Yeah okay, thanks for the help. Single user is not gonna work for us then but it seems like restricted user might. Anyway thank you for your assistance. – rareinator Jul 3 at 6:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.