Why is MSDB database so large?

Question

MSDB stores backup and restore history. Imagine a server with 2000 databases that takes a log backup of all of them every 15 minutes. That's 192,000 new rows into the backupset and backupmediaset tables EVERY DAY! Keeping a few weeks or months of history can cause the size of MSDB to get very large. 31 мар. 2020 г.

Can I shrink MSDB database?

Can you shrink the database and transaction log for the MSDB database? The data and log files for the MSDB database can be resized. Like any other database, this isn't an operation that should be needed very often.

How do I clean up MSDB database?

You could use the database maintenance plan – Cleanup History task and configure it to clean up backup tables automatically. Select the Cleanup History task in the maintenance plan and click Next. On the next page, select the value for the “Remove historical data older than” parameter.

What causes Msdb to grow?

Backup and restore history is the most common reason for the msdb database to bloat, however Database Engine Tuning Advisor, SQL Server Agent history, service broker, log shipping and lack of index maintenance can all contribute to excessive growth of msdb and impact the performance of the database.

What is the purpose of Msdb DB?

The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail. For example, SQL Server automatically maintains a complete online backup-and-restore history within tables in msdb.

Expert · Answer