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?