This section contains error messages for the Adaptive Server Sort Manager.
20
Sort failure
This error occurs during index creation when Adaptive Server fails to complete a sort operation. There are a number of different states for this error, each indicating a different reason for the failure:
State |
Meaning |
2 |
When creating a high level index structure, Adaptive Server is unable to build a parent node in the ancestor list. |
3 |
When creating a high level index structure, Adaptive Server is unable to build a parent node from the root of the tree. |
4 |
When creating a high level index structure, Adaptive Server is unable to build a parent node from the new root of the ancestor tree. |
6 |
The sever is adding the results of an internal sort of a special leaflet node to an external merge in progress, and a single internal merge step has a merge order greater than 8. |
7 |
The server is adding the results of an internal leaf merge node and the previous merge step caused the merge order to be greater than 8. |
8 |
Adaptive Server is starting a non-sort of already sorted data to create a clustered index, and it cannot find a page to an index that was supposed to have been built for the sorted data. |
9 |
The server is unable to allocate and initialize space for managing the sort's buffers. |
10 |
The server is building indexes and the length of a row in an overflow page is either zero or greater than the size of a page. |
When this error occurs, no index is created.
The configuration parameter number of sort buffers controls how Adaptive Server uses memory during sorting. number of sort buffers controls the sort buffer size; its default value is 500. Changing the value of number of sort buffers can cause Error 1501.
Error 1501 can also be caused by an Adaptive Server problem.
Take the following steps:
Determine the current value of number of sort buffers:
1> sp_configure "number of sort buffers" 2> go
Change the value, perhaps back to the default value of 500 (recommended except when creating indexes in parallel):
1> sp_configure "number of sort buffers", new_value 2> go
Refer to “create index” in the Reference Manual for information about creating indexes.
Refer to “Setting Configuration Parameters” in the System Administration Guide for information about configuration parameters.
All versions
14
Create unique index aborted on duplicate key. Primary key is '%S_KEY'.
This error occurs when you try to create a unique index on a column or columns containing at least two rows with the same index value. Each time a unique index is created, Adaptive Server checks for duplicate index values (if data already exists), and displays this message if it finds any.
When this error occurs, no index is created.
A command of the following type generates a 1505 error if there are duplicate values on col1, col2, and col3:
create unique index index_name on table_name(col1, col2, col3)
Decide whether you want to allow or prevent duplicate keys in the table where the index will be created. Use one of the following procedures:
If you decide to allow duplicate keys, do not use the keyword unique when you create the index.
If you decide to prevent duplicate keys, delete rows that have duplicate index values.
If you choose to delete duplicate keys, use the following procedure. For a brief period of time some rows from the table exist only in a temporary table. If a power failure or other interruption occurs, you could lose the temporary table and the rows in it. Therefore, before you begin this procedure, make a backup copy of the table or modify the procedure to use a table you create for this purpose instead of a temporary table.
Find the rows that caused the error:
1> select * from table_name 2> group by col1, col2, col3 having count(*) > 1 3> go
The query result contains all the rows causing the 1505 error.
Put one instance of each distinct row from step 1 into a temporary table:
1> select distinct * into #temp_table_name 2> from table_name 3> group by col1, col2, col3 having count(*) > 1 4> go
Some of the rows may contain identical values in the column or columns to be indexed and different values in other columns. Use the query below to find these rows:
1> select * from #temp_table_name 2> group by col1, col2, col3 having count(*) > 1 3> go
Examine the individual rows to decide which rows should be deleted. Delete these rows from the temporary table now. The rows you delete in this step will be deleted from the original table in step 4.
Delete all the rows which caused the 1505 error from the original table:
1> delete table_name from #temp_table_name 2> where table_name.col1 = #temp_table_name.col1 3> and table_name.col2 = #temp_table_name.col2 4> and table_name.col3 = #temp_table_name.col3 5> go
Each row in the temporary table should have unique values for the columns in question. Add these rows back into the original table:
1> insert into table_name 2> select * from #temp_table_name 3> go
Now you can create the unique index on the table.
Refer to “create index” in the Reference Manual for information about indexes.
All versions
14
Create index aborted on duplicate rows. Primary key is 'S_KEY'.
This error occurs when you try to create a clustered index on a table that contains duplicate rows.
Decide whether you want to allow or prevent duplicate rows in the table where the clustered index will be created. Use one of the following procedures.
Create the index with the allow_dup_row option:
1> create clustered index index_name on table_name 2> with allow_dup_row 3> go
Subsequent update and insert commands can create duplicate rows after the allow_dup_row option is set.
Create the index with the ignore_dup_row option:
1> create clustered index index_name on table_name 2> with ignore_dup_row 3> go
Existing duplicate rows will be deleted from the table as the index is created. Subsequent attempts to enter a duplicate row with insert or update are ignored and the insert or update is cancelled with an informational message. If the insert or update is part of a larger transaction, the rest of the transaction will continue as usual.
ignore_dup_row and allow_dup_row are
mutually exclusive. Using them together in the same create index statement
causes Error 1916 .
All versions
20
Row compare failure
This error occurs when Adaptive Server compares two rows during a sort and the compare fails because a row was being compared against itself.
Error 1509 can occur during normal processing or when you try to create an index on a table and the operation fails because of data corruption or an Adaptive Server problem.
This is a serious error as it indicates possible corruption.
Run dbcc checktable and dbcc tablealloc on the table being accessed at the time of the error to check for corruption.
If there is no corruption, call Sybase Technical Support.
If there is corruption, refer to the writeups in this manual for other error messages that appear. Run dbcc checktable and dbcc tablealloc again to determine whether the corruption has been cleared.
If corruption still exists after eliminating all error messages you can resolve, copy your data out and back in using the procedure in “How to Rescue Data from a Corrupted Table” or restore your database from clean backups using the procedure below.
WARNING! Some data might be lost on this page if you recover your table using bcp or select into (that is, the corrupted row and rows following it might be truncated and contain the wrong keys). Compare the two tables (old and new) row by row (by joining them on a primary key, for example) to determine which rows are different (corrupted).
If you choose to restore your database from clean backups, do the following:
Drop the database. If the drop fails, follow the instructions in “How to Drop a Database When drop database Fails” in the Encyclopedia of Tasks chapter.
Create a database for load. Make sure the database you create has sizes as least as large as those in sysusages for the original database (and that all other sysusages values match the original values). For more information about creating a database for load, refer to “create database” in the Reference Manual.
Load the database from backup. Refer to “load database” in the Reference Manual.
Run the online database command to make the database available for use.
If you need to call Sybase Technical Support, have the following documentation ready:
Server release and SWR rollup level
Server error log output
Text of all error messages
dbcc checktable and dbcc tablealloc output
sp_configure output
All versions
17
Sort failed: Out of space in database '%.*s'
Adaptive Server uses a clustered index to sort rows so that their physical order is the same as their logical (indexed) order. When creating a clustered index, Adaptive Server needs a considerable amount of space, 120 to 150 percent of the size of the table, because it needs to sort the data in the table first and then create the clustered index on the sorted data. This space should be available in the database or on the segment where you want to create your index.
Error 1510 occurs when you try to create an index and one of the following is true:
There is not enough space in the database to create the index. Usually this error occurs with an 1105 error.
No more locks are available.
Unfortunately, there is no straightforward way to identify which problem caused the error.
Select the appropriate action from the sections below.
Use either of the following two strategies to correct this error:
Use the instructions from Error 1505 to increase the space available in your database.
Move the table where the index is to be created to a different, larger, user-defined segment, and then create the index. Refer to “Creating and Using Segments” in the System Administration Guide for information about segments.
Determine whether you have enough space available on the new segment by typing:
1> sp_helpsegment segment_name 2> go
Verify that the database where you want to create your index has enough space allocated on the device or devices that contain the segment from the above query:
1> sp_helpdb database_name 2> go
If the 1510 error occurs because Adaptive Server has no more locks available, use sp_configure to increase the number of locks:
1> sp_configure "number of locks", new_value 2> go
Shut down and restart Adaptive Server to initialize the new configuration parameter value.
Refer to the Performance and Tuning Guide and “Setting Configuration Parameters” and “Configuring Memory” in the System Administration Guide for information about configuration parameters and about how Adaptive Server memory is allocated.
Refer to “create index” in the Reference Manual.
All versions
21
Page allocated to sort found to be busy. Page number %ld. %S_BUF.
During index creation, Adaptive Server allocates and uncaches pages to sort the table's rows. The sort routine double checks whether a page allocated for the sort is in the data cache. If it is in the cache, the page cannot be used, the sort operation fails and Error 1514 is raised.
Failure to sort means that the index creation fails; consequently 4313 errors often accompany Error 1514.
If Error 1514 is raised in an isolated case during index creation on a specific table, run dbcc checktable on the table. If errors are reported, try to fix them using the sections in this manual that describe those errors. Next use one of these options to recover from the 1514 problem:
Retry index creation.
Shutdown and restart Adaptive Server, and retry index creation.
Select the table data into a new table, drop the old table, and rename the new table to the old table name. Create the desired index on the table.
Bulk copy the affected table out, drop and re-create the table, and bulk copy back in. Create the desired index on the table.
If the problem persists, or is not isolated to one table, contact Sybase Technical Support for assistance.
All versions
16
Create index with sorted_data was aborted because of row out of order. Primary key of first out of order row is '%S_KEY'
The sorted_data option speeds creation of an index when the data in the table is already in sorted order. The speed increase becomes significant on tables larger than 1GB.
Error 1530 occurs when you try to create an index with the sorted_data option on a column or columns containing data that is not in sorted order.
When this error occurs, no index is created.
The sorted_data option speeds
index creation only for clustered
indexes or unique nonclustered indexes. Creating a nonunique, nonclustered
index with the sorted_data option may
succeed, but there is no improvement in performance.
To create an index after getting a 1530 error, do one of the following:
Sort the data (using your standard operating system procedures), bcp it into the table, and then create the index with the sorted_data option.
Create the index without the sorted_data option.
Refer to “create index” in the Reference Manual for information about indexes.
All versions
16
The sorted_data option cannot be used for a nonclustered index if the keys are not unique within the table. Create index was aborted because of duplicate keys. Primary key is '%S_KEY'.
This error occurs when you try to create a nonunique, nonclustered index with the sorted_data option on a column or columns containing rows with the same index value. If this situation exists, a statement of the following type will fail:
1> create index index_name 2> on table_name(col1, col2, col3) 3> with_sorted_data 4> go
When Error 1531 occurs, no index is created.
To avoid the 1531 error, do one of the following:
Do not use the sorted_data option, or
Delete rows with duplicate index values.
If you decide to delete rows with duplicate index values, use the following procedure.
For a brief period of time, some rows from the table
will exist only in a temporary table, and if a power failure or
other interruption occurs, you could lose the temporary table and
the rows in it. To protect yourself against data loss, you can make
a backup copy of the table before you begin the procedure. Alternatively,
you can modify the procedure to use a specific table instead of
a temporary table.
Find the rows that caused the 1531 error:
1> select * from table_name 2> group by col1, col2, col3 3> having count (*) > 1 4> go
Put one instance of each distinct row from step 1 into a temporary table:
1> select distinct * into #temp_table_name 2> from table_name 3> group by col1, col2, col3 having count(*) > 1 4> go
Some of the rows may contain identical values in the column or columns to be indexed and different values in other columns. Use the following query to find these rows:
1> select * from #temp_table_name 2> group by col1, col2, col3 having count(*) > 1 3> go
Examine the individual rows to decide which rows should be deleted. Delete these rows from the temporary table now. The rows you delete in this step will be deleted from the original table in step 5.
Delete all the rows which caused the 1531 error from the original table:
1> delete table_name from #temp_table_name 2> where table_name.col1 = #temp_table_name.col1 3> and table_name.col2 = #temp_table_name.col2 4> and table_name.col3 = #temp_table_name.col3 5> go
Now each row in the temporary table should have unique values for the columns in question. Add these rows back into the original table:
1> insert into table_name 2> select * from #temp_table_name 3> go
Create the index with the sorted_data option.
Refer to “create index” in the Reference Manual for information about indexes.
All versions
