Quick Scan Report – User Tables in MSDB

What this check looks for

Tables in msdb where is_ms_shipped is not 1: tables somebody created rather than tables SQL Server ships.

Why it matters

The same portability problem as a table in master, with two extra wrinkles that come from what msdb is for.

The shared problem first. A table in msdb is not in your database backups, it does not move when you restore your user databases onto a new server, and when it goes missing during a migration the thing that breaks is whatever quietly depended on it.

msdb adds two of its own:

• msdb is not a quiet database. It holds Agent job definitions and history, Database Mail and its entire message archive, backup and restore history, maintenance plan packages, log shipping monitor tables and SSIS package metadata. It is written to constantly. A user table sharing that space competes with it, and a user table that grows contributes to an msdb that is already prone to growing for reasons nobody watches.
• msdb gets cleaned up by procedures that do not know about your table. The supported history cleanup procedures delete from the tables they own. If your table has a foreign key to one of them, or your process assumes history that has since been purged, the failure is confusing and intermittent.

There is also a security consideration. Permissions in msdb tend to be broader than in a user database, because Agent and Database Mail need them, so data put there is often readable by more people than the same data in a user database would be.

msdb gets user tables for a specific reason more often than master does: somebody writing a job monitoring or alerting script puts its logging table next to the job tables it reads. That is understandable, and the table still belongs somewhere else.

How to confirm it yourself

USE [msdb];
GO

SELECT s.[name]        AS [schema_name],
       t.[name]        AS [table_name],
       t.[create_date],
       t.[modify_date],
       p.[rows],
       CAST(SUM(a.[total_pages]) * 8.0 / 1024 AS DECIMAL(10,1)) AS [size_mb]
  FROM sys.tables AS t WITH (NOLOCK)
 INNER JOIN sys.schemas AS s WITH (NOLOCK)
         ON s.[schema_id] = t.[schema_id]
 INNER JOIN sys.indexes AS i WITH (NOLOCK)
         ON i.[object_id] = t.[object_id]
 INNER JOIN sys.partitions AS p WITH (NOLOCK)
         ON p.[object_id] = i.[object_id] AND p.[index_id] = i.[index_id]
 INNER JOIN sys.allocation_units AS a WITH (NOLOCK)
         ON a.[container_id] = p.[partition_id]
 WHERE t.[is_ms_shipped] <> 1
 GROUP BY s.[name], t.[name], t.[create_date], t.[modify_date], p.[rows]
 ORDER BY [size_mb] DESC;

The other object types, which come with the tables:

USE [msdb];
GO

SELECT [type_desc], [name], [create_date], [modify_date]
  FROM sys.objects WITH (NOLOCK)
 WHERE [is_ms_shipped] <> 1
   AND [type] IN ('U', 'P', 'FN', 'IF', 'TF', 'V', 'TR')
 ORDER BY [type_desc], [name];

And what uses it, which in msdb is usually a job:

SELECT j.[name] AS [job_name], s.[step_id], s.[step_name], s.[command]
  FROM msdb.dbo.sysjobs AS j WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysjobsteps AS s WITH (NOLOCK)
         ON s.[job_id] = j.[job_id]
 WHERE s.[command] LIKE '%YourTableName%'
 ORDER BY j.[name], s.[step_id];

How to fix it

1. Find what references it. Start with the job query above, because in msdb the answer is usually a job step, then widen to application code and the Schema Search report.
2. Create a utility database if there is not one already, and back it up with everything else.
3. Move the table, then recreate its indexes, keys and permissions, which SELECT INTO does not carry:

SELECT * INTO [DBAUtility].[dbo].[YourTable] FROM [msdb].[dbo].[YourTable];

4. Update the job steps. A job step that referenced the table unqualified while running in msdb context needs the new three part name.
5. Rename rather than drop, and leave it renamed for a release:

USE [msdb];
GO
EXEC sp_rename 'dbo.YourTable', 'ZZZ_YourTable_ToDrop';

Check msdb‘s size while you are in there. If a user table has been accumulating rows, it is probably not the only thing in msdb that has, and the backup history and Database Mail archive are usually larger. Both have their own checks.

How long it takes

About two hours, mostly tracing what uses it and updating the job steps.

Related reports

Related checks

Frequently asked questions

Our monitoring scripts need to be next to the job tables. They need to read them, which a three part name does from any database. The logging table does not have to live there.

Is this worse than a table in master? Different rather than worse. master has the rebuild risk; msdb has the growth and the cleanup interaction. Both share the migration problem, which is the main one.

Can I exclude a table we know about? The check reports anything not shipped by Microsoft. If a table is deliberate and documented, note it and move on; the finding is a prompt rather than an instruction.

Does moving it need downtime? For the copy, no. Updating the job steps means the jobs should not be running at the time, which is usually a few minutes rather than a window.