?
Solved

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

Posted on 2013-12-06
1
Medium Priority
?
2,461 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

743 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