Solved

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

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

770 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