Increase in SAP IQ USER DBSPACE Post Secondary Node Failure.

[Former Member]

Secondary Writer node in a Multiplex Environment of 2 Nodes is down for extended time due to Server Hardware Issue.

Post this we have seen a major jump in usage of MAIN IQ USER DBSPACE.
sp_iqstatus, Other Version Parameter showing 500 Gb Space. sp_iqtransaction showing 5k rows which are in Commited state for long and not getting into APPLIED State. These 5k records are having null values in UserId,Name Columns .

No Active transaction are present.

sp_iqtransaction snapshot

(NULL),(NULL),2615720,2618013,2618013,COMMITTED,0,0,2635352,2037008,0,0,0,0000-01-00 00:00:00.000,0,1,0,(NULL),0,Table-level,False,0
(NULL),(NULL),2715389,2716773,2716773,COMMITTED,0,0,6004496,3588432,0,0,0,0000-01-00 00:00:00.000,0,1,0,(NULL),0,Table-level,False,0
(NULL),(NULL),2637243,2642922,2642922,COMMITTED,0,0,3477600,312,0,0,0,0000-01-00 00:00:00.000,0,1,0,(NULL),0,Table-level,False,0

Even after Coordinator startup (which is doing recovery as seen in the logs and taking atleast 1.5 hour to start) the Space is not getting released ?

Snapshot from IQMSG

I. 11/23 16:45:21. 0000000000 Collation UTF8BIN, Case Respect, Blank Padding On, Comparisons are Conditioned
I. 11/23 16:45:32. 0000000000 RcvyCmpl
I. 11/23 16:45:32. 0000000000 Chk
I. 11/23 16:45:47. 0000000000 ChkDone [NumTxnCP: 57819]
I. 11/23 16:45:47. 0000000000 PostChk
I. 11/23 16:45:52. 0000000000 CloseDatabase

sp_iqstatus snapshot still shows 550 GB under Other Versions

Main IQ I/O:,I: L1474304/P250156 O: C725/D2275/P1758 D:207 C:47.7
Temporary IQ I/O:,I: L549630/P0 O: C35264/D36182/P1228 D:34642 C:100.0
Other Versions:,57820 = 550Gb
Active Txn Versions:,0 = C:0Mb/D:0Mb


sp_iqversionuse command is hanging.

How we can release these 500+ Gb Space. Would converting the Multiplex to Simplex as the last Secondary Node is down for some days now due to Hardware issue would resolve the issue.

[Gisung · Accepted solution]

Hi,

Here are steps to clear up versioning size using -iqmpx_sn switch.

1) Restart IQ Coordinator node with -iqmpx_sn 1/-gm 1 (in single node mode) which clear up versioning.
2) Restart IQ normally without -iqmpx_sn switch
3) Re-sync ALL secondary nodes.
4) Check the versioning size with sp_iqstatus proc.

Best Regards,
Gi-Sung Jang

[Former Member]

Thanks Pal. Post release of versions I have also excluded the Secondary Server in multiplex till Hardware correction .