'Could not allocate space for object 'dbo.SORT temporary run storage'

Hello all,
I’m trying to transfer/transform some data from one table to another, both on the same database. The source table holds 92M records.
To do so I'm using the next statement:
INSERT INTO [dbo].[Messages1]
([Time] ,[ID] ,[ResponseTo] ,[MessageMD5] ,[source] ,[dest]
,[arln_err] ,[ErrorDescription] ,[ErrorNumber] ,[ErrorSource]
,[ErrorType] ,[HttpCall] ,[HttpStatus] ,[QuoteAmount]
,[ReservationPickupLocation] ,[RatePickupLocationDescription] ,[RateReqCarType] ,[RateReqPickupLocation]
,[RejectMessage] ,[ReservationAmount] ,[ReservationCarType] ,[ReservationCarTypeDescription]
,[RatePickupLocation] ,[resp1] ,[ResultNum] ,[strRejectMessage]
,[strResultNum])
SELECT [Time] ,[ID] ,[ResponseTo] ,[MessageMD5] ,[source] ,[dest]
,[EE01] ,[EE02] ,[EE03] ,[EE04]
,[EE05] ,[EE06] ,[EE07] ,cast([EE08] as float)
,[EE09] ,[EE10] ,[EE11] ,[EE12]
,[EE13] ,cast ([EE14] as float) ,[EE15] ,[EE16]
,[EE17] ,[EE18] ,[EE19] ,[EE20]
,[EE21]
FROM [dbo].[Messages]
And I’m getting next exception:
Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.SORT temporary run storage: 185394470715392' 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.
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

I’m having enough disk space, and the PRIMARY and log files are set to auto grow. I also try to add aditionary log files – with no success.
How should I perform this task? How do I stop the database logging for this action or even for the whole database?
Regards
Ronen S.

i will be both tempdb and your data/log file of the user database is growing, and then you run out of disk somewhere. Try rerunnig and monitoring the disks the db files reside on.

Another thing you can do is break up the insert into smaller-sized transactions, i.e. insert ranges, maybe one or two million at a time. That will be more performant and should be faster.

|||

Hello Greg,

It looks to me the problem is while creating temporary table space for the primary key sort.

The statement fail after running for 4 minutes, so the disk space is not running out (I have 52G of free disk). anyhow - how can I monitor table/log size?

About your second suggestion how can I take a million records in a time? is there a something like Oracle psaudo column ROWNUM?

Thanks in advance.

Ronen S.

|||

The error messages talked about tempdb space usage. It indicated that the tempdb data and log file run out of space.

If you are sure that you have enough space for tempdb data and log file, then the problem probably will be that you do not have autogrow or your tempdb files reached to its size limit.

Thanks

Stephen

|||

I would BCP your data out to a file and then BCP the data back in to a table with no indexes.

Then create you indexes after.