The story about msdb size of 42 GB

Recently, there was a minute to see why the old test server shamelessly slowed down ... I had nothing to do with it, but I was overwhelmed with sports interest to figure out what was wrong with it.

First of all, opened the Resource Monitor and looked at the total load. The sqlserv.exe process loaded the CPU at 100% and formed a large disk queue that was over 300 ... despite the fact that a value above one is already considered problematic.

When analyzing disk activity, I noticed continuous IO operations in msdb :
')

D:\SQL_2012\SYSTEM\MSDBData.mdf D:\SQL_2012\SYSTEM\MSDBLog.ldf 

Looked at the size of msdb :

 SELECT name, size = size * 8. / 1024, space_used = FILEPROPERTY(name, 'SpaceUsed') * 8. / 1024 FROM sys.database_files 

and turned on hand-face mode:

 name size space_used ------------ -------------- --------------- MSDBData 42626.000000 42410.374395 MSDBLog 459.125000 6.859375 

The data file occupied 42 GB ... Taking a short pause, I began to understand the reason for such an unhealthy msdb volume and how to overcome problems with server performance.

Checked resource-intensive requests that were performed on the server:

 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 

In the first place is the system stored procedure proudly:

 db status text elapsed_time reads writes logical_reads -------- -------- ------------------------------------- ------------ ------- ------- --------------- msdb running create procedure [sys].[sp_cdc_scan] 6739344 618232 554324 2857923422 

From the name of which you can guess that we are talking about the CDC ( Change Data Capture ), which is used as a means to track the changed data. CDC is based on reading the transaction log and always works asynchronously by using Service Broker .

Due to configuration problems, when you try to send Event Notification to Service Broker , the message may not reach the destination and then it will be archived in a separate table ... Badly said ... In general, if Service Broker is used frequently, then you need to monitor sys.sysxmitqueue . When in this table there is a constant increase in data, then this is either a bug, or we incorrectly use Service Broker .

This query can return a list of objects and their size:

 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 

After execution, I obtained the following results:

 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 

I must say that we will not disregard all the tables in this list. But first you need to clear sys.sysxmitqueue .

Delete data directly from sys.sysxmitqueue will not work, because this table is a system object (S) . After a brief search, I found a way to get SQL Server to clear this table. When creating a new Service Broker , all messages associated with the old broker are automatically deleted.

 USE msdb GO ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE 

But before executing the command, it is strongly recommended to disable SQL Server Agent and transfer SQL Server to Single-User Mode . Deleting existing messages in all Service Broker queues took me about ten minutes. Upon completion, I received the following message:

 Nonqualified transactions are being rolled back. Estimated rollback completion: 100%. 

After the restart of the SQL Server service, all the performance problems were gone ... my soul was happy and we could put an end to this. But remember that this was not the only large table in msdb . Let's deal with the rest ...

For those who like to send mail through Database Mail, you need to know that SQL Server logs and stores all mailing lists in msdb. All email attachments that are sent with the body of the letter are neatly stored there ... Therefore, it is highly recommended to periodically clear this information. You can do it with your hands, i.e. see which tables need to be cleaned:

 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 

Or use the ready-made sysmail_delete_mailitems_sp and sysmail_delete_log_sp stored procedures:

 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 

The SQL Server Agent job history is also saved in msdb . When there are a lot of records in the log, it is not very convenient to work with it, so I try to clean it sp_purge_jobhistory from time to time :

 DECLARE @DateBefore DATETIME SET @DateBefore = DATEADD(DAY, -7, GETDATE()) EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @DateBefore 

Still it is necessary to mention, about the information on backup copies which are logged in msdb . You can delete old entries about created backups sp_delete_backuphistory :

 DECLARE @DateBefore DATETIME SET @DateBefore = DATEADD(DAY, -120, GETDATE()) EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @DateBefore 

But one thing to keep in mind is that when you delete a database, its backup records are not removed from 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' 

In my case, when databases are often created and deleted, this can lead to an increase in msdb . In a situation when backup information is not needed, you can delete it with the sp_delete_database_backuphistory :

 EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'backup_test' 

Small conclusions ...

The msdb system database is used by many components of SQL Server , such as Service Broker , SQL Server Agent, and Database Mail , for example. It should be noted that there is no ready maintenance plan that would take into account what was written above, so it is important to periodically take preventive measures. In my case, after removing unnecessary information and truncating a file, the size of msdb became 200 MB against the original 42 GB.

I hope this post left an instructive story about the benefits of continuous administration ... not only user but also system databases.

If you want to share this article with an English-speaking audience:
How to reduce MSDB size from 42Gb to 200Mb