Quick Scan Report – User Tables in Model

What this check looks for

Tables in model where is_ms_shipped is not 1. The check needs sysadmin and is skipped on Amazon RDS.

Why it matters

model is not a database. It is the template every new database is stamped from, which makes this the same mistake as a table in master plus one extra consequence that nothing else on this report has.

Everything created from now on gets a copy:

• Every new user database starts with that table in it. Not a reference to it, a copy, with its own empty contents.
• tempdb is recreated from model at every single restart. So the table appears in tempdb too, every time SQL Server starts, forever.

That second one is the one that surprises people. A table sitting in tempdb that nobody created and nobody can explain is very often this, and the trail back to model is not obvious because tempdb is rebuilt so routinely that nobody thinks of it as inheriting anything.

And model affects more than objects. Its settings are inherited too, which is why several other checks on this report end with “fix model“:

• Recovery model. model in full recovery is why new databases arrive in full recovery and then have no log backups.
• AUTO_SHRINK and AUTO_CLOSE.
• AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS.
• Page verification.
• File sizes and growth increments, which is why a model with a 1 MB growth increment produces a whole instance of badly configured databases.

So a table in model is worth fixing on its own account, and finding one is a good reason to look at everything else model is propagating.

The shared problems with master and msdb apply as well: the table is not in your database backups, it does not move when you restore user databases elsewhere, and a model rebuild removes it.

How to confirm it yourself

USE [model];
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];

Everything else non-Microsoft in model, which matters more here than in master because all of it propagates:

USE [model];
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','SN')
 ORDER BY [type_desc], [name];

Confirm the propagation, which is the convincing bit:

-- the same table will be sitting in tempdb
SELECT [name], [create_date] FROM tempdb.sys.tables WITH (NOLOCK) WHERE [is_ms_shipped] <> 1;

And check what else model is passing on:

SELECT [name], [recovery_model_desc], [page_verify_option_desc],
       [is_auto_close_on], [is_auto_shrink_on],
       [is_auto_create_stats_on], [is_auto_update_stats_on]
  FROM sys.databases WITH (NOLOCK)
 WHERE [name] = 'model';

SELECT [name], [type_desc],
       CAST([size] * 8.0 / 1024 AS DECIMAL(10,1)) AS [size_mb],
       CASE WHEN [is_percent_growth] = 1 THEN CAST([growth] AS VARCHAR(10)) + ' %'
            ELSE CAST([growth] * 8 / 1024 AS VARCHAR(10)) + ' MB' END AS [growth]
  FROM model.sys.database_files WITH (NOLOCK);

How to fix it

Move the table out, and then review everything else model is propagating.

1. Find what uses it. In model‘s case the answer is often “nothing”, because the table was created there by accident: somebody had model selected in the query window rather than their own database. The Schema Search report finds references across databases.
2. Move it to a proper utility database if it is genuinely wanted:

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

3. Rename rather than drop, and leave it for a release:

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

4. Clean up the copies. Every database created since it appeared has one, and so does tempdb. Dropping it from model does not remove it from databases already created:

-- find the copies
EXEC sp_MSforeachdb N'
    IF ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'')
    AND EXISTS (SELECT 1 FROM [?].sys.tables WHERE [name] = ''YourTable'' AND [is_ms_shipped] <> 1)
    SELECT ''?'' AS [database_name];';

5. Then review model properly, since you are in there. Recovery model, page verification, autoshrink, autoclose, the statistics settings and the file growth increments are all being handed to every new database, and several have their own checks on this report.

One useful thing model is genuinely for: setting sensible defaults deliberately. A model with simple recovery, CHECKSUM page verification, a 512 MB growth increment and a sensible initial size means every new database starts correct. That is a good use of it, and it is a different thing from leaving a table there by accident.

How long it takes

About two hours, most of it finding and cleaning up the copies in databases created since.

Related reports

Related checks

Frequently asked questions

Why is a table in model worse than in master? Because it propagates. Every new database gets a copy, and tempdb gets one at every restart.

We put a table there on purpose, as a template. That is a legitimate if unusual use, and it is worth documenting, because the next person will find it exactly as this check did. Note that every new database gets an empty copy, which is rarely what people mean by a template.

Will dropping it from model affect existing databases? No. They have their own copies, which stay until you remove them individually.

There is a table in tempdb nobody created. This is very often the explanation. Check model before looking anywhere else.