SQLServer2008完全復旧モデルにおけるデータ圧縮

随分前に、SQLServer2008完全復旧モデルにおけるデータ圧縮について説明すると言っておきながら、

かなり月日が経ってしまった。記憶が薄れないうちに記録として残しておく。

前回の説明(単純モデルでのデータ圧縮)

0 データベースのモデルを完全にしておく。

1 DBの肥大化状態を確認

C:\>sqlcmd -S sim7\sim7db -d TEST -Usa -P"パスワード" -Q "exec sp_spaceused "
database_name
                        database_size      unallocated space
---------------------------------------------------------------------------------------------------------
----------------------- ------------------ ------------------
TEST
                        630.00 MB          58.60 MB
reserved           data               index_size         unused
------------------ ------------------ ------------------ ------------------
1432 KB            592 KB             680 KB             160 KB

→DBのサイズが630MByteまで肥大化している。

C:\>sqlcmd -S sim7\sim7db -Usa -P"パスワード" -Q "dbcc sqlperf ('LOGSPACE')"
Database Name
                        Log Size (MB)  Log Space Used (%) Status
---------------------------------------------------------------------------------------------------------
----------------------- -------------- ------------------ -----------
   〜
TEST
                             569.99219          26.979195           0
   〜
(10 行処理されました)

→トランザクションログのサイズが569MByte(使用率は26%)まで肥大化している。

2 DB&トランザクションログのバックアップ

完全復旧モデルは、その名のとおり、障害から完全に復旧することを目的とするモデルであることから、

トランザクションログは完全な形で記録されていることを前提としている。従って、単純モデルと異なり、

(1) トランザクションログの切り捨て(※)はトランザクションログのバックアップ時に行われる。

→単純モデルでは、トランザクションログのバックアップは行えない。というのは、チェックポイント時に

自動でログの切り捨てが行われるからだ。

(2) 単純モデルとは異なり、自動でトランザクションログの切り捨ては行われない。

※ 切り捨てというのは、アクティブでなくなったトランザクションログを破棄することであり、ファイル

 システム上のログファイルのサイズを縮小するという意味ではないことに注意されたし。

DBファイル自体の縮小は、単純モデルと同様、Transact-SQL”DBCC SHRINKDATABASE”で実施する。

C:\>sqlcmd -S sim7\sim7db -Usa -P"パスワード" -Q "BACKUP DATABASE TEST TO DISK = 'C:\TEMP\testbk.bak'"
データベース 'TEST' の 200 ページ、ファイル 1 のファイル 'test' を処理しました。
データベース 'TEST' の 1 ページ、ファイル 1 のファイル 'test_log' を処理しました。
BACKUP DATABASE により 201 ページが 0.904 秒間で正常に処理されました (1.729 MB/秒)。

3 トランザクションログの切り捨て確認

C:\>sqlcmd -S sim7\sim7db -d TEST -Usa -P"パスワード" -Q "exec sp_spaceused "
database_name
                        database_size      unallocated space
---------------------------------------------------------------------------------------------------------
----------------------- ------------------ ------------------
TEST
                        630.00 MB          58.60 MB
reserved           data               index_size         unused
------------------ ------------------ ------------------ ------------------
1432 KB            592 KB             680 KB             160 KB

→DBファイル自体には変化なし。

C:\>sqlcmd -S sim7\sim7db -Usa -P"パスワード" -Q "dbcc sqlperf ('LOGSPACE')"
Database Name
                        Log Size (MB)  Log Space Used (%) Status
---------------------------------------------------------------------------------------------------------
----------------------- -------------- ------------------ -----------
   〜
TEST
                             569.99219         0.83034652           0
   〜

(10 行処理されました)

→トランザクションログの使用率が26%から0.8%程度まで下がった。(切り捨てが行われた)

更にDBファイルとトランザクションログのファイルサイズを縮小したい場合は、

(1)DBCC SHRINKDATABASEでDBとトランザクションログを縮小する。以下、例。

sqlcmd -S sim7\sim7db -Usa -P”パスワード” -Q “DBCC SHRINKDATABASE(TEST, 10)”

(2)DBCC SHRINKFILEでトランザクションログのみを縮小する。以下、例。

sqlcmd -S sim7\sim7db -Usa -P”パスワード” -Q “USE TEST;DBCC SHRINKFILE (TEST_LOG,5)”

という方法が考えられる。