15

I have a large table (~50 million rows) I'm trying to bulk insert into SQL Server and I get the error:

Could not allocate space for object 'myDB' in database 'I 3 Stroke' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

There is another table in the database with around 25 million rows. This database will only be used on a single machine, and it will be designed to mine data that already exists and it under no circumstances will ever grow beyond its current size.

For a situation such as this, what's the best way to tackle this so SQL Server doesn't complain? Will the solution matter that this DB won't be exposed to multiple users?

|improve this question
  • 2
    You need to add more space to the database or remove unused objects. What is your question? – Gordon Linoff Jan 28 '13 at 19:11
  • @GordonLinoff when the database was created the filegroup was designed to have unrestricted growth, and yet I'm running into this problem. I'm hoping to find a way to prevent this from happening again. – wootscootinboogie Jan 28 '13 at 19:14
  • 1
    Do you have more physical disk space available? If so, check the SQL Server error logs and see if there is additional information on why the autogrowth failed. – Kevin Jan 28 '13 at 19:15
  • Would you please confirm that enough disk space is available? This is the only plausible cause, except for restricted file growth (which you said is not the case). – usr Jan 28 '13 at 19:36
  • 1
    Maybe you are hitting the db size limit of the sql express edition. – Bruno Martinez Aug 15 '15 at 2:26
17

Follow these steps:

  1. Identify how much space you want to add to the database storage allocation:
    1. Open windows explorer
    2. Right click on the disk drive that your database files exist on
    3. Select properties
    4. Check how much disk space is available and decide how much of this you want to allocate for the database
      (Suggestion: Leave at least 20% disk space free if you house the database files on the same disk as your OS {Sub-Suggestion: Don't do this! Rebuild/migrate your data to it's own disk; you're screwing yourself on I/O.} and leave at least 8% for a pure data disk; these numbers are estimates of what I think the actual percentage suggestions are.)
  2. Update the storage allocation for the database.
    1. Open SSMS
    2. Click the "View" tab
    3. Select "Object Explorer"
    4. Expand the "Databases" folder
    5. Right click the database your trying to bulk insert into
    6. Select "Properties"
    7. Click the "Files" list option from the "Select a page" area at the left of the properties window
    8. Find the "Database files" row with the "Filegroup" as "PRIMARY"
    9. Add whatever number of megabytes you want to add to the database allocation to the "Initial Size (MB)" number
    10. Hit "OK"
      (You might also want to consider your "Autogrowth" values while you're here.)

You want to give your database as much storage allocation as you can afford to give it. If it runs out of space you'll receive this error without auto-grow on and if auto-grow is on you'll take a performance hit each time it has to auto-grow. If you are simply out of disk space then that is your answer and you need a bigger disk.

|improve this answer
  • 1
    Don't we all want bigger DIsKs? :P – Santa Aug 23 '16 at 7:04
12

Express Edition 2014 has a limit of 10 GB per database. If the auto grow of a database hits that point SQL Server throws the (obscure?) error message, not mentioning license limit. There may be plenty of disk space but you still see this message.

Remedy if design allows for: use multiple databases under this license.

|improve this answer
1

Databases often run out of space when they are in full recovery mode. If you are not doing transactions and manual backups are sufficient, then you can change the recovery mode to simple.

You might also need to recover space from the log file . . . it can be emptied but still use space.

However, you are better off asking this question to DBAs who may offer more solutions.

|improve this answer
  • This is not an issue of failed log growth. Data growth failed. – usr Jan 28 '13 at 19:35
  • @usr . . . They probably occupy the same underlying disk space. The issue is a lack of disk space. – Gordon Linoff Jan 28 '13 at 19:38
  • He says disk space is available. I explicitly asked because I was suspicious, too. – usr Jan 28 '13 at 21:17
  • How much disk space is available/free ? What is the current size of all the files in the primary file group and what is their growth setting? It is possible the growth increment is larger than the available free space. – user9164 Jan 29 '13 at 1:48
1

In my experience, this message occurs when the primary file (.mdf) has no space to save the metadata of the database. This file include the system tables and they only save their data into it.

Make some space in the file and the commands works again. That's all, Enjoy

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.