Sql-server – Database in SIMPLE recovery model full using SSIS bulk insert task

bulk-insertrecovery-modelsql serversql server 2014ssis

I have a database in SIMPLE recovery model with 10GB free.

The file I am trying to load through SSISs bulk load is 1GB.

The table is empty (truncated prior to each loading), is a heap and has no non clustered indexes. It is a table of varchar(255) fields.

When running the bulk insert task, I can watch the database size grow from 2GB to 12GB (max space available) where it runs out of space and returns the following message:

[Bulk Insert Task] Error: An error occurred with the following error message: "Could not allocate space for object 'dbo.tablename' in database 'DBNAME' 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.".

Firstly, I thought because I was in SIMPLE recovery model, bulk loading isn't logged. I know that it is when there is data in the table AND a clustered index, but neither is the case here. I assume the Bulk Insert Task is included in this as it should use BCP. Am I wrong?

Secondly, it seems strange that a 1GB text file would result in 10GB in SQL Server varchar fields. This seems mighty strange. The data is not being transformed, it is being Bulk loaded into a table. Why is it growing so large?

Best Answer

I can watch the database size grow from 2GB to 12GB (max space available) where it runs out of space and returns the following message:

The issue is either you're simply running out of disk space and the physical file cannot grow to complete the transaction, or you have a limit on the SQL file growth settings and the physical file cannot grow more to complete the transaction (whatever max space available means extactly e.g. per settings or disk capacity).

I thought because I was in SIMPLE recovery model, bulk loading isn't logged.

Per the below site and quoted text it does state minimal logging is more effecient than full logging, but it does NOT state that NO logging occurs at all in SIMPLE recovery mode.

Prerequisites for Minimal Logging in Bulk Import

under the simple recovery model or bulk-logged recovery model, minimal logging of bulk-import operations reduces the possibility that a bulk-import operation will fill the log space. Minimal logging is also more efficient than full logging.

So this means there IS still SQL transaction logging that occurs when the database is in SIMPLE recovery mode but it's minimal logging when compared to FULL recovery mode.

Why is it growing so large?

It would seem the explanation would be that your your committed transaction sizes with the BULK INSERT operations are HUGE. Simply make the commit\batch size parameters of your logic in SSIS or your execute TSQL in the package of a smaller size. Try testing with 100, 1000, 10000, 100000, and so on to see what gives you the best result and prevents the issue from occurring.

If the transactions are smaller, once the committed transactions are committed in SIMPLE recovery mode, then the log space of the committed transactions can be reused by subsequent BULK INSERT (or other) transactions.

Additional Resources

(These articles seem to have some references to potential parameters in SSIS package areas to set for smaller transactions, etc. The titles or the steps may be for a different ultimate goal, but there is potentially applicable content in these for what you may need to change in your SSIS logic to rectify your issue so these may be worth a simple read.)

Related Question