SQL Server 起動完了後のエラー 18456 「ユーザー 'XXX' はログインできませんでした。 理由: 明示的に指定されたデータベースを開けませんでした。」

 

神谷 雅紀
Escalation Engineer

以前のポスト FAQ: SQL Server 起動後、接続が可能となるタイミングは？ では、SQL Server 起動時のデータベース復旧処理中に、まだ復旧が完了していないデータベースを既定のデータベースとしていたり、接続文字列中で明示的に指定していたりした場合に、表題のエラーになるという話をしました。オフライン (OFFLINE) に設定されていたり、何らかの問題により未確定 (SUSPECT) の状態にあるデータベースでもこのエラーになることは容易に想像できるかと思いますが、それ以外にもこのエラーになる状況が存在します。

それは、AUTO_CLOSE (自動終了) オプションが有効になっているデータベースへのアクセス時です。

 

なぜ AUTO_CLOSE オプションが有効になっているデータベースで「明示的に指定されたデータベースを開けませんでした。」となるのか

AUTO_CLOSE オプションが有効になっているデータベースは、通常はクローズされた状態になっています。誰かがそのデータベースにアクセスしようとすると、その時点でデータベースはオープンされます。誰もアクセスしていない状態になると、データベースは自動的にクローズされますが、クローズ処理中はデータベースにはアクセスできません。そのため、あるユーザーがデータベースへのアクセスを終えてデータベースのクローズ処理が実行されている最中に、別のユーザーがそのデータベースを指定してログインしようとすると、その「別のユーザー」は、エラー 18456 「ユーザー 'XXX' はログインできませんでした。 理由: 明示的に指定されたデータベースを開けませんでした。」となり、ログインに失敗します。

このエラー自体は、タイミングの問題なので、再試行を行うと、ほとんどの場合、エラーなくログインが可能になります。

 

AUTO_CLOSE オプションを OFF にする方法

データベースの AUTO_CLOSE オプションを OFF にすれば、このような状況を回避できます。

T-SQL を使用する場合

以下の T-SQL ステートメントを実行します。(dbname は対象データベース名)

ALTER DATABASE dbname SET AUTO_CLOSE OFF

AUTO_CLOSE オプションの設定状態については、sys.databases カタログビューで確認することができます。

select name, is_auto_close_on from sys.databases

Management Studio GUI を使用する場合

「データベースのプロパティ」の「オプション」で、「自動」-「自動終了」を False に設定します。

補足

アクセスされていないデータベースをクローズする目的は、リソースの節約とファイル管理の利便性を高めるためです。データベースがクローズされると、キャッシュされていたデータベースのデータはすべてメモリから削除されるため、それらのメモリは他の用途に再利用可能となります。また、データベースのクローズと同時にデータベースを構成するファイルもクローズされるため、SQL Server を停止しなくても、それらファイルを別の場所にコピーすることができるようになります。通常このようなことが利点となるのは、デスクトップやノート PC などの非サーバー上で実行され、単一ユーザーによって使用されることを想定した Express エディションのみです。そのため、AUTO_CLOSE オプションは、Express エディション以外のエディションは既定で OFF になっています。

ただし、AUTO_CLOSE はデータベースオプションであることは意識しておく必要があります。開発時に Express エディションを使用してデータベースを作成し、その後、運用環境への展開のためにデータベースをバックアップして Enterprise など別のエディションにリストアするという方法が取られることはよくあるかと思います。この場合、AUTO_CLOSE オプションは維持されるため、明示的に OFF にしない限り、リストア先でも ON の状態になります。