[SQLServer]エラー「プールから接続を取得する前にタイムアウト期間が過ぎました。プールされた接続がすべて使用中で、プール サイズの制限値に達した可能性があります」

ある時いきなり以下のエラーが出て、その後同じアプリケーションプールを使っているシステムがすべてタイムアウトになった

プールされた接続がすべて使用中で、プール サイズの制限値に達した可能性があります

調べると １つのアプリケーションプールがＤＢに接続する際に使用するプール数ＭＡＸはデフォルト１００。 

これを超えた場合、上記のような現象になる。 

プール数が異常に溜まる場合は 基本的にはプログラムが原因で、

きちんとDBをクローズしないようなプログラムを書いていると 処理が終わった後もプールが４分～１０分残り続ける。 

その間に溜まりに溜まって100以上になるとこの現象になる。 

プログラムの原因は別途調べるとして

ひとまず現状を回復したい場合は web.configにてMaxPool数をデフォルトの100から200等にUPすればよい。

デメリットとしては永遠に増え続けた場合。メモリを大量に使うことになる懸念がある。

<add name="Constr" connectionString="Server=XXXX;database=DBName;Trusted_connection=true;pooling=true;Max Pool Size=200;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

現在のプール数を調べるSQL文は以下（sa権限が必要）

--①hostname内訳
SELECT DB_NAME(sP.dbid) AS the_database,hostname
, COUNT(sP.spid) AS total_database_connections
FROM sys.sysprocesses sP
--where DB_NAME(dbid) = 'ＸＸＸＸDB'　--DB名で絞りたい場合
--and hostname in ('webサーバ名で絞りたい場合')
GROUP BY DB_NAME(sP.dbid),hostname
ORDER BY 1;


--②DB別のsleepコネクション数の分布をチェック
SELECT spid,DB_NAME(sP.dbid) AS the_database , hostname AS host, *
FROM sys.sysprocesses sP
--WHERE DB_NAME(sP.dbid) = 'ＸＸＸＸDB'　--DB名で絞りたい場合
--and hostname in ('webサーバ名で絞りたい場合')
WHERE status = 'sleeping'
ORDER BY hostname,login_time;

--③さらに詳細が知りたい場合
Select text,* from master.dbo.sysprocesses
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS qt
where
--spid = *** -- ②で取得したsession_idをここにセット
spid IN (267,
271,
264,
245,
119
)

上記ＳＱＬ文で、溜まっているＳＱＬを割り出し、そこから該当のプログラムを探し出すといいかも