Should I shrink Msdb?
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. If, after reading this tutorial, a more aggressive housekeeping routine is put in place to clear out some of the history tables, then maybe a one-off shrink is warranted.
What does shrinking a DB do?
The Shrink Database task reduces the size of SQL Server database data and log files. Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file.
What Msdb contains?
The following list outlines the information contained in the msdb database: SQL Server Agent information such as jobs, job history, job schedules, operators, alerts, and notifications. Database maintenance plan information such as maintenance plan jobs and history. Replication publishers and distributor information.
Is it good or bad to shrink MSDB database?
After running above script, our msdb database which was of 18 GB came down to only 600 MB. I strongly encourage everyone to read my blog post about SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance, which explains why shrinking database is not a good idea and how it adversely affect performance.
What should I do if my MSDB is too big?
We actually reached the point where the data drive free space was consumed. First, step is to do a full backup of MSDB. You should always make a backup before you plan on doing any changes with a system database. You should also have a plan to restore the system database just in case you have to implement it. So Where’s the Beef?
How to stop growing MSDB database by removing history?
The resolution was to delete data from various history table of the MSDB. First, we took back of our database and after that I deleted all the history from various tables excluding last 30 days data. Here is the script which I used.
How to find the file size of MSDB?
I ran the following query to see the file size of all database objects within the MSDB database. You can find this query written by Jeremy Kadlec in MSSQLTIP #1461 I also highly recommend reading that tip as it will provide some more helpful information to troubleshoot a large MSDB.