Database Schema for Blank Database is large. 20MB

Is it normal for blank databases with just schema to be large? Or is there a way that I can reduce the size of the initial database?

If you create a database without specifying a file size, the size is mapped from model. You can see what this is by running:

EXEC model.sys.sp_helpfile;
-- on my system, size of primary data file -> 3264 KB

Now, if I create a database just using CREATE DATABASE, I get an exact replica of those files.

CREATE DATABASE [floob1];
GO
EXEC floob1.sys.sp_helpfile;
-- size of primary data file -> 3264 KB

I can try to shrink it after the fact, and it will shrink some, but not entirely.

DBCC SHRINKFILE(floob1, 1);
GO
EXEC floob1.sys.sp_helpfile;
-- size of primary data file -> 2368 KB

So you can do that, but perhaps you should first find out what's in your model database and why it's 20 MB. Perhaps you can fix this problem by removing objects and/or shrinking that file. If shrinking the file doesn't reduce the size, then check for objects:

SELECT s.name, t.name, rows = SUM(p.rows)
  FROM model.sys.partitions AS p
  INNER JOIN model.sys.objects AS t
    ON p.[object_id] = t.[object_id]
  INNER JOIN model.sys.schemas AS s
    ON t.[schema_id] = s.[schema_id]
  WHERE p.index_id IN (0,1)
  ORDER BY rows DESC;

SELECT s.name, o.name, codesize = DATALENGTH(m.definition)
  FROM sys.objects AS o
  INNER JOIN sys.schemas AS s
    ON o.[schema_id] = s.[schema_id]
  INNER JOIN sys.sql_modules AS m
    ON o.[object_id] = m.[object_id]
  ORDER BY codesize DESC;

It's possible that objects were created there by mistake (in which case drop them), or have more rows than they should (in which case prune them and rebuild).

Note that if you try to override model and make a smaller database, you can't:

CREATE DATABASE [floob2]
ON (NAME = floob2_data, 
  FILENAME = 'C:\...wherever...\floob2.mdf',
  SIZE = 1MB, FILEGROWTH = 2MB);

This results in an error:


Msg 1803, Level 16, State 1, Line 18

The CREATE DATABASE statement failed. The primary file must be at least 4 MB to accommodate a copy of the model database.

I assume you'll get a similar error if you try to create a database with an explicit data file < 20 MB, because it sounds like your model database is about 20 MB. So going back to my previous point, you're going to need to dig in and see why model is 20 MB, and fix that. (And note that I have no idea how you're measuring 20 MB - it could be the log file, for all I know, so you may need to look at that file instead.)

Of course, if space is a real issue and you put thousands of databases there under the assumption that they'll never grow, and then they all start growing, this is going to be a problem.

StackExchange Database Administrators Q#110695, answer score: 11