SAP HANA – Statistics server has been disabled

After logging to SAP HANA Studio You find information in Alerts and Mesagges: “Statistics server has been disabled. Data might be out of date“.

In statisticsserver trace You can find entries similiar to below:

112891]{-1}[-1/-1] 2020-08-03 12:00:52.952842 i STATS_WORKER ConfigurableRegistry.cpp(00382) : call SYS_STATISTICS.Shared_Build_SR_Views ('', '') [3915]{325461}[172/702216102] 2020-08-03 12:00:55.604357 i TraceContext TraceContext.cpp(00960) : UserName=, StatementHash=3c8c0b6da197d8b8396f29d45db245fc [3915]{325461}[172/702216102] 2020-08-03 12:00:55.309465 w SQLScript qx.cc(02710) : Failed to recompile procedure _SYS_STATISTICS.STATISTICS_SCHEDULABLEWRAPPER:invalidated procedure: ALERT_INTERNAL_DISK_FULL_EVENTS: line 3 col 5067 (at pos 6109) (at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/HANA__FA_CO_LIN64GCC48HAPPY_rel_fa~hana1sp12/s/ptime/query/checker/check_call.cc:258) Failed to recompile procedure _SYS_STATISTICS.STATISTICS_SCHEDULABLEWRAPPER:invalidated procedure: ALERT_INTERNAL_DISK_FULL_EVENTS: line 3 col 5067 (at pos 6109) (at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/HANA
[139227]{325461}[172/702216102] 2020-08-03 12:00:56.452887 w SQLScript qx.cc(02710) : Failed to recompile procedure _SYS_STATISTICS.STATISTICS_SCHEDULABLEWRAPPER:invalidated procedure: ALERT_MON_SAVEPOINT_DURATION: line 3 col 10502 (at pos 11544) (at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/HANA__FA_CO_LIN64GCC48HAPPY_rel_fa~hana1sp12/s/ptime/query/checker/check_call.cc:258)
Failed to recompile procedure _SYS_STATISTICS.STATISTICS_SCHEDULABLEWRAPPER:invalidated procedure: ALERT_MON_SAVEPOINT_DURATION: line 3 col 10502 (at pos 11544) (at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/HANA__F
[43215]{325461}[172/702216102] 2020-08-03 12:00:57.676726 w SQLScript qx.cc(02710) : Failed to recompile procedure _SYS_STATISTICS.STATISTICS_SCHEDULABLEWRAPPER:invalidated procedure: ALERT_LOG_SEGMENT_COUNT: line 3 col 14570 (at pos 15612) (at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/HANA__FA_CO_LIN64GCC48HAPPY_rel_fa~hana1sp12/s/ptime/query/checker/check_call.cc:258)
Failed to recompile procedure _SYS_STATISTICS.STATISTICS_SCHEDULABLEWRAPPER:invalidated procedure: ALERT_LOG_SEGMENT_COUNT: line 3 col 14570 (at pos 15612) (at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/HANA__FA_CO_LIN64
[61824]{325461}[172/702216102] 2020-08-03 12:00:59.065490 w SQLScript qx.cc(02710) : Failed to recompile procedure _SYS_STATISTICS.STATISTICS_SCHEDULABLEWRAPPER:invalidated procedure: ALERT_REPLICATION_CONNECTION_CLOSED: line 3 col 15950 (at pos 16992) (at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/HANA__FA_CO_LIN64GCC48HAPPY_rel_fa~hana1sp12/s/ptime/query/checker/check_call.cc:258)
Failed to recompile procedure _SYS_STATISTICS.STATISTICS_SCHEDULABLEWRAPPER:invalidated procedure: ALERT_REPLICATION_CONNECTION_CLOSED: line 3 col 15950 (at pos 16992) (at /sapmnt/ld7272/a/HDB/jenkins_prod/wor
[12126]{325461}[172/702216102] 2020-08-03 12:01:02.621132 e LJIT cePopCustomLjit.cpp(00609) : Llang Runtime Error: Exception::SQLException257: sql syntax error: incorrect syntax near "union": line 1 col 99 (at pos 99)
at main (line 204) ("_SYS_STATISTICS"."SHARED_CREATE_UNION_VIEW": line 35 col 3 (at pos 1784))
[12126]{325461}[172/702216102] 2020-08-03 12:01:02.621166 e LJIT cePopCustomLjit.cpp(00628) : "_SYS_STATISTICS"."SHARED_CREATE_UNION_VIEW": line 35 col 3 (at pos 1784): [257] (range 3) sql syntax error exception: sql syntax error: incorrect syntax near "union": line 1 col 99 (at pos 99)
[112891]{325461}[172/702216102] 2020-08-03 12:01:02.621350 e Executor PlanExecutor.cpp(00872) : plan plan297686980@server:3XX03 failed with rc 257; sql syntax error"_SYS_STATISTICS"."SHARED_CREATE_UNION_VIEW": line 35 col 3 (at pos 1784): [257] (range 3) sql syntax error exception: sql syntax error: incorrect syntax near "union": line 1 col 99 (at pos 99)
...

What You can do

1. Check the installation status:

SELECT * FROM _SYS_STATISTICS.STATISTICS_PROPERTIES where key = 'internal.installation.state';

If the result is “Done (error) since ”, the last migration finished unsuccessfully. Please check SAP Note 2006652

2. Please run this queries below one by one to recompile the procedures.

---> ALTER PROCEDURE _SYS_STATISTICS.ALERT_LOG_SEGMENT_COUNT RECOMPILE 
---> ALTER PROCEDURE _SYS_STATISTICS.ALERT_REPLICATION_CONNECTION_CLOSED RECOMPILE 
---> ALTER PROCEDURE _SYS_STATISTICS.STATISTICS_SCHEDULABLEWRAPPER RECOMPILE

Then please reinstall the statistic server:

---> alter system alter configuration ('nameserver.ini','SYSTEM') set ('statisticsserver','active')='true' with reconfigure

Now once again Check the installation status:

---> select value from _SYS_STATISTICS.STATISTICS_PROPERTIES where key = 'internal.installation.state'

3. Sometimes problem is more complicated and we have reinitialize ESS.

3.1. Initialize statistics server and execute from sql editor of HANA studio below procedure:

CREATE PROCEDURE deletestatistics () LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS
CURSOR c FOR select 'drop procedure '||schema_name||'.'||procedure_name
command from sys.procedures where schema_name='_SYS_STATISTICS' union
select 'drop synonym '||schema_name||'.'||synonym_name command from
sys.synonyms where schema_name='_SYS_STATISTICS' union
select 'drop view '||schema_name||'.'||view_name command from sys.views
where schema_name='_SYS_STATISTICS' union
select 'drop type '||schema_name||'.'||table_name command from sys.tables
where schema_name='_SYS_STATISTICS' and is_user_defined_type='TRUE' union
select 'drop table '||schema_name||'.'||table_name||' cascade' command
from sys.tables where schema_name='_SYS_STATISTICS' and
is_user_defined_type='FALSE' ;
BEGIN
for cmd as c do
exec cmd.command;
end for;
end;

call deletestatistics();

drop procedure deletestatistics;

3.2. Enable statistics server

alter system alter configuration ('nameserver.ini','SYSTEM') set ('statisticsserver','active')='true' with reconfigure;

3.3. Check whether statistics server enabled successfully

select value from _SYS_STATISTICS.STATISTICS_PROPERTIES where key = 'internal.installation.state';

If ESS enabled successfully, you will be able to see result like below:

Done (okay) since YYYY-MM-DD 07:58:26.1720000 local time:YYYY-MM-DD 16:58:26.1720000

Useful notes:

2343366 – How to solve alert ‘statistics server has been disabled’?
2644242 – HANA Statistics server has been disabled after upgrade.