Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jul 19 '13 at 3:17

This question came from our site for professional and enthusiast programmers.

    
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"? –  Brandon Jul 18 '13 at 23:25
1  
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 '13 at 23:30
    
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 '13 at 23:37
    
Have you tried to manually grow the DB? –  Goat CO Jul 19 '13 at 0:59
    
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 '13 at 3:31
add comment

2 Answers 2

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
);
share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.