msdbデータベースをメンテナンス

SQL Server Enterprise Manager でデータベースの復元をしようとした時、「データベースの復元」ダイアログの表示が遅いと感じたことは無いだろうか？
SQL Server 2000 では、バックアップするたびに、データベースとバックアップ先を記録する。
そこで、メンテナンス（不要な履歴を削除）することにより、表示が速くなる。

msdbにインデックス付与

バックアップ履歴の削除（sp_delete_backuphistory）を実行する前にインデックスを付与することをお勧めする。
以下(https://weblogs.sqlteam.com/geoffh/2008/01/21/msdb-performance-tuning/)より引用

/************************************************************************
*                                                                       *
*    Title:    msdb index creation                                      *
*    Author:    Geoff N. Hiten                                          *
*    Purpose: Index msdb database                                       *
*    Date:    12/12/2005                                                *
*    Modifications:                                                     *
*                                                                       *
*    01-01-2001                                                         *
*        Sample Entry                                                   *
*                                                                       *
*                                                                       *
************************************************************************/

use msdb 
go 

--backupset 
Create index IX_backupset_backup_set_id on backupset(backup_set_id) 
go 

Create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid) 
go 

Create index IX_backupset_media_set_id on backupset(media_set_id) 
go 

Create index IX_backupset_backup_finish_date on backupset(backup_finish_date) 
go 

Create index IX_backupset_backup_start_date on backupset(backup_start_date) 
go 

--backupmediaset 
Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id) 
go 

--backupfile 
Create index IX_backupfile_backup_set_id on backupfile(backup_set_id) 
go 

--backupmediafamily 
Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id) 
go 

--restorehistory 
Create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id) 
go 

Create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id) 
go 

--restorefile 
Create index IX_restorefile_restore_history_id on restorefile(restore_history_id) 
go 

--restorefilegroup 
Create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id) 
go 

/************************************************************************ 
*    End Script                                                         * 
************************************************************************/

sp_delete_backuphistory

バックアップ履歴を削除するには次のように行う。（例は、2008/01/01より古いバックアップ履歴を削除します）

USE msdb
EXEC sp_delete_backuphistory '2008/01/01'

以下msdnからの引用(Microsoft API and Reference Catalog)

sp_delete_backuphistory
oldest_date 以前のバックアップセットに関するバックアップと復元の履歴テーブルのエントリを削除します。バックアップや復元が行われるごとにバックアップと復元の履歴テーブルに行が追加されるので、sp_delete_backuphistory は msdb データベースの履歴テーブルのサイズを減らすのに使用できます。
構文
sp_delete_backuphistory [ @oldest_date = ] 'oldest_date'

ジョブとして登録

「msdbにインデックス付与」の引用元サイトを見られた方はお気づきだろうが、ジョブとして登録することが可能。

定期的に履歴削除を実行するジョブを追加する方法

SQL Enterprise Manager で対象サーバの「管理」−「SQL Server エージェント」−「ジョブ」を選択。
「ジョブ」を右クリックし「新規ジョブ(J)...」をクリック。（メニューバーの「操作」−「新規ジョブ(J)...」でも可）
「全般」タブの「名前」には、適当なジョブ名（"履歴のクリーンアップ"等）を入力。
「ステップ」タブをクリックし「新規(W)...」をクリック。
1. 「全般」タブの「ステップ名」には、適当なステップ名（"ステップ 1"等）を入力。
2. 「全般」タブの「種類」は、そのまま。（"Transact-SQL スクリプト(TSQL)"）
3. 「全般」タブの「データベース」は、"msdb"を選択。
4. 「全般」タブの「コマンド」に、下記ソースを貼り付け。念のため「解析」ボタンをクリックし確認。
5. 「詳細」タブの「成功時の動作」は、"成功をレポートしジョブを終了する"を選択。
6. 「OK」ボタンをクリック
「スケジュール」タブをクリックし、「新規スケジュール(W)...」をクリック。
1. 「名前」には、適当なスケジュール名（"スケジュール 1"等）を入力。
2. 「スケジュールの種類」は、「定期的」を選択し、「変更(A)...」をクリック。
  1. 実行間隔等を設定し「OK」ボタンをクリック。
3. 「OK」ボタンをクリック。
「OK」ボタンをクリック。

declare @oldest_date smalldatetime;
set @oldest_date = dateadd(dd, -7,getdate())
EXEC sp_delete_backuphistory @oldest_date;

上記の例は、@oldest_date が "-7"。つまり、１週間より前の履歴を削除する。

データベースの圧縮

ジョブが正常に稼動していることを確認できたら、データベースを圧縮しディスク容量を節約しよう。

seki-motoの日記