22

Using the SQL Server Import and Export Wizard, I get this error:

Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80004005.
Could not allocate a new page for database 'database' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

I'm trying to upload a series of 2GB files and while loading the 4th file, I continue to get the above error message even after I go to Database properties > Files and change the Filegroup's autogrowth to 2500 MB and maxsize unlimited.

What is the best workaround? This data will ultimately take up around 60-80GB.

5
  • 1
    What version of SQL Server are you running? How much free space is available on the drive(s) that store the data files for "database"?
    – nabrond
    Jul 18, 2013 at 23:25
  • 2
    The error message tells you the "best workarounds": Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. I'm not sure what more we can tell you.
    – Ken White
    Jul 18, 2013 at 23:30
  • 1
    Brandon, SQL SERVER 2012. 760gb of free space on the drive that stores the data files for database. Ken, I have autogrowth enabled, autogrowth grows by 2500 MB and maxsize unlimited. SHould I still be running into this issue? Is it possible that there are multiple autogrowths that need enabling?
    – Jerry Carson
    Jul 18, 2013 at 23:37
  • 1
    Have you tried to manually grow the DB?
    – Goat CO
    Jul 19, 2013 at 0:59
  • 1
    Can it be that autogrow only works between elements and that ONE insert goes over the limit? Would be my bet - if a 20000gb insert fails on a 2500gb autogrow.... it can be there are 900mb free and the insert fails.
    – TomTom
    Jul 19, 2013 at 3:31

4 Answers 4

20

Sql express database will top out at 10g. I believe this is similar to the error message that you get.

I ran into this problem once at a client and it took me a while to figure out.

what version of sql are you running?

1
  • 5
    this solution saved me! I was on the Express version and upgraded to the Dev. Oct 16, 2018 at 22:40
12

Try growing it manually:

1.In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

2.Expand Databases, right-click the database to increase, and then click Properties.

3.In Database Properties, select the Files page.

4.To increase the size of an existing file, increase the value in the Initial Size (MB) column for the file. You must increase the size of the database by at least 1 megabyte.

5.To increase the size of the database by adding a new file, click Add and then enter the values for the new file. For more information, see Add Data or Log Files to a Database.

6.Click OK.

From: MSDN

1
  • 6
    Manual expanding is good because it may tell you more details. In my case I got: "CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database." Which is self explanatory.
    – user824276
    Feb 25, 2017 at 13:52
7

Are you sure it's

Could not allocate a new page for database 'database' because of insufficient disk space in filegroup 'PRIMARY'.

and not

Could not allocate a new page for database 'tempdb' because of insufficient disk space in filegroup 'PRIMARY'.

?

If it's actually [tempdb] in the error message then that's something that needs to be managed separately by ensuring that [tempdb] is on an appropriately-sized disk separated from other database.

If it's not [tempdb] in the error message, I'd imagine that an "upload" of this kind would hit [tempdb] pretty hard. Is [tempdb] on the same disk/LUN as 'database'? If so, you're probably exacerbating your problem with multiple attempted file growths (on multiple databases). So while you're doing this "upload" make sure you're monitoring [tempdb] as well.

If [tempdb] is on a separate drive and you're certain that you have enough space, I'd go ahead and size your data file to what you expect to be it's end-state size:

USE master;
GO
ALTER DATABASE [database]
MODIFY FILE
    (
    NAME = datafile,
    SIZE = 60GB,
    FILEGROWTH = 5GB
);
GO

Also, please make sure that the database's log file is separate from the data file. If it's not then you're back with competing growth events. If they are on separate disks, then I'd also go through and size them appropriately.

USE master;
GO
ALTER DATABASE [database]
MODIFY FILE 
(
    NAME = logfile, 
    SIZE = 8000MB
);
GO

ALTER DATABASE [database]
MODIFY FILE 
(
    NAME = logfile, 
    SIZE = 16000MB
);
GO

ALTER DATABASE [database]
MODIFY FILE 
(
    NAME = logfile, 
    SIZE = 24000MB
);
GO
...

until you get to your desired logfile size. Let's call it 80GB where you'll set your growth to a specific size. I'm using values recommended by Paul Randal and Kimberly Tripp

ALTER DATABASE [database]
MODIFY FILE 
(
    NAME = logfile, 
    SIZE = 80000MB,
    FILEGROWTH = 8000MB
);
1

You can also use the following scripts to free space from database:

DELETE FROM STActionLog
where id in (SELECT TOP 100000 id
             FROM STActionLog
             ORDER BY actionDate ASC)

delete from STActionLog
where actionDate < '2019-08-01'

These ones help me with the error :) Also make sure we have enough disk space on the computer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.