• Adam Thurgar

Why is my msdb so large?


msdb is a system database used mainly by the SQL Server Agent to store system activities like SQL Server jobs, mail, service broker, maintenance plans, user and system database backup history, etc.

Like any database it needs to be monitored and maintained. You may find that your msdb database has grown significantly.

What may have caused this could be:

- retention of backup history

- maintenance plan logs

- database mail

- job history

To reclaim control over the space in msdb you need to look at deleting data out of the large tables. By looking at the table sizes you can determine what is causing your issues. Most of the time it is just a matter of running a stored procedure to do this cleanup.

For example:

a) sp_delete_backuphistory

b) sp_maintplan_delete_log

c) sysmail_delete_mailitems_sp

d) sp_purge_jobhistory

Then you could shrink the msdb data and log file to reclaim disk space.


21 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t