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

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

事前準備

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

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

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

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文を実行すると、指定したデータベースに接続しているプロセスの一覧が表示されます。

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

実行例

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


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


kill コマンドを利用してプロセスを終了していきます。プロセスが終了できると「コマンドは正常に終了しました」のメッセージが表示されます。


なお、SQL文を実行する自分自身のプロセスは終了できません。

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します。

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

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


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


すべての接続が切断されます。クエリウィンドウの下部に接続が解除された旨のメッセージが表示されます。

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

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


[データベースをオフラインにする]ダイアログが表示されます。


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


データベースがオフラインになります。SQL Server Management Studio のオブジェクト エクスプローラーのデータべース名に "(オフライン)" の文字が追加された表示になります。



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

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

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

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


[データベースのデタッチ]ダイアログが表示されます。


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


デタッチしたデータベースを再度読み込むアタッチの手順はこちらの記事を参照してください。