SQL Server filegroup full during a large INSERT INTO statement

Question

Consider a SQL script designed to copy rows from one table to another in a SQL 2000 database. The transfer involves 750,000 rows in a simple:

INSERT INTO TableB([ColA],[ColB]....[ColG])
SELECT [ColA],[ColB]....[ColG]
FROM  TableA

This is a long running query, perhaps in part because ColB is of type ntext. There are a handful of CONVERT() operations in the SELECT statement.

The difficulty is that after ~15 mins of operation, this exception is raised by SQL Server.

Could not allocate space for object '[TABLE]'.'[PRIMARY_KEY]' in database '[DB]' 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.

More Info

• Autogrowth is already on.
• there is more than enough free space on disk (~20gb)
• the single .mdf is ~6gb
• no triggers on the source or target tables

Question

What options need to be set, either via Management Studio, or via T-SQL to allow the database to grow as required? What other remedies would you suggest?

Resolution

The db could not grow as needed because I was hosting this database on an instance of SQL Server 2008 Express. Upgrading to a non-neutered version of SQL Server will solve this problem.

Answer 1

If you can share a screenshot/information on your PRIMARY filegroup makeup and autogrow settings (i.e. all files included in the PRIMARY and the autogrow settings for each) that would be helpful as well. First thought without seeing anything additional would be that you potentially have a maxFileSize specified for one/more of the files that makeup your PRIMARY group, but that's just a hunch without actually seeing the information.

Answer 2

Best advice: Pre-size your database larger, instead of forcing it to grow on-demand (which can be a slow operation).

One reason this error could occur is if your autogrow interval is set too large. Besides the obvious (trying to grow by 25GB with only 20GB on disk), a large growth interval can take a very long time to allocate, which can cause your query to time out.

EDIT: Based on your new screenshot, doesn't look like the interval is the problem. But, my original advice still stands. Try to manually grow the database yourself, and see if it lets you:

ALTER DATABASE foobar
MODIFY FILE (name = foobar_data, size = 5000)

Answer 3

Is there any triggers on the table in question? I've seen similar results before when there was triggers and it was actually the expansion of the log file (ldf) which reached the limit just logging all the queries that were being ran by triggers and not the mdf itself. If there is any triggers I'd consider disabling them while you do this update and seeing if that helps (I assume this is a 1-off data migration rather than a recurring event?)