Handle SQL Server Single User scenarios

keep-single-user-while-looping.sql

-- just a template example
DECLARE	@iterator INT;
DECLARE	@killstmt	nvarchar(MAX);

SET @iterator = 10; -- init first iteration

WHILE 1=1
BEGIN
	BEGIN TRY
		PRINT 'do something';
	END TRY
	BEGIN CATCH
		-- in some cases, system processes may be start "using" the database
		-- in such cases we should simply wait and retry, instead we're also
		-- making sure to kill any user process that may have succeeded in
		-- acquiring single user state
		IF ERROR_NUMBER() = 924
		BEGIN
			IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
			-- try to kill user sessions holding locks
			-- note: system processes are not killed, we're going to retry anyway
			SELECT @killstmt = STUFF((
				SELECT '; KILL ' + CONVERT(varchar, request_session_id)
				FROM	sys.dm_tran_locks AS L
				JOIN	sys.dm_exec_sessions AS S
					ON	L.request_session_id = S.session_id
				WHERE	resource_database_id = DB_ID()
					AND	S.is_user_process = 1
					AND	S.session_id <> @@SPID
				FOR XML PATH('')), 1, 2, ''
				)
			BEGIN TRY
				EXEC (@killstmt)
			END TRY
			BEGIN CATCH
				IF ERROR_NUMBER() <> 6106 -- SPID no more active...
					THROW;
			END CATCH
			-- wait some time for good practice between retries
			WAITFOR DELAY '00:00:00.333';
			CONTINUE;	-- we're not deleting current queue item, so it 
		END
	END CATCH
	SET @iterator = @iterator -1;
	IF @iterator = 0
		BREAK
END -- WHILE

SetDatabaseSingleUser.sql

DROP PROCEDURE IF EXISTS dbo.SetDatabaseSingleUser
GO
CREATE PROCEDURE dbo.SetDatabaseSingleUser
	@DatabaseName	 sysname
,	@debug			 tinyint = 0
AS
IF @debug = 1 PRINT FORMATMESSAGE('%s %s: stored procedure starting', convert(varchar(25), getdate(), 120), QUOTENAME(OBJECT_NAME(@@PROCID)));
IF @debug = 1 PRINT FORMATMESSAGE('%s %s: parameter @DatabaseName=''%s''', convert(varchar(25), getdate(), 120), QUOTENAME(OBJECT_NAME(@@PROCID)), @DatabaseName);
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
	------------------------------------------------------------------------------
	-- variables
	------------------------------------------------------------------------------
	DECLARE @sqlstmt		nvarchar(max);

	------------------------------------------------------------------------------
	-- Acquire single user
	------------------------------------------------------------------------------
	WHILE 1=1
	BEGIN
		SET @sqlstmt =
			REPLACE(N'ALTER DATABASE [@DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;'
			, N'@DatabaseName', @DatabaseName);
		BEGIN TRY
			IF @debug = 1 PRINT FORMATMESSAGE('%s %s: attempting to acquire single user', convert(varchar(25), getdate(), 120), QUOTENAME(OBJECT_NAME(@@PROCID)));
			EXEC (@sqlstmt);
		END TRY
		BEGIN CATCH
			-- re-throw error only if it's not one expected when ROLLBACK IMMEDIATE doesn't work
			IF ERROR_NUMBER() NOT IN (924, 5064, 5069, 3101)
				THROW;
		END CATCH
		-- check if we managed to acquire single user
		IF EXISTS (
			SELECT	*
			FROM	sys.dm_tran_locks 
			WHERE	resource_database_id = DB_ID(@DatabaseName)
			  AND	request_session_id = @@SPID
		)
		BEGIN
			-- exit loop if we acquired single user
			BREAK;
		END
		ELSE
		BEGIN
			-- otherwise try to kill user sessions holding locks
			-- note: system processes are not killed, we're going to retry anyway
			SELECT @sqlstmt = STUFF((
				SELECT '; KILL ' + CONVERT(varchar, request_session_id)
				FROM	sys.dm_tran_locks AS L
				JOIN	sys.dm_exec_sessions AS S
				  ON	L.request_session_id = S.session_id
				WHERE	resource_database_id = DB_ID(@DatabaseName)
				  AND	S.is_user_process = 1
				FOR XML PATH('')), 1, 2, ''
				)
			IF @debug = 1 PRINT FORMATMESSAGE('%s %s: killing user connections holding locks on database', convert(varchar(25), getdate(), 120), QUOTENAME(OBJECT_NAME(@@PROCID)));
			BEGIN TRY
				EXEC (@sqlstmt)
			END TRY
			BEGIN CATCH
				IF ERROR_NUMBER() <> 6106 -- SPID no more active...
					THROW;
			END CATCH
			-- wait some time for good practice between retries
			WAITFOR DELAY '00:00:00.333';
		END
	END
END TRY
BEGIN CATCH
	THROW;
END CATCH
IF @debug = 1 PRINT FORMATMESSAGE('%s %s: stored procedure ending', convert(varchar(25), getdate(), 120), QUOTENAME(OBJECT_NAME(@@PROCID)));
GO

SetDatabseMultiUser.sql

DROP PROCEDURE IF EXISTS dbo.SetDatabaseMultiUser
GO
CREATE PROCEDURE dbo.SetDatabaseMultiUser
	@DatabaseName	 sysname
,	@debug			 tinyint = 0
AS
IF @debug = 1 PRINT FORMATMESSAGE('%s %s: stored procedure starting', convert(varchar(25), getdate(), 120), QUOTENAME(OBJECT_NAME(@@PROCID)));
IF @debug = 1 PRINT FORMATMESSAGE('%s %s: parameter @DatabaseName=''%s''', convert(varchar(25), getdate(), 120), QUOTENAME(OBJECT_NAME(@@PROCID)), @DatabaseName);
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
	------------------------------------------------------------------------------
	-- variables
	------------------------------------------------------------------------------
	DECLARE @sqlstmt		nvarchar(max);

	------------------------------------------------------------------------------
	-- Set multi user
	------------------------------------------------------------------------------
	WHILE 1=1
	BEGIN
		SET @sqlstmt =
			REPLACE(N'ALTER DATABASE [@DatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATE;'
			, N'@DatabaseName', @DatabaseName);
		BEGIN TRY
			IF @debug = 1 PRINT FORMATMESSAGE('%s %s: attempting to acquire multi user', convert(varchar(25), getdate(), 120), QUOTENAME(OBJECT_NAME(@@PROCID)));
			EXEC (@sqlstmt);
		END TRY
		BEGIN CATCH
			-- re-throw error only if it's not one expected when ROLLBACK IMMEDIATE doesn't work
			IF ERROR_NUMBER() NOT IN (924, 5064, 5069, 3101)
				THROW;
		END CATCH
		-- check if we managed to acquire multi user
		IF NOT EXISTS (
			SELECT	*
			FROM	sys.dm_tran_locks 
			WHERE	resource_database_id = DB_ID(@DatabaseName)
		)
		BEGIN
			-- exit loop if we acquired multi user
			BREAK;
		END
		ELSE
		BEGIN
			-- otherwise try to kill user sessions holding locks
			-- note: system processes are not killed, we're going to retry anyway
			SELECT @sqlstmt = STUFF((
				SELECT '; KILL ' + CONVERT(varchar, request_session_id)
				FROM	sys.dm_tran_locks AS L
				JOIN	sys.dm_exec_sessions AS S
				  ON	L.request_session_id = S.session_id
				WHERE	resource_database_id = DB_ID(@DatabaseName)
				  AND	S.is_user_process = 1
				FOR XML PATH('')), 1, 2, ''
				);
			IF @debug = 1 PRINT FORMATMESSAGE('%s %s: killing connections holding locks on database', convert(varchar(25), getdate(), 120), QUOTENAME(OBJECT_NAME(@@PROCID)));
			BEGIN TRY
				EXEC (@sqlstmt)
			END TRY
			BEGIN CATCH
				IF ERROR_NUMBER() <> 6106 -- SPID no more active...
					THROW;
			END CATCH
			-- wait some time for good practice between retries
			WAITFOR DELAY '00:00:00.333';
		END
	END
END TRY
BEGIN CATCH
	THROW;
END CATCH
IF @debug = 1 PRINT FORMATMESSAGE('%s %s: stored procedure ending', convert(varchar(25), getdate(), 120), QUOTENAME(OBJECT_NAME(@@PROCID)));
GO