TempDB を圧縮する場合の注意点について

  • Article

高原 伸城

Support Escalation Engineer

 

皆さん、こんにちは。 Microsoft SQL Server/Microsoft Azure SQL Database サポートチームの 高原 です。

今回は、TempDB を圧縮する場合の注意点について紹介したいと思います。

 

一時テーブルに大量のデータを挿入したり、大量データをソートする必要があるような ロング トランザクション を実行した場合、TempDB のファイルサイズが大きく拡張される可能性があります。

そして、拡張された TempDB のファイルサイズを最も安全に初期サイズまで縮小させるためには、SQL Server サービスの再起動を実施することになります。

しかしながら、24時間365日 稼働し続けるシステムの場合、SQL Server サービスを即座に再起動させることは困難であるものと想定しています。

 

そのため、TempDB についても、ユーザーデータベースと同様に、DBCC SHRINKDATABASE, DBCC SHRINKFILE コマンド、または、SQL Server Management Studio (以降 SSMS) の圧縮タスクで、データベースを圧縮することが可能になっています。

 

＋参考情報

SQL Server で tempdb データベースを圧縮する方法

 

しかしながら、TempDB に対して DBCC SHRINKDATABASE, DBCC SHRINKFILE コマンド、または、SSMS の圧縮タスクを実行する場合、以下の点に注意が必要です。

[注意点] 1) DBCC SHRINKDATABASE 及び DBCC SHRINKFILE コマンド、または、SSMS の圧縮タスクを実行後、Tempdb の 初期サイズ が更新されます。 2) Tempdb の 初期サイズ は、SQL Server Management Studio の データベースのプロパティに表示されている [初期サイズ (MB)] 欄の値と異なる場合があります。

 

それでは、実際に、Tempdb の初期サイズがどのように更新されるかを見ていきましょう。

※ 検証では、SQL Server 2014 を使用しています。

 

まず Tempdb の初期サイズについて説明します。

SQL Server サービス起動時に Tempdb が再作成されますが、Tempdb 作成時のファイルサイズは、sys.master_files の size 列の値が、初期ファイルサイズとして使用されます。

 

[クエリ]

select name, type_desc, size*8 as 'size (KB)' from sys.master_fileswhere database_id = 2

[結果]

name    type_desc    size (KB)tempdev    ROWS    10240templog    LOG    10240

 

今回の例の場合は、Tempdb のデータベース物理ファイル (tempdev) サイズ、トランザクションログファイル (templog) サイズの何れも 10 MB となっています。

ここで、Tempdb のファイルサイズを拡張させるため、以下のクエリを実行します。

 

[クエリ]

set nocount onuse tempdbgocreate table t1 (c1 char(4000), c2 char(4000))godeclare @i intset @i = 0while @i < 10000beginset @i = @i +1insert into t1 values(@i,@i)endgoselect name, type_desc, size*8 as 'size (KB)' from sys.master_fileswhere database_id = 2goselect name, type_desc, size*8 as 'size (KB)' from sys.database_filesgo

[結果]

[sys.master_files] name    type_desc    size (KB)tempdev    ROWS    10240templog    LOG    10240 [sys.database_files] name    type_desc    size (KB)tempdev    ROWS    84928templog    LOG    10240

 

クエリの実行結果を確認すると、sys.master_files の size 列の値と sys.database_files の size 列の値が異なっていることが分かると思います。

sys.master_files の size 列の値は、Tempdb の初期サイズであり、sys.database_files の size 列の値は、現在の実際のファイルサイズ (SQL Server Management Studio の データベースのプロパティに表示されている [初期サイズ (MB)]) となります。

 

Tempdb 上に一時テーブルのデータが残っている状態 (圧縮をすることが出来ない状態) で、DBCC SHRINKDATABASE コマンドを実行してみます。

 

[クエリ]

DBCC SHRINKDATABASE('tempdb')goselect name, type_desc, size*8 as 'size (KB)' from sys.master_fileswhere database_id = 2goselect name, type_desc, size*8 as 'size (KB)' from sys.database_filesgo

[結果]

[sys.master_files] name    type_desc    size (KB)tempdev    ROWS    83200templog    LOG    10240  [sys.database_files] name    type_desc    size (KB)tempdev    ROWS    83200templog    LOG    10240

 

クエリの実行結果を確認すると、sys.master_files の size 列の値と sys.database_files の size 列の値が同じ値になっていることが分かると思います。

つまり、Tempdb に対して 圧縮コマンドを実行した場合、圧縮コマンドで期待したファイルサイズまで圧縮されていなかったとしても、圧縮コマンド実行後のファイルサイズが、Tempdb の初期サイズとして更新されることになります。

今回の例では、Tempdbの物理ファイルサイズが 83 MB と大きくないサイズですが、仮に Tempdbの物理ファイルが 100 GB であった場合、でかつ、圧縮コマンドで 90 GB までしか圧縮できなかった場合は、Tempdb の初期サイズが 90 GB に更新されます。

そして、Tempdb の初期サイズが大きいなサイズの場合、次回 SQL Server 再起動時、Tempdb の再作成に時間を要し、SQL Server プロセスの起動までに時間を要するなどの悪影響を及ぼす可能性があります。

Tempdb データベース物理ファイル (tempdev)、トランザクションログファイル (templog) のサイズを明示的に指定して運用されている環境の場合、意図せず Tempdb の初期サイズが変わり、運用に支障を及ぼす可能性もあるかもしれません。

 

そのため、Tempdb を SQL Server サービスの再起動ではなく、圧縮コマンド、または、SSMS の圧縮タスクで圧縮される場合は、Tempdb の初期サイズが更新される点を注意してもらえればと思います。

 

[補足]

SQL Server Management Studio の データベースのプロパティに表示されている [初期サイズ (MB)] の値を明示的に変更しても、Tempdb の初期サイズは更新されます。

 

 

＋参考情報

sys.master_files (Transact-SQL) sys.database_files (Transact-SQL) DBCC SHRINKDATABASE (Transact-SQL) DBCC SHRINKFILE (Transact-SQL) データベースの圧縮

 

 

※ 本Blogの内容は、 2016年5月 現在の内容となっております。