データベースへの接続をすべて閉じる

データベースへの接続をすべて閉じる手順を紹介します。

事前準備

データベースに接続するアプリケーションをすべて停止する

アクティブな接続があると、接続を閉じるために非アクティブになるまで接続を待ち続ける動作になるため、アクティブな接続がなくなるまでずっと待ち続ける動作になる場合があります。接続を閉じる作業をする前に、対象のデータベースに接続するアプリケーションをすべて停止しておく必要があります。

SQL文を実行する方法:シングルユーザーモードに変更して接続を閉じる

ALTER DATABASE コマンドで SET SINGLE_USER を指定してデータベースをシングルユーザーモードに変更する際に WITH ROLLBACK IMMEDIATE を指定すると、すべての未完了のトランザクションがロールバックされ、データベースへの接続がすべて閉じられる動作になります。
ALTER DATABASE データベース名 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

下記は iPentecSandBox データベースをシングルユーザーモードにして接続を閉じ、その後、シングルユーザーモードを解除するSQL文です。
ALTER DATABASE iPentecSandBox SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE iPentecSandBox SET MULTI_USER

SQL文を実行する方法:killコマンドで接続を閉じる

master データベースのsysprocesses テーブルにデータベースに接続されているプロセスが記録されています。下記のSQL文を実行すると、指定したデータベースに接続しているプロセスの一覧が表示されます。
select spid,status,dbid from master.dbo.sysprocesses where dbid = db_id('データベース名')

プロセス番号が取得できたら、kill コマンドでプロセスを終了することで接続を閉じることができます。
kill (プロセス番号)

実行例

sysprocesses のプロセス表示のSQLを実行した結果が下図です。こちらのデータベースは接続が1つしかありません。
データベースへの接続をすべて閉じる:画像1

別のデータベースでは接続がたくさんあることが確認できます。プロセスID 54から順次kill コマンドでプロセスを終了すれば接続を閉じられます。
データベースへの接続をすべて閉じる:画像2

kill コマンドを利用してプロセスを終了していきます。プロセスが終了できると「コマンドは正常に終了しました」のメッセージが表示されます。
データベースへの接続をすべて閉じる:画像3

なお、SQL文を実行する自分自身のプロセスは終了できません。
データベースへの接続をすべて閉じる:画像4

SQL例

上記の処理をWHILEループを使ってすべてのプロセスをkillするSQL文が下記になります。
USE master
GO

DECLARE @SQL AS VARCHAR(255)
DECLARE @SPID AS SMALLINT
DECLARE @Database AS VARCHAR(500)

SET @Database = '(データベース名)'

DECLARE Murderer CURSOR FOR
SELECT spid FROM sys.sysprocesses WHERE DB_NAME(dbid) = @Database

OPEN Murderer

FETCH NEXT FROM Murderer INTO @SPID

WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SQL = 'Kill ' + CAST(@SPID AS VARCHAR(10)) + ';'
    EXEC (@SQL)
    PRINT  ' Process ' + CAST(@SPID AS VARCHAR(10)) +' has been killed'
    FETCH NEXT FROM Murderer INTO @SPID
    END 

CLOSE Murderer
DEALLOCATE Murderer

実行するとすべての接続プロセスをkillします。
データベースへの接続をすべて閉じる:画像5

SQL Server Management Studio のクエリウィンドウを利用する方法

SQL Server Management Studio を起動し、ツールバーの[新しいクエリ]ボタンをクリックしてクエリウィンドウを開きます。ウィンドウ左上のSQLエディタのツールバーのコンボボックスで接続を解除するデータベースを選択します。
データベースへの接続をすべて閉じる:画像6

クエリウィンドウ内でマウスボタンを右クリックします。下図のポップアップメニューが表示されますので[接続]サブメニューの[すべてのクエリの切断]をクリックします。
データベースへの接続をすべて閉じる:画像7

すべての接続が切断されます。クエリウィンドウの下部に接続が解除された旨のメッセージが表示されます。
データベースへの接続をすべて閉じる:画像8

SQL Server Management Studio のデータベースのオフラインを利用する方法

データベースをオフラインにすることで、すべての接続を閉じることができます。オフラインの手順の詳細についてはこちらの記事も参照してください。
SQL Server Management Studio を起動し、オブジェクト エクスプローラーのツリービューで接続を削除したいデータベースのノードをクリックして選択します。選択したノードで右クリックします。下図のポップアップメニューが表示されますので、[タスク]サブメニューの[オフラインにする]の項目をクリックします。
データベースへの接続をすべて閉じる:画像9

[データベースをオフラインにする]ダイアログが表示されます。
データベースへの接続をすべて閉じる:画像10

[すべてのアクティブな接続を解除する]の項目のチェックボックスをクリックしてチェックを付けます。このチェックにより、データベースに接続しているすべての接続を閉じてデータベースをオフラインにできます。設定ができたらダイアログ右下の[OK]ボタンをクリックします。
データベースへの接続をすべて閉じる:画像11

データベースがオフラインになります。SQL Server Management Studio のオブジェクト エクスプローラーのデータべース名に "(オフライン)" の文字が追加された表示になります。
データベースへの接続をすべて閉じる:画像12
データベースへの接続をすべて閉じる:画像13

オフラインにしたデータベースをオンラインに戻す方法はこちらの記事を参照してください。

SQL Server Management Studio のデータベースのデタッチを利用する方法

データベースをデタッチすることですべての接続を閉じることができます。データベースのデタッチの詳細はこちらの記事を参照してください。

SQL Server Management Studio を起動し、オブジェクト エクスプローラーのツリービューで接続を削除したいデータベースのノードをクリックして選択します。選択したノードで右クリックします。下図のポップアップメニューが表示されますので、[タスク]サブメニューの[デタッチ]の項目をクリックします。
データベースへの接続をすべて閉じる:画像14

[データベースのデタッチ]ダイアログが表示されます。
データベースへの接続をすべて閉じる:画像15

[接続の削除]の項目のチェックボックスをクリックしてチェックを付けます。このチェックにより、デタッチ時にすべての接続を閉じてデタッチする動作になります。
データベースへの接続をすべて閉じる:画像16

デタッチしたデータベースを再度読み込むアタッチの手順はこちらの記事を参照してください。
AuthorPortraitAlt
著者
iPentec.com の代表。ハードウェア、サーバー投資、管理などを担当。
Office 365やデータベースの記事なども担当。
作成日: 2019-01-04
Copyright © 1995–2025 iPentec all rights reserverd.