Could not allocate a new page for database because of insufficient disk space

Question

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.

Answer 1

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?

Answer 2

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

Answer 3

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
);

Answer 4

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.