HANA Table Consistency Check – Exclude Big Tables/Checks – SPS05 and above

Introduction

Corruptions are not necessarily recognized quickly after they have been introduced. It might take weeks and months until the corrupted area is accessed the next time. The more time passes between the introduction of a corruption and the repair, the more difficult is the root cause analysis and correction.

Regular consistency checks are required to detect hidden corruptions as early as possible.The CHECK_TABLE_CONSISTENCY procedure can be used to check the consistency of the structure and data of tables. Its parameters are:

• ACTION: Type of action, e.g. ‘CHECK’

• SCHEMA_NAME: Name of analyzed schema (NULL for all schemas)

• TABLE_NAME: Name of analyzed object (NULL for all objects)

---

Problem

We all have heard about DBCC or Database Consistency Check in SAP HANA. Those who are new, can refer to SAP Note 2116157 for more information.

Below you can find some known options for running manual consistency check in SAP HANA for tables :

-- Consistency Check for all tables in all schemas
CALL CHECK_TABLE_CONSISTENCY('CHECK', NULL, NULL);​

-- Consistency Check for all tables in a specific schema
CALL CHECK_TABLE_CONSISTENCY('CHECK', <schema_name>, NULL);​

-- Consistency Check for specific table in a specific schema
CALL CHECK_TABLE_CONSISTENCY('CHECK', <schema_name>, <table_name>);​

Sometimes consistency checks may require significant amounts of CPU and memory resources, so it is better not to run it concurrent with peak workload. For production systems, an alternative is to run these checks on recent copy if done on any test/dev system.

But if you want to continue this check on production system only, excluding the bigger tables or excluding any specific check action we have now a solution.

---

Solution

Since SAP HANA 2.0 SPS05 and above versions (also for SAP HANA Cloud), Check actions can be excluded by using a minus character before the action name. This command for example, executes all checks which are included in the summary action ‘CHECK’ except for CHECK_PRIMARY_KEY:

CALL CHECK_TABLE_CONSISTENCY('CHECK, -CHECK_PRIMARY_KEY', NULL, NULL);

Note : The following three actions are only restrictions & cannot be excluded:

‘CHECK’, ‘CHECK_COLUMN_TABLES’, ‘CHECK_ROW_TABLES’.

It is also possible now to use the minus character to exclude specific tables from the consistency check. This command for example, executes all checks on all tables except for tables ‘CDPOS’ and ‘DDNTF’:

CALL CHECK_TABLE_CONSISTENCY('CHECK', 'SAPH2Q', '-CDPOS,-DDNTF');

Hence, if you have a SAP HANA instance in SPS05 or above revision, and you want to exclude some checks or some tables which are not that critical to execute on a production box, they can be excluded with a minus character as mentioned in above examples. Try it out on your HANA systems and feel free to post your comments & feedback.