locked
Sybase 'tempdb' - 'system' segment is full/has no free extents RRS feed

  • Question

  • I am using SSMA for sybase to migrate data from Sybase to SQLServer 2012.

    The source database(Sybase) has more than 100 tables. I am able to migrate all other tables except 2 big ones.

    It is showing error message

    [DataDirect ADO Sybase Provider] 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.

    The two tables raising this error has around 20 and 47 millions of rows each.

    Please help me in resolving this.

    Wednesday, August 14, 2013 10:15 AM

Answers

  • Thanks scott_morris for your reply.

    I managed to get the solution for this.

    Although the SSMA error says that it is from Sybase, internally it is driven by the indexes of those big tables migrated into SQLServer.

    Example:

    When you migrate a big table which got index(cluster index), every time you insert a row it will reorder the table data filling up tempdb.

    SSMA creates indexes and triggers on SQL Server by default. The following steps can be used to avoid this error,

    SSMA for Sybase (Sybase-->SQLServer)

    1. Connect to Sybase

    2. Connect to SQL Server

    3. Specify Sybase DB to SQL Server DB mappings

    4. "Convert" Sybase schema

    5. "Synchronize" to apply the converted schema to SQL Server - when this happens the indexes and triggeres will get created along with other objects.

    6. Migrate data - currently it is not possible to disable triggers and indexes creation from SSMA. Ouside of SSMA, you can drop the triggers and indexes from all the tables after step #5 above manually, migrate the data using SSMA and recreate the triggers and indexes manually(if needed)


    • Proposed as answer by Sofiya Li Thursday, August 15, 2013 1:22 AM
    • Marked as answer by Balaji666 Friday, August 16, 2013 11:37 AM
    Wednesday, August 14, 2013 8:28 PM

All replies

  • The error is coming from the sybase dbms, so posting here is not likely to be useful.  However, it seems to me that the error message is very clear about how you (or a sybase dba) can avoid the problem. 
    Wednesday, August 14, 2013 12:51 PM
  • Thanks scott_morris for your reply.

    I managed to get the solution for this.

    Although the SSMA error says that it is from Sybase, internally it is driven by the indexes of those big tables migrated into SQLServer.

    Example:

    When you migrate a big table which got index(cluster index), every time you insert a row it will reorder the table data filling up tempdb.

    SSMA creates indexes and triggers on SQL Server by default. The following steps can be used to avoid this error,

    SSMA for Sybase (Sybase-->SQLServer)

    1. Connect to Sybase

    2. Connect to SQL Server

    3. Specify Sybase DB to SQL Server DB mappings

    4. "Convert" Sybase schema

    5. "Synchronize" to apply the converted schema to SQL Server - when this happens the indexes and triggeres will get created along with other objects.

    6. Migrate data - currently it is not possible to disable triggers and indexes creation from SSMA. Ouside of SSMA, you can drop the triggers and indexes from all the tables after step #5 above manually, migrate the data using SSMA and recreate the triggers and indexes manually(if needed)


    • Proposed as answer by Sofiya Li Thursday, August 15, 2013 1:22 AM
    • Marked as answer by Balaji666 Friday, August 16, 2013 11:37 AM
    Wednesday, August 14, 2013 8:28 PM