Checking Database Structures

Use

During a database structure check, the system checks the structural consistency of the database in the cache and the data area. Note that the semantic data model is not checked, which is why you cannot find logical errors with a database structure check.

Recommendation

Check the database structures once a week.

If you receive one of the following error messages, perform an unscheduled database structure check:

-9053: Data page corrupted

-9023: Illegal entrypos

-9003: Illegal entrylength

-9006: Illegal keylength

-9041: Index not accessible (if you get this message, recreate the index first)

-9407: This error is displayed for a wide range of error situations; read the detailed error text to find out whether a database structure check is necessary.

Note that this list is only a selection of error situations that may make a database structure check necessary.

During the database structure check, the system runs through all B* trees and page chains and carries out the following checks, among others:

Whether all the pages of the B* trees and page chains can be accessed.
Whether the administration structures on the pages are intact (for example, the separators on the B* tree index level).
Whenever the system reads a data page from the data area, it calculates a checksum that has to match the checksum saved on the data page.
The system also checks the key lengths and whether they are sorted in ascending order.

Depending on your database size, database usage and general system environment, select the appropriate checking procedure.

Table 1: Procedures for Checking Database Structures
Procedure	Operational State	Description	Advantages	Disadvantages
Check the complete database structure and clean up the data area	ONLINE	The system checks the following: Tables Indexes LOBs History pages Using the results of this check, the system removes unused pages and LOBs that are not referenced from the data area. Internally, the system creates a snapshot of the data area and carries out the database structure check on this snapshot. This snapshot is automatically deleted by the system after the database structure check is finished.	The system is available without any restrictions (no pages are locked).	Additional space is used in the data area during the check (depending on the change activity in the database during the check). The check may run slowly because its I/O operations have a low priority and all data needs to be read from the data area. Even though the check is carried out on a snapshot, the system still needs to read data from the same disks that are used by normal database operation.
Check the complete database structure	ONLINE	The system checks the following: Tables Indexes To speed up the check, you can exclude indexes from the check. LOBs	In general, the check runs fast because its I/O operations have a high priority and a part of the data can be read from the cache.	The system is only available with restrictions (pages are temporarily locked). This may significantly impact normal database operation. We recommend that you carry out the check in times of low database usage.
Check a table	ONLINE	The system checks the specified table. Options: Check the table structure Check table structure with additional LOB checks If the table contains LOB columns, the system checks whether all the LOB values that are specified in the table actually exist. To ensure the consistency of the transaction, the system requests a share lock for the duration of the check. Check the catalog for the table (optional)	In general, the check runs fast because its I/O operations have a high priority and a part of the data can be read from the cache.	Depending on the options you choose, the system is only be available with restrictions (pages are temporarily locked).
Check an index	ONLINE	The system checks the consistency of the specified index. Note that the consistency between index and table is not checked.	-	The index is temporarily locked.
Check the complete database structure and clean up the data area in operational state ADMIN	ADMIN	The system checks the following: Tables Indexes LOBs History pages Using the results of this check, the system removes unused pages and LOBs that are not referenced from the data area.	You can use this check during a recovery (after restoring data backups).	The system is not available for users during the check.

Procedure

You can perform database structure checks as described in the table above. It is also possible to perform a structure check of the objects in a specified snapshot. Use one of the following database tools: Database Studio or CCMS (in SAP systems only). For more information, see

Database Studio, Checking Database Structures, Making Snapshots of the Data Area
Database Administration in CCMS,
- Scheduling a Database Structure Check
- Tables/Views/Synonyms (checking a single table)

Result

The results are written to the KnlMsgArchive log file (see Log Files).

CCMS user can check the scheduling and the results of the database structure check in the DBA Planning Calendar (see Database Administration in CCMS, Displaying Actions).

Return code 0 means that the database system found no errors during the database structure check. If the database structure check returns any other return code, proceed as described in Fixing Errors in the Database Structure.

More Information

Concepts of the Database System, Logical Access Structures

SAP Note, 940420 Information published on SAP site