What causes Msdb to grow?

Question

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. 10 июл. 2015 г.

Why is Msdb so large?

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.

What is stored in MSDB?

msdb. The msdb database is a system database that is used by several SQL Server components such as the SQL Server Agent service. In addition to SQL Server Agent configuration and task information, replication, log shipping, and maintenance plan data are stored in the msdb database.

What is Msdb used for?

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.

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.

Expert · Answer