Over a million developers have joined DZone.

How to Reduce MSDB Size From 42Gb to 200Mb

The system database msdb is used by many components of SQL Server, such as Service Broker, SQL Server Agent, and Database Mail. It is worth noting that there is no ready-for-use service plan, which would take into account the above-mentioned, so it is important to perform regular preventive measures.

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

by Sergey Syrovatchenko

Recently, I had a spare minute to see why an old test server was running too slow… I had nothing to do with it, but I was very anxious to find out what was wrong with the server.

First thing, I opened Resource Monitor and looked at the overall load. The sqlserv.exe process took up 100% of CPU and generated a large disk queue exceeding 300… whereas the number greater than 1 is considered problematic.

When analyzing disk activity, I observed continuous IO operations in msdb:


I looked at the size of msdb:

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

and switch to the “facepalm” mode:

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

The data file takes up 42 GB. After a short break, I began to investigate the reason for such an «unhealthy» size of msdb, and how to overcome the problems with server performance.

I’ve checked resource-cost queries running on the server:

    , 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

The system stored procedure comes first:

session_id db       status   text                                  cpu_time    total_elapsed_time reads   writes    logical_reads
---------- -------- -------- ------------------------------------- ----------- ------------------ ------- --------- ---------------
62         msdb     running  create procedure [sys].[sp_cdc_scan]  111638      6739344            618232  554324    2857923422

I am referring to CDC (Change Data Capture), which is used as a tool for capturing data changes. CDC is based on reading the transaction log and always works asynchronously through the use of Service Broker.

Upon sending Event Notification to Service Broker, the message may not reach the destination due to configuration problems, and then it is archived in a separate table. In general, if Service Broker is frequently used, you need to monitor state of sys.sysxmitqueue. When there is constant increase of data in the table, it is either a bug or we use Service Broker incorrectly.

Execution plan from dbForge Studio:

This query returns the top list of objects and their size:

USE msdb

    , obj = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , o.[type]
    , i.total_rows
    , i.total_size
FROM sys.objects o
        , 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 running the query, 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 leave all the tables in this list without attention. But first, we need to fix the issue with sys.sysxmitqueue.

We cannot delete data directly from sys.sysxmitqueue, because this table is a system object (S). After some searching, I found a way to get SQL Server to clear the table. When creating a new Service Broker, all the messages associated with the old broker will be deleted.

USE msdb


But, before executing the command, it is strongly recommended to disable SQL Server Agent and switchSQL Server to Single-User Mode. It took me approximately 10 minutes to delete the existing messages in all queues of Service Broker. On completion, I received the following message:

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

After restarting the SQL Server service all performance problems were gone… my heart filled with joy and we could put a period here. But, we should remember that it was not the only large table in msdb. Let’s look at the rest.

Those who like to send mail via Database Mail should know that SQL Server logs and keeps all the mailing in msdb. All e-mail attachments that are sent with the letter body are neatly stored there. Therefore, it is recommended to regularly delete this information. This can be done by hand, that is, look out for tables that 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

Alternatively, use ready-to-use stored procedures sysmail_delete_mailitems_sp and sysmail_delete_log_sp:

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 history of SQL Server Agent tasks is also stored in msdb. When there are too many entries in the log, they are hard to work with, so I try to clean it regularly with sp_purge_jobhistory:

SET @DateBefore = DATEADD(DAY, -7, GETDATE())

EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @DateBefore

I should also mention about the information on backups that are logged in msdb. The old backup records can be deleted with sp_delete_backuphistory:

SET @DateBefore = DATEADD(DAY, -120, GETDATE())

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @DateBefore

But, we should remember about one nuance–when you delete a database, its backup info remain in msdb:

USE [master]

IF DB_ID('backup_test') IS NOT NULL BEGIN
    DROP DATABASE [backup_test]

CREATE DATABASE [backup_test]

BACKUP DATABASE [backup_test] TO DISK = N'backup_test.bak'

DROP DATABASE [backup_test]

FROM msdb.dbo.backupset
WHERE database_name = 'backup_test'

In my case, when databases are frequently created and deleted, it can lead to the increase of msdb. In the situation where backup information is of no use, it can be removed by the stored sp_delete_database_backuphistory:

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


The system database msdb is used by many components of SQL Server, such as Service BrokerSQL Server Agent, and Database Mail. It is worth noting that there is no ready-for-use service plan, which would take into account the above-mentioned, so it is important to perform regular preventive measures. In my case, after deleting the unnecessary information and shrinking the file, the size of msdb became 200 MB against the original 42 GB.

I hope this post provides an instructive story about the benefits of a permanent administration of both user and system databases.

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

ms sql,sql server,database management,databases

Published at DZone with permission of Jordan Sanders. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}