SQLTeam.com | Weblogs | Forums

SSIS Package Fails

Hi,

I am getting error as ```
Could not allocate space for object 'dbo.SORT temporary run storage: 430271000936448' in database 'mydata' 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.

Mainly i am getting this error on update statement.

The error tells you the possible solutions. Increase or allow auto growth for the one of the files in the PRIMARY file group or make space for additional data by adding a file to PRIMARY on a drive with room or make room by deleting data from tables in the PRIMARY filegroup.

Getting Error as
[Execute SQL Task] Error: Executing the query "UPDATE [SLUCare].[dbo].[EDW_PAT_ENC_FACT]
SET
ENC..." failed with the following error: "Could not allocate space for object 'dbo.SORT temporary run storage: 141327926362112' in database 'tempdb' 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.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

My DBA has did that.But still getting the same error.

Check the space for tempdb. Sorting may be done in temdb.

I cant see any sort option.

Auto growth is allowed for files also.

The error tells you the database causing the error is tempdb. Ask your DBA to look at the file(s) in the PRIMARY filegroup and ensure there is enough space available for tempdb to grow.

Check the indexes on the table you're updating to check if sort in tempdb is on. When updating a table with an index set to sort in tempdb, it can cause file growth in tempdb.

Is this an SCD - or are you running a SQL statement in SSIS? If this is a SQL statement - which it appears to be...what are you updating and how many rows are being updated?

If you can outline the process - I think we can come up with a solution that avoids this issue.

I have fixed this issue.They were updating around 50 columns.I have removed unnecessary columns in update statement.Then the package starts working.