最近、古いテストサーバーが恥知らずにスローダウンしている理由を確認する時間がありました...私はそれとは何の関係もありませんでしたが、何が悪いのかを理解するためにスポーツの興味に打ち勝ちました。
まず、 リソースモニターを開き、全体的な負荷を調べました。 sqlserv.exeプロセスはCPUを100％未満でロードし、300を超える大きなディスクキューを形成しました... 1を超える値はすでに問題があると考えられているという事実にもかかわらず。
ディスクアクティビティを分析するとき、 msdbで継続的なIO操作に気付きました：
D:\SQL_2012\SYSTEM\MSDBData.mdf D:\SQL_2012\SYSTEM\MSDBLog.ldf
msdbのサイズを見て：
SELECT name, size = size * 8. / 1024, space_used = FILEPROPERTY(name, 'SpaceUsed') * 8. / 1024 FROM sys.database_files
ハンドフェイスモードをオンにします。
name size space_used ------------ -------------- --------------- MSDBData 42626.000000 42410.374395 MSDBLog 459.125000 6.859375
データファイルは42 GBを占有しました...少し休止した後、このような不健全なmsdbの原因とサーバーパフォーマンスの問題を克服する方法を理解し始めました。
サーバーで実行されたリソース集約型クエリを確認しました。
SELECT r.session_id , db = DB_NAME(r.database_id) , r.[status] , p.[text] --, sql_text = SUBSTRING(p.[text], (r.statement_start_offset / 2) + 1, -- CASE WHEN r.statement_end_offset = -1 -- THEN 2147483647 -- ELSE ((r.statement_end_offset - r.statement_start_offset) / 2) + 1 -- END) , r.cpu_time , r.total_elapsed_time , r.reads , r.writes , r.logical_reads FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) p WHERE r.[sql_handle] IS NOT NULL AND r.session_id != @@SPID ORDER BY logical_reads DESC
最初の場所は、システムストアドプロシージャです。
db status text elapsed_time reads writes logical_reads -------- -------- ------------------------------------- ------------ ------- ------- --------------- msdb running create procedure [sys].[sp_cdc_scan] 6739344 618232 554324 2857923422
名前から、 変更されたデータを追跡する手段として使用されるCDC （ Change Data Capture ）について話していると推測できます。 CDCはトランザクションログの読み取りに基づいており、常にService Brokerを使用して非同期的に実行されます。
構成の問題により、 イベント通知をService Brokerに送信しようとすると、メッセージが宛先に到達せず、別のテーブルにアーカイブされることがあります...非常に退屈です...一般に、 Service Brokerが頻繁に使用される場合、 sys.sysxmitqueueを監視する必要があります。 このテーブルのデータが絶えず増加している場合、これはバグであるか、 Service Brokerを誤って使用しています。
ここで、このクエリを使用して、オブジェクトとそのサイズのリストを返すことができます。
USE msdb GO SELECT TOP(10) o.[object_id] , obj = SCHEMA_NAME(o.[schema_id]) + '.' + o.name , o.[type] , i.total_rows , i.total_size FROM sys.objects o JOIN ( SELECT i.[object_id] , total_size = CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(18,2)) , total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END) FROM sys.indexes i JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id JOIN sys.allocation_units a ON p.[partition_id] = a.container_id WHERE i.is_disabled = 0 AND i.is_hypothetical = 0 GROUP BY i.[object_id] ) i ON o.[object_id] = i.[object_id] WHERE o.[type] IN ('V', 'U', 'S') ORDER BY i.total_size DESC
実行後、次の結果を受け取りました。
object_id obj type total_rows total_size ----------- -------------------------------- ---- ------------ ----------- 68 sys.sysxmitqueue S 6543502968 37188.90 942626401 dbo.sysmail_attachments U 70 2566.00 1262627541 dbo.sysmail_attachments_transfer U 35 2131.01 1102626971 dbo.sysmail_log U 44652 180.35 670625432 dbo.sysmail_mailitems U 19231 123.39 965578478 dbo.sysjobhistory U 21055 69.05 366624349 dbo.backupfile U 6529 14.09 727673640 dbo.sysssispackages U 9 2.98 206623779 dbo.backupset U 518 1.88 286624064 dbo.backupfilegroup U 3011 1.84
このリストのすべてのテーブルを無視するわけではないことをすぐに言わなければなりません。 ただし、最初にsys.sysxmitqueueをクリアする必要があります。
このテーブルはシステムオブジェクト（S）であるため、 sys.sysxmitqueueからデータを直接削除することはできません。 簡単な検索の後、 SQL Serverでこのテーブルをクリアする方法を見つけました。 新しいService Brokerを作成すると、古いブローカーに関連付けられているすべてのメッセージが自動的に削除されます。
USE msdb GO ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE
ただし、コマンドを実行する前に、 SQL Serverエージェントを無効にし、 SQL Serverをシングルユーザーモードにすることを強くお勧めします。 すべてのService Brokerキューの既存のメッセージを削除するには、約10分かかりました。 完了すると、次のメッセージを受け取りました。
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
SQL Serverサービスを再起動した後、パフォーマンスの問題はすべてなくなりました...魂は幸せで、これで終わりです。 ただし、これはmsdbの唯一の大きなテーブルではないことに注意してください 。 残りに対処しましょう...
データベースメールを使用してメールを送信する場合は、 SQL Serverがすべてのメーリングリストをmsdbに記録して保存することを知っておく必要があります。 レターの本文とともに送信されるすべてのメールの添付ファイルは慎重に保管されます...したがって、この情報を定期的に消去することを強くお勧めします。 手でこれを行うことができます、すなわち クリーニングする必要があるテーブルを確認します。
SELECT o.name, p.[rows] FROM msdb.sys.objects o JOIN msdb.sys.partitions p ON o.[object_id] = p.[object_id] WHERE o.name LIKE 'sysmail%' AND o.[type] = 'U' AND p.[rows] > 0
または、既製のストアドプロシージャsysmail_delete_mailitems_spおよびsysmail_delete_log_spを使用します。
DECLARE @DateBefore DATETIME SET @DateBefore = DATEADD(DAY, -7, GETDATE()) EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DateBefore --, @sent_status = 'sent' EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @DateBefore
SQL Serverエージェントのジョブ履歴もmsdbに保存されます。 ログに多くのエントリがある場合、それを操作するのはあまり便利ではないので、定期的にsp_purge_jobhistoryをきれいにしようとします ：
DECLARE @DateBefore DATETIME SET @DateBefore = DATEADD(DAY, -7, GETDATE()) EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @DateBefore
msdbに記録されるバックアップ情報についても言及する価値があります。 古いバックアップエントリはsp_delete_backuphistoryで削除できます。
DECLARE @DateBefore DATETIME SET @DateBefore = DATEADD(DAY, -120, GETDATE()) EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @DateBefore
ただし、1つのニュアンスを覚えておく必要があります。データベースを削除しても、そのバックアップに関するレコードはmsdbから削除されません。
USE [master] GO IF DB_ID('backup_test') IS NOT NULL BEGIN ALTER DATABASE [backup_test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE [backup_test] END GO CREATE DATABASE [backup_test] GO BACKUP DATABASE [backup_test] TO DISK = N'backup_test.bak' GO DROP DATABASE [backup_test] GO SELECT * FROM msdb.dbo.backupset WHERE database_name = 'backup_test'
私の場合、データベースが頻繁に作成および削除されると、 msdbが増大する可能性があります。 バックアップ情報が不要な状況では、sp_delete_database_backuphistory ストレージを使用して削除できます。
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'backup_test'
小さな結論...
msdbシステムデータベースは 、 Service Broker 、 SQL Server Agent、 Database Mailなど、多くのSQL Serverコンポーネントで使用されます 。 上記を考慮した既成のサービスプランがないことに注意してください。したがって、予防措置を定期的に実行することが重要です。 私の場合、不要な情報を削除してファイルを切り捨てると、 msdbのサイズは元の42 GBに対して200 MBになりました。
この投稿から、ユーザーだけでなくシステムデータベースも含めて、永続的な管理の利点について有益な話が出たことを願っています。
この記事を英語圏の聴衆と共有したい場合：
MSDBサイズを42Gbから200Mbに減らす方法