Quick Scan Report – User Tables In master

What this check looks for

Tables in master where is_ms_shipped is not 1, so tables somebody created rather than tables SQL Server ships.

A short exclusion list keeps known, legitimate visitors out of the results: CommandLog, which Ola Hallengren’s scripts create by convention, the CPNS hardware and results history tables, Worksta, Queue, QueueDatabase, and the rds_ tables Amazon RDS adds to manage an instance it owns.

Why it matters

It works perfectly, right up until the day you move the server.

That is the entire risk, and it is why this sits at Medium rather than higher. A table in master behaves like any other table. Queries find it, because master is on everyone’s path. Applications use it. Years pass.

Then:

• It is not in your database backups. It is in the master backup, which is a different backup on a different schedule, often less frequent, and restored in a completely different procedure. Plenty of instances do not back up master at all.
• It does not move with your databases. Migration to a new server means restoring or attaching user databases. master is not one of them. The table is left behind and whatever depended on it breaks on the new server, during the cutover, when there is no time to work out why.
• Rebuilding master destroys it. master is rebuilt as part of several recovery procedures and some upgrades, and the rebuild is a fresh master.
• It confuses master‘s real job. master holds instance configuration. Anything else in there makes it harder to reason about what has to be preserved.

The usual origin is a utility script, a lookup table, or a temporary staging table that somebody created without switching database context first, because master is what a new query window connects to.

How to confirm it yourself

USE [master];
GO

SELECT s.[name]        AS [schema_name],
       t.[name]        AS [table_name],
       t.[create_date],
       t.[modify_date],
       p.[rows]
  FROM sys.tables AS t WITH (NOLOCK)
 INNER JOIN sys.schemas AS s WITH (NOLOCK)
         ON s.[schema_id] = t.[schema_id]
  LEFT JOIN sys.partitions AS p WITH (NOLOCK)
         ON p.[object_id] = t.[object_id] AND p.[index_id] IN (0, 1)
 WHERE t.[is_ms_shipped] <> 1
 ORDER BY t.[create_date];

Tables are not the only thing that ends up here. While you are looking, check for the rest:

USE [master];
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 find out whether anything still uses it:

SELECT j.[name] AS [job_name], 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%';

How to fix it

Move it, do not just drop it. Something created it for a reason, and that reason may still be running somewhere.

1. Find out what uses it. The job query above, plus a search of your application code and any linked server definitions. The Schema Search report searches module definitions across databases for the name.
2. Create a home for it. A dedicated administrative or utility database is the right answer. That database gets backed up with everything else, moves with everything else, and is somewhere obvious to look.
3. Move the table:

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

Recreate the indexes, keys and permissions, which SELECT INTO does not carry. 4. Update everything that referenced it, including three part names in jobs and procedures. 5. Rename the original before dropping it, and leave it renamed for a release. Something you did not find will announce itself, and renaming is reversible in a way dropping is not.

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

In the meantime, back up master. Whatever you decide, while the table is there it is only protected by the master backup, and that backup should exist anyway.

How long it takes

About two hours, most of which is finding what references the table.

Related reports

Related checks

Frequently asked questions

It has been there for years without a problem. The problem arrives at migration, at a master rebuild, or at a restore to different hardware. Those are the moments with the least time available to work out what is missing.

What about Ola Hallengren’s CommandLog? It is excluded, because putting it in master is the documented default for those scripts. Moving it to a utility database is still tidier, but it is a deliberate convention rather than an accident.

Can I just drop it? Only once you know nothing uses it. Rename it first and leave it for a release.

Does this affect performance? No. It is entirely about recoverability and portability.