Error 1803 and model size change in SQL Server 2012

Recently I encountered error 1803 when working on SQL Server 2012. The script I ran against a SQL Server 2012 instance was

CREATE DATABASE [suspect_db] ON PRIMARY

( NAME = N'suspect_db', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL11_CTP3\MSSQL\DATA\suspect_db.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'suspect_db_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL11_CTP3\MSSQL\DATA\suspect_db_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

I was confident I used this same script successfully on previous versions of SQL Server. Therefore, I examined the error message and the script properly:

Msg 1803, Level 16, State 1, Line 3

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

Then I went and compared the physical size of model and noticed that the size changed between SQL Server 2012 and previous versions. Here is a comparison of the sizes:

SQL Server version	Physical file size (bytes)		sp_spaceused information
SQL Server version	model.mdf	modellog.ldf	reserved	data	index_size	unused
2000	655,360	524,288	528 KB	144 KB	280 KB	104 KB
2005	1,245,184	524,288	1136 KB	472 KB	560 KB	104 KB
2008	1,310,720	524,288	1200 KB	472 KB	624 KB	104 KB
2008 R2	1,310,720	524,288	1216 KB	512 KB	632 KB	72 KB
2012	2,162,688	524,288	2096 KB	792 KB	1080 KB	224 KB

So, the next obvious question is - Why this change now?

When the SQL Server product team ships new features or enhancements to existing features, these involve changes to the core metadata. These might be in the form of new system tables, views, stored procedures and other objects. In some occasions, these changes may be server wide that you just make the change to the associated catalog tables in the msdb or master database. If this is a change that needs to be implemented in every database, then you will see the effect in the model database. Since we added several new exciting features in SQL Server 2012, we needed to add supporting system tables in every database. Therefore, those need to be persisted in model database first. Depending upon on the number of system tables you add and the available free space, the data file needs to grow. That is what happened in SQL 2012. New stored procedures and system wide views are normally implemented in the Resource database and installed as part of the setup.

If you have scripts where you specify the initial size of the database [especially SQL Express], make sure to consider this factor when migrating applications to SQL Server 2012.

Here is the query I used to find out what system tables were added in each version of SQL Server:

select object_name(p.object_id) as object_name , p.index_id , p.rows , au.total_pages

from sys.allocation_units au left outer join sys.partitions p on (au.container_id = p.hobt_id)

where au.type in ( 1 , 3 )

union

select object_name(p.object_id) as object_name , p.index_id , p.rows , au.total_pages

from sys.allocation_units au left outer join sys.partitions p on (au.container_id = p.partition_id)

where au.type in ( 2 )

The output I got from different versions is shown below with the changes highlighted. As you can see these system tables correspond to the new features introduced in SQL Server 2012 [e.g. AlwaysOn, Contained database, FileTable].

SQL 2012					SQL 2008 R2
object_name	index_id	rows	total_pages	size_bytes	object_name	index_id	rows	total_pages	size_bytes
filestream_tombstone_2073058421	1	0	0	0	filestream_tombstone_2073058421	1	0	0	0
filestream_tombstone_2073058421	2	0	0	0	filestream_tombstone_2073058421	2	0	0	0
filetable_updates_2105058535	1	0	0	0
queue_messages_1977058079	1	0	0	0	queue_messages_1977058079	1	0	0	0
queue_messages_1977058079	2	0	0	0	queue_messages_1977058079	2	0	0	0
queue_messages_2009058193	1	0	0	0	queue_messages_2009058193	1	0	0	0
queue_messages_2009058193	2	0	0	0	queue_messages_2009058193	2	0	0	0
queue_messages_2041058307	1	0	0	0	queue_messages_2041058307	1	0	0	0
queue_messages_2041058307	2	0	0	0	queue_messages_2041058307	2	0	0	0
sysallocunits	1	138	4	32,768	sysallocunits	1	103	4	32,768
sysallocunits	2	138	2	16,384	sysallocunits	2	103	2	16,384
sysasymkeys	1	0	0	0	sysasymkeys	1	0	0	0
sysasymkeys	2	0	0	0	sysasymkeys	2	0	0	0
sysasymkeys	3	0	0	0	sysasymkeys	3	0	0	0
sysaudacts	1	0	0	0	sysaudacts	1	0	0	0
sysbinobjs	1	23	2	16,384	sysbinobjs	1	23	2	16,384
sysbinobjs	2	23	2	16,384	sysbinobjs	2	23	2	16,384
sysbinsubobjs	1	3	2	16,384	sysbinsubobjs	1	3	2	16,384
sysbinsubobjs	2	3	2	16,384	sysbinsubobjs	2	3	2	16,384
sysbrickfiles	1	0	0	0
syscerts	1	0	0	0	syscerts	1	0	0	0
syscerts	2	0	0	0	syscerts	2	0	0	0
syscerts	3	0	0	0	syscerts	3	0	0	0
syscerts	4	0	0	0	syscerts	4	0	0	0
syschildinsts	1	0	0	0
sysclones	1	0	0	0
sysclsobjs	1	16	2	16,384	sysclsobjs	1	16	2	16,384
sysclsobjs	2	16	2	16,384	sysclsobjs	2	16	2	16,384
syscolpars	1	694	17	139,264	syscolpars	1	483	16	131,072
syscolpars	2	694	7	57,344	syscolpars	2	483	5	40,960
syscommittab	1	0	0	0	syscommittab	1	0	0	0
syscommittab	2	0	0	0	syscommittab	2	0	0	0
syscompfragments	1	0	0	0	syscompfragments	1	0	0	0
sysconvgroup	1	0	0	0	sysconvgroup	1	0	0	0
syscscolsegments	1	0	0	0
syscsdictionaries	1	0	0	0
sysdbfiles	1	2	2	16,384
sysdbfrag	1	0	0	0
sysdbfrag	2	0	0	0
sysdbreg	1	0	0	0
sysdbreg	2	0	0	0
sysdbreg	3	0	0	0
sysdercv	1	0	0	0	sysdercv	1	0	0	0
sysdesend	1	0	0	0	sysdesend	1	0	0	0
sysendpts	1	0	0	0
sysendpts	2	0	0	0
sysfgfrag	1	0	0	0	sysfgfrag	1	2	2	16,384
sysfiles1	0	2	2	16,384	sysfiles1	0	2	2	16,384
sysfoqueues	1	0	0	0
sysfos	1	0	0	0
sysfos	2	0	0	0
sysftinds	1	0	0	0	sysftinds	1	0	0	0
sysftproperties	1	0	0	0
sysftproperties	2	0	0	0
sysftproperties	3	0	0	0
sysftsemanticsdb	1	0	0	0
sysftstops	1	0	0	0	sysftstops	1	0	0	0
sysguidrefs	1	0	0	0	sysguidrefs	1	0	0	0
sysguidrefs	2	0	0	0	sysguidrefs	2	0	0	0


sysidxstats	1	180	4	32,768	sysidxstats	1	123	4	32,768
sysidxstats	2	180	2	16,384	sysidxstats	2	123	2	16,384
sysiscols	1	367	4	32,768	sysiscols	1	275	4	32,768
sysiscols	2	367	2	16,384	sysiscols	2	275	2	16,384
syslnklgns	1	0	0	0
sysmultiobjrefs	1	107	2	16,384	sysmultiobjrefs	1	106	2	16,384
sysmultiobjrefs	2	107	2	16,384	sysmultiobjrefs	2	106	2	16,384
sysnsobjs	1	1	2	16,384	sysnsobjs	1	1	2	16,384
sysnsobjs	2	1	2	16,384	sysnsobjs	2	1	2	16,384
sysobjkeycrypts	1	0	0	0	sysobjkeycrypts	1	0	0	0
sysobjvalues	1	187	5	40,960	sysobjvalues	1	125	3	24,576
sysobjvalues	1	187	25	204,800	sysobjvalues	1	125	25	204,800
sysowners	1	14	2	16,384	sysowners	1	14	2	16,384
sysowners	2	14	2	16,384	sysowners	2	14	2	16,384
sysowners	3	14	2	16,384	sysowners	3	14	2	16,384
sysphfg	1	1	2	16,384	sysphfg	1	1	2	16,384
syspriorities	1	0	0	0	syspriorities	1	0	0	0
syspriorities	2	0	0	0	syspriorities	2	0	0	0
syspriorities	3	0	0	0	syspriorities	3	0	0	0
sysprivs	1	137	2	16,384	sysprivs	1	130	2	16,384
syspru	1	0	0	0
sysprufiles	1	2	2	16,384	sysprufiles	1	2	2	16,384
sysqnames	1	98	2	16,384	sysqnames	1	97	2	16,384
sysqnames	2	98	2	16,384	sysqnames	2	97	2	16,384
sysremsvcbinds	1	0	0	0	sysremsvcbinds	1	0	0	0
sysremsvcbinds	2	0	0	0	sysremsvcbinds	2	0	0	0
sysremsvcbinds	3	0	0	0	sysremsvcbinds	3	0	0	0
sysrmtlgns	1	0	0	0

sysrowsetrefs	1	0	0	0	sysrowsetrefs	1	0	0	0
sysrowsets	1	124	2	16,384	sysrowsets	1	91	2	16,384
sysrscols	1	870	17	139,264	sysrscols	1	632	9	73,728
sysrts	1	1	2	16,384	sysrts	1	1	2	16,384
sysrts	2	1	2	16,384	sysrts	2	1	2	16,384
sysrts	3	1	2	16,384	sysrts	3	1	2	16,384
sysscalartypes	1	34	2	16,384	sysscalartypes	1	34	2	16,384
sysscalartypes	2	34	2	16,384	sysscalartypes	2	34	2	16,384
sysscalartypes	3	34	2	16,384	sysscalartypes	3	34	2	16,384
sysschobjs	1	2063	33	270,336	sysschobjs	1	53	2	16,384
sysschobjs	2	2063	33	270,336	sysschobjs	2	53	2	16,384
sysschobjs	3	2063	33	270,336	sysschobjs	3	53	2	16,384
sysschobjs	4	2063	6	49,152	sysschobjs	4	53	2	16,384
sysseobjvalues	1	0	0	0

syssingleobjrefs	1	155	2	16,384	syssingleobjrefs	1	146	2	16,384
syssingleobjrefs	2	155	2	16,384	syssingleobjrefs	2	146	2	16,384
syssoftobjrefs	1	0	0	0	syssoftobjrefs	1	0	0	0
syssoftobjrefs	2	0	0	0	syssoftobjrefs	2	0	0	0
syssqlguides	1	0	0	0	syssqlguides	1	0	0	0
syssqlguides	2	0	0	0	syssqlguides	2	0	0	0
syssqlguides	3	0	0	0	syssqlguides	3	0	0	0
systypedsubobjs	1	0	0	0	systypedsubobjs	1	0	0	0
systypedsubobjs	2	0	0	0	systypedsubobjs	2	0	0	0
sysusermsgs	1	0	0	0
syswebmethods	1	0	0	0
sysxlgns	1	0	0	0
sysxlgns	2	0	0	0
sysxlgns	3	0	0	0
sysxmitbody	1	0	0	0
sysxmitqueue	1	0	0	0	sysxmitqueue	1	0	0	0
sysxmlcomponent	1	100	2	16,384	sysxmlcomponent	1	99	2	16,384
sysxmlcomponent	2	100	2	16,384	sysxmlcomponent	2	99	2	16,384
sysxmlfacet	1	112	2	16,384	sysxmlfacet	1	112	2	16,384
sysxmlplacement	1	19	2	16,384	sysxmlplacement	1	18	2	16,384
sysxmlplacement	2	19	2	16,384	sysxmlplacement	2	18	2	16,384
sysxprops	1	0	0	0	sysxprops	1	0	0	0
sysxsrvs	1	0	0	0
sysxsrvs	2	0	0	0
			SUM	2,146,304				SUM	1,245,184

SQL 2008					SQL 2005
object_name	index_id	rows	total_pages	size_bytes	object_name	index_id	rows	total_pages	size_bytes
filestream_tombstone_2073058421	1	0	0	0					0
filestream_tombstone_2073058421	2	0	0	0					0
				0					0
queue_messages_1977058079	1	0	0	0	queue_messages_1977058079	1	0	0	0
queue_messages_1977058079	2	0	0	0	queue_messages_1977058079	2	0	0	0
queue_messages_2009058193	1	0	0	0	queue_messages_2009058193	1	0	0	0
queue_messages_2009058193	2	0	0	0	queue_messages_2009058193	2	0	0	0
queue_messages_2041058307	1	0	0	0	queue_messages_2041058307	1	0	0	0
queue_messages_2041058307	2	0	0	0	queue_messages_2041058307	2	0	0	0
sysallocunits	1	103	4	32,768	sysallocunits	1	89	2	16,384
sysallocunits	2	103	2	16,384
sysasymkeys	1	0	0	0	sysasymkeys	1	0	0	0
sysasymkeys	2	0	0	0	sysasymkeys	2	0	0	0
sysasymkeys	3	0	0	0	sysasymkeys	3	0	0	0
sysaudacts	1	0	0	0					0
sysbinobjs	1	23	2	16,384	sysbinobjs	1	23	2	16,384
sysbinobjs	2	23	2	16,384	sysbinobjs	2	23	2	16,384
sysbinsubobjs	1	3	2	16,384	sysbinsubobjs	1	0	0	0
sysbinsubobjs	2	3	2	16,384	sysbinsubobjs	2	0	0	0
				0					0
syscerts	1	0	0	0	syscerts	1	0	0	0
syscerts	2	0	0	0	syscerts	2	0	0	0
syscerts	3	0	0	0	syscerts	3	0	0	0
syscerts	4	0	0	0	syscerts	4	0	0	0
				0					0
				0					0
sysclsobjs	1	16	2	16,384	sysclsobjs	1	14	2	16,384
sysclsobjs	2	16	2	16,384	sysclsobjs	2	14	2	16,384
syscolpars	1	483	16	131,072	syscolpars	1	419	16	131,072
syscolpars	2	483	5	40,960	syscolpars	2	419	4	32,768
syscommittab	1	0	0	0					0
syscommittab	2	0	0	0					0
syscompfragments	1	0	0	0					0
sysconvgroup	1	0	0	0	sysconvgroup	1	0	0	0
				0					0
				0					0
				0	sysdbfiles	1	2	2	16,384
				0					0
				0					0
				0					0
				0					0
				0					0
sysdercv	1	0	0	0	sysdercv	1	0	0	0
sysdesend	1	0	0	0	sysdesend	1	0	0	0
				0					0
				0					0
sysfgfrag	1	2	2	16,384					0
sysfiles1	0	2	2	16,384	sysfiles1	0	2	2	16,384
				0					0
				0					0
				0					0
sysftinds	1	0	0	0	sysftinds	1	0	0	0
				0					0
				0					0
				0					0
				0					0
sysftstops	1	0	0	0					0
sysguidrefs	1	0	0	0	sysguidrefs	1	0	0	0
sysguidrefs	2	0	0	0	sysguidrefs	2	0	0	0
					syshobtcolumns	1	538	7	57,344
					syshobts	1	78	2	16,384
sysidxstats	1	117	2	16,384	sysidxstats	1	102	2	16,384
sysidxstats	2	117	2	16,384	sysidxstats	2	102	2	16,384
sysiscols	1	269	4	32,768	sysiscols	1	216	2	16,384
sysiscols	2	269	2	16,384					0
				0					0
sysmultiobjrefs	1	106	2	16,384	sysmultiobjrefs	1	102	2	16,384
sysmultiobjrefs	2	106	2	16,384	sysmultiobjrefs	2	102	2	16,384
sysnsobjs	1	1	2	16,384	sysnsobjs	1	1	2	16,384
sysnsobjs	2	1	2	16,384	sysnsobjs	2	1	2	16,384
sysobjkeycrypts	1	0	0	0	sysobjkeycrypts	1	0	0	0
sysobjvalues	1	119	3	24,576	sysobjvalues	1	102	3	24,576
sysobjvalues	1	119	25	204,800	sysobjvalues	1	102	25	204,800
sysowners	1	14	2	16,384	sysowners	1	14	2	16,384
sysowners	2	14	2	16,384	sysowners	2	14	2	16,384
sysowners	3	14	2	16,384	sysowners	3	14	2	16,384
sysphfg	1	1	2	16,384					0
syspriorities	1	0	0	0					0
syspriorities	2	0	0	0					0
syspriorities	3	0	0	0					0
sysprivs	1	130	2	16,384	sysprivs	1	120	2	16,384
				0					0
sysprufiles	1	2	2	16,384					0
sysqnames	1	97	2	16,384	sysqnames	1	91	2	16,384
sysqnames	2	97	2	16,384	sysqnames	2	91	2	16,384
sysremsvcbinds	1	0	0	0	sysremsvcbinds	1	0	0	0
sysremsvcbinds	2	0	0	0	sysremsvcbinds	2	0	0	0
sysremsvcbinds	3	0	0	0	sysremsvcbinds	3	0	0	0
				0					0
					sysrowsetcolumns	1	538	7	57,344
sysrowsetrefs	1	0	0	0	sysrowsetrefs	1	0	0	0
sysrowsets	1	91	2	16,384	sysrowsets	1	78	2	16,384
sysrscols	1	632	9	73,728					0
sysrts	1	1	2	16,384	sysrts	1	1	2	16,384
sysrts	2	1	2	16,384	sysrts	2	1	2	16,384
sysrts	3	1	2	16,384	sysrts	3	1	2	16,384
sysscalartypes	1	34	2	16,384	sysscalartypes	1	27	2	16,384
sysscalartypes	2	34	2	16,384	sysscalartypes	2	27	2	16,384
sysscalartypes	3	34	2	16,384	sysscalartypes	3	27	2	16,384
sysschobjs	1	53	2	16,384	sysschobjs	1	47	2	16,384
sysschobjs	2	53	2	16,384	sysschobjs	2	47	2	16,384
sysschobjs	3	53	2	16,384	sysschobjs	3	47	2	16,384
sysschobjs	4	53	2	16,384	sysschobjs	4	47	2	16,384
				0					0
					sysserefs	1	89	2	16,384
syssingleobjrefs	1	146	2	16,384	syssingleobjrefs	1	133	2	16,384
syssingleobjrefs	2	146	2	16,384	syssingleobjrefs	2	133	2	16,384
syssoftobjrefs	1	0	0	0					0
syssoftobjrefs	2	0	0	0					0
syssqlguides	1	0	0	0	syssqlguides	1	0	0	0
syssqlguides	2	0	0	0	syssqlguides	2	0	0	0
syssqlguides	3	0	0	0	syssqlguides	3	0	0	0
systypedsubobjs	1	0	0	0	systypedsubobjs	1	0	0	0
systypedsubobjs	2	0	0	0	systypedsubobjs	2	0	0	0
				0					0
				0					0
				0					0
				0					0
				0					0
				0					0
sysxmitqueue	1	0	0	0	sysxmitqueue	1	0	0	0
sysxmlcomponent	1	99	2	16,384	sysxmlcomponent	1	93	2	16,384
sysxmlcomponent	2	99	2	16,384	sysxmlcomponent	2	93	2	16,384
sysxmlfacet	1	112	2	16,384	sysxmlfacet	1	97	2	16,384
sysxmlplacement	1	18	2	16,384	sysxmlplacement	1	17	2	16,384
sysxmlplacement	2	18	2	16,384	sysxmlplacement	2	17	2	16,384
sysxprops	1	0	0	0	sysxprops	1	0	0	0
				0					0
				0					0
			SUM	1,228,800				SUM	1,163,264

NOTE: Do not query information directly from system tables or manipulate them. Use the documented and supported interfaces to query and modify the state of various SQL Server entities.

All the information provided above is based on SQL Server 2012 CTP3. It is possible there are changes when the product is released for production.

Thanks

Suresh Kandoth

SQL Server Escalation Services

Microsoft

CSS SQL Server Engineers

Analysis Services Thread Pool Changes in SQL Server 2012

Management Studio tricks you may or may not know about

The case of the incorrect page numbers

The official release of System Center Advisor…

How It Works: sys.dm_tran_session_transactions

Error 1803 and model size change in SQL Server 2012