Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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,558 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

609 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