SQL Server データベースをシングルユーザー(Single User)モードから通常のマルチユーザーモードに戻す方法

SQL Server データベースのシングルユーザーモード

SQL Server データベースをシングルユーザー(Single User)モードにすると、そのデータベースに一度に一人のユーザーしかアクセスできなくなります。

例えば、シングルユーザーモードで既に他のユーザーがアクセスしているデータベースに対してクエリーを実行すると、次のようなエラーになります。

Msg 924, Level 14, State 1, Line 2 データベース 'Test' は既に開かれています。同時に 1 人のユーザーだけが開けます。

今回はシングルユーザー(SINGLE_USER)モードから通常のマルチユーザー(MULTI_USER)モードに戻す方法をご紹介します。

マルチユーザー(MULTI_USER)モードに変更する

まず、SQL Server データベースをマルチユーザー(MULTI_USER)モードに変更するスクリプトは以下の通りです。

実行中のトランザクションがあればロールバックされます。

USE master;
GO

ALTER DATABASE [DatabaseName]
SET MULTI_USER
WITH ROLLBACK IMMEDIATE;

ただ、他のユーザーがそのデータベースに既にアクセスしている場合、以下のようなエラーになります。

Msg 5064, Level 16, State 1, Line 4 この時点では、データベース 'Test' の状態やオプションを変更できません。データベースはシングルユーザーモードで、現在ユーザーが接続中です。 Msg 5069, Level 16, State 1, Line 4 ALTER DATABASE ステートメントが失敗しました。

データベースにアクセスしているプロセスを探す

マルチユーザー(MULTI_USER)モードに変更するスクリプトを実行する為に、現在ターゲットのデータベースにアクセスしているプロセスのセッション ID を探し出して、強制終了します。

データベースにアクセスしているプロセスのセッション ID を探すクエリーには次のようなものがあります。

-------------------------------------
SELECT	request_session_id,
	* 
FROM	sys.dm_tran_locks 
WHERE	resource_database_id = DB_ID('DatabaseName');
-------------------------------------
SELECT	*
FROM	sys.sysprocesses 
WHERE	dbid = DB_ID('DatabaseName');
-------------------------------------
DECLARE @sp_who_result TABLE (
	spid		SMALLINT,
	ecid		SMALLINT,
	status		NCHAR(30),
	loginame	NCHAR(128),
	hostname	NCHAR(128),
	blk		CHAR(5),
	dbname		NCHAR(128),
	cmd		NCHAR(16),
	request_id	INT
);

INSERT INTO @sp_who_result
	EXEC sp_who;

SELECT	*
FROM	@sp_who_result
WHERE	dbname = 'DatabaseName';
-------------------------------------

sys.dm_tran_locks は現在アクティブなロックマネージャーリソースの情報を返すシステムビューです。 request_session_id カラムの値がセッション ID です。

sys.sysprocesses は実行されているプロセスの情報を返すシステムビューです。 spid カラムの値がセッション ID です。

sp_who は、現在のユーザー、セッション、プロセスなどの情報を返すシステムストアードプロシージャです。こちらも spid カラムの値がセッション ID です。

どの結果も待機中などのプロセスも含まれます。スクリーンショットのケースでは 56 にブロックされて 66 は待機状態でなので、セッションID 56 がシングルユーザーモードでアクセスしているセッション ID ですね。

アクセスしているプロセスを強制終了する

アクセスしているプロセスのセッション ID を見つけたら、以下のクエリーを使って、そのプロセスを強制終了します。

KILL [Session ID];

その後、再度マルチユーザー(MULTI_USER)モードに変更するスクリプトを実行すると、マルチユーザー(MULTI_USER)モードに変更できました。

USE master;
GO

ALTER DATABASE Test
SET MULTI_USER
WITH ROLLBACK IMMEDIATE;