No Hub or QMC access after upgrade to Sense November 2017 or higher - recursive cleanup script

[Andre_Sostizzo]

Trying to access Qlik Sense Hub or Management Console (QMC) leads to the getting "refused to connect" on the web browser.

Investigating the issue with the command netstat | findstr :443 confirms there is not service listening on port 443 (or port QMC/Hub proxy is set to listen on).

Case 1:

An inspection of the System_Repository log located in C:\ProgramData\Qlik\Sense\Log\Repository\Trace shows an error message relating to being unable to remove soft deleted records. Sample:

Fatal exception Connection must be opened.??Connection must be opened.??Failed to remove soft deleted records??An exception was thrown while invoking the constructor 'Void .ctor()' on type 'DatabaseContext'

Case 2:

The Repository Service to update fails on a table that iolates foreign key constraint.

C:\ProgramData\Qlik\Sense\Log\Repository\Trace\QLIKSERVER1_System_Repository
Fatal exception update or delete on table "CompositeEvents" violates foreign key constraint "FK_CompositeEventTimeConstraints_CompositeEvents_ID" on table "CompositeEventTimeConstraints"??Failed to execute query: 'DELETE FROM "CompositeEvents" WHERE "Deleted" = true'. Exception: update or delete on table "CompositeEvents" violates foreign key constraint 

 

Environment:

• Qlik Sense Enterprise on Windows 

Cause:

In November 2017, February 2018, April 2018 and June 2018, when the Qlik Sense Repository Service initializes, it attempts to clean up the underlying Repository Database. If there are records which have broken referential integrity then the Repository Service will fail to initialize. This is a consequence of a change in behavior in November 2017/February 2018 where the Repository no longer does soft deletes.

This script can be obtained from the official Qlik site: Failed to remove soft deleted records. 
If logged on to our support site, it can be directly downloaded from the attachment. The attachment is unavailable for anonymous users. 

Resolution:

This article uses the recursive (recurse) cleanup sql script. After upgrading to Qlik Sense November 2017 or February 2018, the administrator is unable to access the Hub and/or QMC.

This article also fixes database issues and soft deletes, which cause numerous other problems besides being unable to access the QMC or HUB after an upgrade. This script is necessary to be run if upgrading from an older version to 11.14.3 or newer.
 

1. Stop all services but the Qlik Sense Repository Database
   • If using a stand-alone PostgreSQL server to host Qlik Sense content, then stop all Qlik Sense Services
2. Take a backup of the Qlik Sense Repository Database
   • Sample command using cmd.exe: cd / & mkdir QSR & "C:\Program Files\Qlik\Sense\Repository\PostgreSQL\9.6\bin\pg_dump.exe" -h localhost -p 4432 -U postgres -b -F t -f "c:\QSR\QSR_backup_%date:~-4,4%%date:~-10,2%%date:~-7,2%.tar" QSR
     • This assumes that:
       • Qlik Sense is installed to C:\Program Files
       • That the site is not using a stand-alone PostgreSQL server
         • Make any necessary adjustments if neither of those assumptions are true
3. Download the attached .sql file and place it into the same directory as pg_dump.exe
4. Run the following command in cmd.exe (run them one at a time and hit Enter then continue with the next one):
   • cd C:\"Program Files"\Qlik\Sense\Repository\PostgreSQL\9.6\bin
   • SET PGCLIENTENCODING=utf-8
   • chcp 65001
   • psql.exe -h localhost -p 4432 -U postgres -d QSR -e -f recurse_cleanup.sql
     • Note: If Qlik Sense is installed to a non-default directory then make the necessary adjustments
5. Start all Qlik Sense Services
6. Validate that the error about Failed to remove soft deleted records is not present in the System_Repository log
7. If this does not resolve the issue then do contact Qlik Support for a more customized query

[Maike151]

Hi Andre,

I upgraded my Qlik Sense Enterprise for Windows from August 2023 to May 2025 and I am facing the same issue. We are using a standalone PostgreSQL-Server

Could this be due to the same cause? Or does this only help when upgrading from a version older than November 2017?

Thank you and regards

Maike