Solved

SQL CREATE DATABASE error:  the primary file must be at least 100 mb to accommodate

Posted on 2013-12-06
1
2,320 Views
Last Modified: 2013-12-06
Hello:

In SQL 2008 R2, we get the following error upon trying to create a database:  

The following SQL statement produced an error:
 create database [TGOOD]         ON          (NAME = 'GPSTGOODDat.mdf',             FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\GPSTGOODDat.mdf',             SIZE = 50,             FILEGROWTH = 20% )                 LOG ON    

ERROR [Microsoft][SQL Server Native Client 10.0][SQL Server]The CREATE DATABASE statement failed. The primary file must be at least 100 MB to accommodate a copy of the model database.

Is the remedy for this to shrink the model database?  If so, how?  

Are there any issues with shrinking the model database?  If so, what remedy can be applied?

Thanks!

TBSupport
0
Comment
Question by:TBSupport
1 Comment
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39701200
The model database is used as a template when creating new databases. Normally is less than 100MB but it is possible that someone created objects on it in order to be created on every new database created. If that is the case there is not much you can do.

In order to shrink a database there has to be unused space in it, which will be eliminated in the shrinking process. If there is no unused space the only way to make some is to drop objects, mainly tables as the data in them occupy the most space. If you don't know what tables to drop then you can't do much.

In order to check if there is unused space in model you will have to expand the Server node > Databases > System Databases > right click on model > Properties > under General section check in the right panel the Space available value. If there is something you can get rid of it by shrinking.

To shrink database you will right click on its name > Tasks > Shrink > Database > Click OK

There should be no implications. The available space is removed and it will be at OS disposal.

Of course the simplest solution is to actually modify the CREATE statement for your new database to be at least 100MB in size.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Re-appearing SQL Server Agent jobs 7 43
query optimization 6 23
Compare a column in results by values left of decimal 2 22
Database Mail Profiles 1 23
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question