Find SQL Server MSDB Database Size and Largest Table Across SQL Server Instances

By:   |   Updated: 2019-05-07   |   Comments   |   Related: More > System Databases


Problem

As important as all the SQL Server system databases are, the MSDB database keeps important information such as: execution history of SQL Server Agent Jobs, execution history of backups and restores, SQL Server Integration Services package execution, etc.

However, I have seen cases where issues in a particular server are caused by MSDB, but how you may ask? Well, this system database should have a regular maintenance cycle (pretty much like the user databases themselves), to avoid things like the following:

  • Large backup cycles due to the MSDB database being hundreds of GBs in size.
  • Excessive resources utilization by queries that access very large tables in MSDB.
  • Even something as simple as "View History" of an SQL Server Agent Job in SQL Server Management Studio, can give you a hard time if the sysjobhistory table is large and you really need to confirm the actual execution history of a particular job.
Solution

In this tip I am not going to address the solution for every possible issue that an unmaintained MSDB can generate; instead, I am going to present a solution that can help you gain visibility of an instance (or set of instances) that can potentially give you a hard time as a Database Administrator and that probably is screaming for some sort of maintenance (depending on the particular scenario).

Within the PowerShell code, I have marked sections of the code to try to explain each one of them at a high level.

#Section 1

The very first thing to address is the creation of the table that will contain the MSDB information of all the SQL Server instances under your care. Ideally, this one should be hosted in a central environment under your control.

#Section 2

In this section you prepare the list of instances that are under your support and from where the MSDB information will be retrieved.

You can feel free to change this mechanism with another one of your choosing. Perhaps you can have a .txt file with the list of instances and you feed the variable with that set.

#Section 3

This section contains the query that will gather the information from the MSDB, for each instance. As it is, it will attempt to collect the current size of the msdb database, the name of the largest table (in size) in the entire database, how many rows that table has, and the current size of that largest table. You can run the query below on any SQL Server instance to see the output.

USE msdb;

DECLARE @msdbTableData TABLE(
   table_name VARCHAR(64) NOT NULL,
   table_size DECIMAL(10,2),
   table_rows INT NOT NULL
);

INSERT INTO @msdbTableData 
SELECT TOP 1
    t.name,
    CONVERT (DECIMAL(10,2), (SUM(a.total_pages) * 8.0)/1024),
    SUM(p.rows) AS 'table_rows'
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND  
    i.index_id <= 1
GROUP BY t.NAME, i.object_id, i.index_id, i.name
ORDER BY 2 DESC;

SELECT SERVERPROPERTY('SERVERNAME') AS 'instance',
(SELECT CONVERT(DECIMAL(10,2), SUM((size*8.0)/1024)) FROM sys.master_files WHERE database_id = 4) AS 'size',
(SELECT table_name FROM @msdbTableData) AS 'largest_table',
(SELECT table_size FROM @msdbTableData) AS 'table_size',
(SELECT table_rows FROM @msdbTableData) AS 'table_rows',
(SELECT GETDATE()) AS 'timestamp';

You can feel free to add more information that will serve your own purpose, just make sure to update the table schema, the query and the section where the final insert statement is built and passed to the central instance.

#Section 4

For each instance that you have specified in section 2, the "for each" statement will attempt to go 1 by 1 and fetch the MSDB information. After it has been collected, the respective "insert" statement is built and passed to the central instance to store such information.

Complete PowerShell Script to Check MSDB Databases

  • You might want to modify the Invoke-Sqlcmd command to include your own specific credentials to establish the proper connection.
  • Replace "xxxx" with the values that fit your use case.
  • I use the schema "Monitoring" for the sake of maintaining a certain order/structure, you can either get rid of it or simply use another schema name that you like (just make sure that such schema exists first, and you should be good to go). 
$server = "xxxx"
$inventoryDB = "xxxx"

#Section 1 START
#Create the central table where you will store the information gathered from all the instances
$msdbTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'msdb' AND xtype = 'U')
CREATE TABLE [Monitoring].[MSDB](
   [instance]      [VARCHAR](128) NOT NULL,
   [size]          [DECIMAL](10,2) NOT NULL,
   [largest_table] [VARCHAR](64) NOT NULL,
   [table_rows]    [INT] NOT NULL,
   [table_size]    [DECIMAL](10,2) NOT NULL,
   [timestamp]     [DATETIME] NOT NULL
) ON [PRIMARY]
GO
"

Invoke-Sqlcmd -Query $msdbTableCreationQuery -Database $inventoryDB -ServerInstance $server

#Clean the Monitoring.MSDB table
Invoke-Sqlcmd -Query "TRUNCATE TABLE Monitoring.MSDB" -Database $inventoryDB -ServerInstance $server
#Section 1 END

#Section 2 START
#Fetch all the instances from the list you specify
<#
   This is an example of the result set that your query must return
   ###################################################
   # name                     #  instance            #
   ###################################################
   # server1.domain.net,45000 #  server1             #
   # server1.domain.net,45001 #  server1\MSSQLSERVER1# 
   # server2.domain.net,45000 #  server2             #
   # server3.domain.net,45000 #  server3             #
   # server4.domain.net       #  server4\MSSQLSERVER2#
   ###################################################            
#>

#Put in your query that returns the list of instances as described in the example result set above
$instanceLookupQuery = "SELECT name, instance FROM instances" 
$instances = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query $instanceLookupQuery
#Section 2 END

#Section 3 START
#For each instance, fetch the desired information of MSDB
$msdbInformationQuery = "
USE msdb;

DECLARE @msdbTableData TABLE(
   table_name VARCHAR(64) NOT NULL,
   table_size DECIMAL(10,2),
   table_rows INT NOT NULL
);

INSERT INTO @msdbTableData 
SELECT TOP 1
    t.name,
    CONVERT (DECIMAL(10,2), (SUM(a.total_pages) * 8.0)/1024),
    SUM(p.rows) AS 'table_rows'
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND  
    i.index_id <= 1
GROUP BY t.NAME, i.object_id, i.index_id, i.name
ORDER BY 2 DESC;

SELECT SERVERPROPERTY('SERVERNAME') AS 'instance',
(SELECT CONVERT(DECIMAL(10,2), SUM((size*8.0)/1024)) FROM sys.master_files WHERE database_id = 4) AS 'size',
(SELECT table_name FROM @msdbTableData) AS 'largest_table',
(SELECT table_size FROM @msdbTableData) AS 'table_size',
(SELECT table_rows FROM @msdbTableData) AS 'table_rows',
(SELECT GETDATE()) AS 'timestamp';
"
#Section 3 END

#Section 4 START
foreach ($instance in $instances){
   $results = Invoke-Sqlcmd -Query $msdbInformationQuery -ServerInstance $instance.name -ErrorAction Stop -querytimeout 30

   #Perform the INSERT in the Monitoring.MSDB table only if it returns information
    if($results.Length -ne 0){

      #Build the insert statement
      $insert = "INSERT INTO Monitoring.MSDB VALUES"
      foreach($result in $results){        
         $insert += "
         (
         '"+$result['instance']+"',
         "+$result['size']+",
         '"+$result['largest_table']+"',
         "+$result['table_rows']+",
         "+$result['table_size']+",
            '"+$result['timestamp']+"'
            ),
         "
       }

      #Store the results in the local Monitoring.MSDB table created in Section 1
      Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $server -Database $inventoryDB
   }
}
#Section 4 END

Write-Host "Done!"

After executing the script, the MSDB table should contain values that look like this:

MSDB values after running the script.
  • Columns "size" and "table_size" are in MB.
  • This was tested against SQL Server instances with versions 2005, 2008, 2008 R2, 2012, 2014, 2016 and 2017.

Judging by the look of the output, you can determine the following:

  • Instance "TEST1" has an msdb database that doesn’t look like is going to do any harm any time soon.
  • Instance "TEST2" is almost reaching the 50GB in size, with the "sysjobhistory" table at 18.5GB. You would have to mark this one as a potential candidate for a maintenance cycle.
    • It is almost certain that if you try to visualize the history of job execution in SSMS, for any job, it will take a long time to show anything.
  • Instance "TEST3" is almost reaching the 60GB in size, with the "syssssislog" table at 8.76GB. You would have to mark this one as a potential candidate for a maintenance cycle.
    • It is almost certain that if you try to issue a query that consumes the sysssislog table, it will give you a hard time and it can potentially consume a lot of resources to get the job done.
Next Steps
  • The output of the PowerShell script has very basic information that I considered important first hand. However, you can add as much information you want to fit your particular use case.
  • You can automate this solution and build a reporting/alerting mechanism around it, so it can warn you about any potential cases that can be treated as a "red flag".
  • With this you gain the visibility of the state of the MSDB across all your SQL Server instances, but that is just the first chapter of the whole story. The very next step is actually being able to address all those cases that require it, in the fashion of:
    • Confirming why the database is so large?
    • Are there tables that definitely need to be that large?
    • Should a purge job/mechanism be put in place to retain only a certain period of information?
    • Are there index structures that can help against queries that consume large tables?
  • Here are some useful resources written by members of the MSSQLTips community, that can help you with the maintenance needs:


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Alejandro Cobar Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2019-05-07

Comments For This Article

















get free sql tips
agree to terms