Data migration of large Sybase tables may cause TEMPDB errors

Problem:

During data migration of a large Sybase table using SSMA 5.x for Sybase ASE, the following error may occur:

"Can't allocate space for object 'temp worktable' in database 'tempdb' because 'system' segment is full/has no free extents.

If you ran out of space in syslogs, dump the transaction log.

Otherwise, use ALTER DATABASE to increase the size of the segment.”

Data migration for the table in question will fail. The same data migrates successfully under SSMA 4.x.

Additional Information

If there is a clustered index on the target SQL Server table (default for a primary key), SSMA 5.x uses this clustered index expression to create an ORDER BY clause in the SELECT statement that SQLBulkLoad uses to migrate the data from the existing  Sybase table to SQL Server.

With very large tables, this ORDER BY query can overflow memory on the Sybase server and cause query processing to overflow into TEMPDB. The behavior is analogous to a SQL Server Sort Warning event. If the query overflows the allocated TEMPDB space, the following error is thrown and data migration fails: 

"Can't allocate space for object 'temp worktable' in database 'tempdb' because 'system' segment is full/has no free extents."

Workarounds:

Workarounds include:

1) Dropping the clustered index on the target SQL Server table prior to data migration, then re-adding it after data migration is complete. However, re-adding a clustered index is very costly because the table must be physically reordered.

2) On the Sybase server side, before performing the data migration, either:

a) set the TEMPDB either larger than the largest table to be migrated, or

b) set TEMPDB for unrestricted growth.

Published by : Brian (MSFT), SQL Escalation Services, Microsoft