Resolving the large space usage of the MSDB database in SQL SERVER

May 18, 2025#MSSQL #数据库15

AI Translation

This post is translated from Chinese into English through AI.View Original

AI-generated summary

The MSDB database in SQL Server can sometimes occupy a large amount of disk space, often due to large backup logs. To clean up this space, you can truncate several tables related to backups and restores, such as `backupfile`, `restorehistory`, and others. Before performing this cleanup, ensure that the backup logs are no longer needed, as this action is irreversible. If you don't need to log backups, you can disable logging with the command `dbcc traceon(3226,-1)`. After cleanup, if the database size hasn't decreased, consider shrinking it, although it usually shrinks automatically.

Sometimes you may encounter the MSDB database in SQL SERVER occupying a large amount of disk space, leading to a full disk. The following methods can be used for cleanup.
The likely reason is that the backup logs are too large.

Note: Please make sure you know what you are doing before cleaning up.#

Only execute the cleanup if you are certain that the backup logs are useless; this operation cannot be undone.#

truncate table backupfile;
truncate table backupfilegroup;
truncate table restorefile;
truncate table restorefilegroup;
truncate table restorehistory;
truncate table backupset;
truncate table backupmediafamily;
truncate table backupmediaset;

If logging is not needed, you can disable logging with the following command.#

dbcc traceon(3226,-1)

The cleanup is now complete. If the database has not shrunk, you can shrink it; generally, it will shrink automatically.