SQL Server2005 ExpressEdtion リソース開放タイミング

Question

SQL Server 2005 Express Edtion での質問がございます。

開発をしていて、ＤＢを前の状態に戻したりする際に、
単純に　test.mdf と test.ldf を　コピーしようとしています。

ところが、ＩＩＳが動作して、ＤＢにアクセスされると、
しばらくの間（約８分程度）　test.mdf, test.ldf をコピー
や移動ができなくなってしまいます。

接続方法は、ユーザインスタンスではなくて、
SQL Management Studio ExpressでＤＢにアタッチしてから
下記のように接続をしております。

    <add key="dbConnectionString" value="Addr=.\SQLEXPRESS;Database=TESTDB;UID=sa;Pwd=******"/>

１．SQL Server を停止したり、ＩＩＳ　を停止したりすれば、コピーができるのですが、
夜間のバックアップなどを単純なxcopy で考えているため、ＷＥＢを停止せずに
せめて読み取りだけでも、できるようにならないでしょうか？

２．また、約８分後には、コピーができるようになるのはなぜなのでしょうか？
（ＦｒａｍｅＷｏｒｋのガーベッジコレクションなどが働いているのでしょうか？）

３．この時間の調節などは可能なのでしょうか？

４．簡易なバックアップの運用設計で、もっとよい方法はないでしょうか？

どなたか御親切な方、よろしく御指導お願いいたします。

 

 

 

 

 

 

 

 

Answer 1

通常のバックアップが一番簡易だと思います。

アタッチしているデータベースのmdfファイルを、単純なファイル扱いするにはデタッチが必須です。

Answer 2

（２）については、データベースのプロパティに自動起動か自動終了かその関連のプロパティがありますので、そこの設定だと思います（2005ではプロパティ名を確認していないので間違っていたら申し訳ありません）

一定時間アクセスがなかった際に、自動で終了するのでファイルがコピーできる状態になったのではないでしょうか。

ＭＳＤＥまではデフォルトが自動で終了する設定（ＳｑｌＳｅｒｖｅｒでは逆）なので、恐らくExpressEditionも・・・。

Answer 3

自動終了とは、ちょいと状況が違うと思われます。
User Instance Timeout の値と ASP.NET の何かが関係していて 8 分の値が出ているのではと推察できます。
User Instance Timeout の値はデフォルト 60 秒となっていて、使われなければ 60 秒で切り離されるはずです。
ただ、ASP.NET 側のコネクションプーリングが効いてしまって、暗黙的に切り離されないのではと。

では、どうするか？ということなのですが、sp_detach_db で明示的にデータベースを切り離した後、ファイルを xcopy するということをしないとだめだと思います。
User Instance を指定した場合のデータベース名はアタッチしたファイル名のフルパスだったはずなので、切り離しもちょっとしたコツが必要だと思います。

Answer 4

みなさん、御返答ありがとうございます。

御意見を参考にして調査したところ、小川さんのおっしゃるように、コネクション・プーリングが原因のようです。

下記のURLを参考にしてコネクション・プーリングを無効にしたところ、いつでもコピーができるようになりました。
"Addr=.\SQLEXPRESS;Database=TESTDB;UID=sa;Pwd=****;Pooling=false"/>

http://msdn.microsoft.com/library/ja/default.asp?url=/library/ja/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.asp

ただ Pooling=true;Connection Lifetime=5 の設定をしても実際には ５秒で開放されず、６分２０秒??という結果になりました。
connection.dispose してもすぐには開放されず、ASP.net 側のガベージコレクションかなにかで実際に開放しているものと思われます。

しかし、パフォーマンスの面から　コネクションプールを無効にするのは問題があるため、バックアップ batch内で
強制的にデタッチを行ってから、xcopy を行いたいと思います。
Express Edtion にもバックアップコマンドがあるとよいのですが・・・。

みなさんどうもありがとうございました。

 

Answer 5

＞Express Edtion にもバックアップコマンドがあるとよいのですが・・・。

ありますよ？

うちはExpress Editionでバックアップとってます。

BACKUP DATABASE [databasename] TO  DISK = N'xxx.bak' WITH NOFORMAT, NOINIT,  NAME = N'BACKUP', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

こんな感じで。

後はBOLを見てください。

Answer 6

中博俊　さんありがとうございます。

Express にも Backup ありましたね。
BACKUP DATABSE と RESTORE DATABASE を組み合わせて、うまく　バックアップ・復元されることを確認しました。
ありがとうございます。

 

 

 

Answer 7

mdf ファイルを単純なファイル扱いとするためには デタッチが必須とのことですが、
当方の環境では、デタッチせずとも（アタッチしたままで） 通常のファイルコピーが可能です。
(移動や消去すら可能です。移動してしまうとカタログとの矛盾が発生します）

ただ、IISからコネクトされると、上記８分間の（多分コネクションプーリングによるもの）ホールドが発生し、
その間は sp_detach_db すら無効です。
sqlcmd から sp_detach_db を実行したところ下記のメッセージが発生します。

メッセージ 3703、レベル16、状態2、サーバー xxxx\sqlexpress
データベース 'TESTDB' は現在使用中なので、デタッチできません

８分経過後には、正常に sp_detach_db ができます。

ですので、単純ファイル扱いとするバックアップ計画そのものがなにか矛盾があるものと思っております。

やりたいことは、２台のサーバ構成で、夜間にサーバAからサーバBへ、「ある時点」のDBを保管しておき
サーバAでオペレーションミスによる破壊が起きたときに、（ハード障害ではなく、人為的に削除など）
サーバBに切り替えることで、「ある時点」まで復元するような処置をおこないたいのです。
ですので、ミラーリングやデュプリケートではなく、「時間差」のあるバックアップが必要になります。

であれば、やはり BACKUP DATABASE と RESTORE DATABASE を組み合わせるのがベストなのでしょうね