How to reduce MSDB size from 42Gb to 200Mb

Recently I’ve got a spare minute to see why an old test server was running too slow… I had nothing to do with this, 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:

and switch to the “facepalm” mode:

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

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

The system stored procedure comes first:

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:

After running the query, I obtained the following results:

I must say that we will not leave all the tables in this list without attention. But first we need to fix 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 how 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.

But before executing the command, it is strongly recommended to disable SQL Server Agent and switch SQL 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:

After restarting the SQL Server service all performance problems were gone … the 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 keep 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 hands, that is, look out for tables that need to be cleaned:

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

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:

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:

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

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:

Summary

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. In my case, after deleting the unnecessary information and shrink the file, the size of msdb became 200 MB against the original 42 GB.

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

One Response to “How to reduce MSDB size from 42Gb to 200Mb”

  1. Ian Says:

    Excellent thanks

Leave a Comment