Solved

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

Posted on 2013-12-06
1
2,254 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now