C# と VB.NET の質問掲示板

わんくま同盟

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

ツリー一括表示

SQL Serverのサイズ /chobi (18/08/07(Tue) 17:33) #88173
Re[1]: SQL Serverのサイズ /魔界の仮面弁士 (18/08/07(Tue) 18:15) #88174
  └ Re[2]: SQL Serverのサイズ /魔界の仮面弁士 (18/08/07(Tue) 18:38) #88175
    ├ Re[3]: SQL Serverのサイズ /chobi (18/08/08(Wed) 07:10) #88176
    └ Re[3]: SQL Serverのサイズ /chobi (18/08/08(Wed) 07:50) #88177
      └ Re[4]: SQL Serverのサイズ /chobi (18/08/08(Wed) 08:36) #88178 解決済み


親記事 / ▼[ 88174 ]
■88173 / 親階層)  SQL Serverのサイズ
□投稿者/ chobi (1回)-(2018/08/07(Tue) 17:33:25)

分類:[データベース全般] 

お世話になります。
こちらに質問して良いか少し迷ったのですが、他に聞くあてがないので
SQL Server Express には古いもので 4GB 新しいもので 10GB のサイズの制限があります。
サイズ制限に関係する値が取得したくて以下で何やらサイズらしきものは取得できました。
最初は "database_size" だろうと思っていたのですが・・・・

cmd.CommandText = "EXEC sp_spaceused";
  sda.Fill(ds);
@ ds.Tables[0].Rows[0]["database_name"].ToString();
A ds.Tables[0].Rows[0]["database_size"].ToString();
B ds.Tables[0].Rows[0]["unallocated space"].ToString();

C ds.Tables[1].Rows[0]["reserved"].ToString();
D ds.Tables[1].Rows[0]["data"].ToString();
E ds.Tables[1].Rows[0]["index_size"].ToString();
F ds.Tables[1].Rows[0]["unused"].ToString();

「data」はレコード数に比例して増えていくのですが、「database_size」は全く変わりませんでした。

レコード数 database_size unallocated reserved data index unusede
0 12341.56 6311.75 8.448 6.968 0.856 0.624
10000 12341.56 6305.86 14.48 12.936 0.872 0.672
20000 12341.56 6300.05 20.432 18.904 0.872 0.656
30000 12341.56 6294.23 26.384 24.864 0.872 0.648
40000 12341.56 6288.36 32.4 30.832 0.872 0.696※ 単位はMB(index と unusedはkB)
50000 12341.56 6282.55 38.352 36.8 0.872 0.68
60000 12341.56 6276.73 44.304 42.76 0.872 0.672
80000 12341.56 6265.11 56.208 54.696 0.872 0.64
100000 12341.56 6253.42 68.176 66.624 0.872 0.68

制限に近づいてきたらレコードを消す目安にしたかったのですが、
"data" を使えば良いのでしょうか?


[ □ Tree ] 返信 編集キー/

▲[ 88173 ] / ▼[ 88175 ]
■88174 / 1階層)  Re[1]: SQL Serverのサイズ
□投稿者/ 魔界の仮面弁士 (1769回)-(2018/08/07(Tue) 18:15:51)
No88173 (chobi さん) に返信
> SQL Server Express には古いもので 4GB 新しいもので 10GB のサイズの制限があります。

.ldf はその制限に含まれないそうです。
.mdf ファイルごとのサイズらしいです。.ndf もかな?
https://social.msdn.microsoft.com/Forums/sqlserver/ja-JP/8d764214-4daf-4dc2-b27e-9194ad69aa27/sql-server?forum=sqlserverja


> サイズ制限に関係する値が取得したくて以下で何やらサイズらしきものは取得できました。
> 最初は "database_size" だろうと思っていたのですが・・・・

database_size は、mdf + ldf + ndf のサイズですね。

ファイルのパスは sys.database_files から得られるので、そこから
System.IO.FileInfo の Length 等で確認できないでしょうか。


> 「data」はレコード数に比例して増えていくのですが、「database_size」は全く変わりませんでした。

それらの追加レコードは、データファイル内の未使用ページに書き込まれていったのでは?
[ 親 88173 / □ Tree ] 返信 編集キー/

▲[ 88174 ] / ▼[ 88176 ] ▼[ 88177 ]
■88175 / 2階層)  Re[2]: SQL Serverのサイズ
□投稿者/ 魔界の仮面弁士 (1770回)-(2018/08/07(Tue) 18:38:38)
No88174 (魔界の仮面弁士) に追記
> ファイルのパスは sys.database_files から得られるので、


SELECT
  sdb.name AS [DB名]
, smf.file_id AS [FileID]
, smf.name	AS [論理名]
, smf.physical_name AS [ファイルパス]
, CASE ( smf.type )
    WHEN CAST(0 AS tinyint) THEN 'データ'
    WHEN CAST(1 AS tinyint) THEN 'ログ'
  END AS [種別]
, CAST(sdf.size AS bigint) * 8192 AS [サイズ(Bytes)] 
, CAST(CAST(sdf.size AS decimal) * 8192 / 1024 AS decimal(12, 2)) AS [サイズ(KB)] 
, CAST(CAST(sdf.size AS decimal) * 8192 / 1024 / 1024 AS decimal(9, 2)) AS [サイズ(MB)] 
, CAST(CAST(sdf.size AS decimal) * 8192 / 1024 / 1024 / 1024 AS decimal(5, 2)) AS [サイズ(GB)] 
, CASE ( smf.is_percent_growth )
    WHEN CAST(0 AS bit) then CAST(smf.growth * 8192 / 1024 AS varchar) + 'KB'
    WHEN CAST(1 AS bit) then cast(smf.growth AS varchar) + '%'
  END AS [増分]
FROM
  sys.master_files smf
  LEFT JOIN sys.databases sdb 
  ON smf.database_id = sdb.database_id
  LEFT JOIN sys.database_files sdf
  ON smf.file_id = sdf.file_id
ORDER BY
  sdb.name
, smf.file_id

[ 親 88173 / □ Tree ] 返信 編集キー/

▲[ 88175 ] / 返信無し
■88176 / 3階層)  Re[3]: SQL Serverのサイズ
□投稿者/ chobi (2回)-(2018/08/08(Wed) 07:10:17)
丁寧な回答ありがとうございます。
試してみます。


> SELECT
> sdb.name AS [DB名]
> , smf.file_id AS [FileID]
> , smf.name AS [論理名]
> , smf.physical_name AS [ファイルパス]
> , CASE ( smf.type )
> WHEN CAST(0 AS tinyint) THEN 'データ'
> WHEN CAST(1 AS tinyint) THEN 'ログ'
> END AS [種別]
> , CAST(sdf.size AS bigint) * 8192 AS [サイズ(Bytes)]
> , CAST(CAST(sdf.size AS decimal) * 8192 / 1024 AS decimal(12, 2)) AS [サイズ(KB)]
> , CAST(CAST(sdf.size AS decimal) * 8192 / 1024 / 1024 AS decimal(9, 2)) AS [サイズ(MB)]
> , CAST(CAST(sdf.size AS decimal) * 8192 / 1024 / 1024 / 1024 AS decimal(5, 2)) AS [サイズ(GB)]
> , CASE ( smf.is_percent_growth )
> WHEN CAST(0 AS bit) then CAST(smf.growth * 8192 / 1024 AS varchar) + 'KB'
> WHEN CAST(1 AS bit) then cast(smf.growth AS varchar) + '%'
> END AS [増分]
> FROM
> sys.master_files smf
> LEFT JOIN sys.databases sdb
> ON smf.database_id = sdb.database_id
> LEFT JOIN sys.database_files sdf
> ON smf.file_id = sdf.file_id
> ORDER BY
> sdb.name
> , smf.file_id
[ 親 88173 / □ Tree ] 返信 編集キー/

▲[ 88175 ] / ▼[ 88178 ]
■88177 / 3階層)  Re[3]: SQL Serverのサイズ
□投稿者/ chobi (3回)-(2018/08/08(Wed) 07:50:48)
確認してみました。
レコード数を10k --> 20k --> 50k と増やしてみましたが、
ファイルサイズはmdf/ldfとも一切変わりませんでした。
だから「database_size」も変わらないのは分かりましたが、どうすればよいかはまだ未解決です。
SQLは一度確保したHDDのメモリ領域はデータを減らしても開放せずにずっと持っていて、
その空き領域に書き込むのでファイルサイズでは判断できなそうなイメージでしょうか・・・
もう少しSQLがmdf/ldfを増減させる仕組みについて調べてみます。

[ 親 88173 / □ Tree ] 返信 編集キー/

▲[ 88177 ] / 返信無し
■88178 / 4階層)  Re[4]: SQL Serverのサイズ
□投稿者/ chobi (4回)-(2018/08/08(Wed) 08:36:30)
少しだけ勉強しました。
タスク --> 圧縮(ファイル、データベース)をすると空き領域が開放されるので
レコード増やすとdatabase_size がレコード数に比例して増えていくことが
分かりました。
魔界の仮面弁士さん ありがとうございました。
解決済み
[ 親 88173 / □ Tree ] 返信 編集キー/


管理者用

- Child Tree -