IIQ DB Size not decreasing even after clearing 75% of identities and 75% of provisioning transactions

IdentityIQ (IIQ) IIQ Discussion and Questions
, ,
1

Which IIQ version are you inquiring about?

8.4 p1

Cleared provisioning transactions from 240 days to 60 days, almost 75% count reduced. Cleared all identities from years ago. But DB size remained same. Any suggestions on how to keep DB size in check.

2

Hi @nvk1

Suggestions to Keep Database Size in Check:

Database Maintenance Tasks (Crucial):
This is the most important step. You need to perform database-specific maintenance tasks to reclaim unused space and defragment the database. The exact commands will depend on the database system you are using (e.g., Oracle, SQL Server, MySQL).

In summary, while clearing old data within IdentityIQ is a good first step, you must perform database-level maintenance to reclaim the disk space. Focus on shrinking the database files and rebuilding/reorganizing indexes as the primary actions. Remember to consult your database administrator for the specific commands and best practices for your database system.

3

Did you do the shirnk\reside of DB?

4

How sure are you that those were the objects taking up all of the space? We had a runaway syslog event table taking up about 1TB, and some of our LOB columns are pretty high too. If you’re using an Oracle DB, you can run something like this to double check.

SELECT
    SZ.OWNER,
    SZ.SEGMENT_NAME,
    SZ.GB,
    COALESCE(DL.TABLE_NAME, DI.TABLE_NAME) AS TABLE_NAME,
    DL.COLUMN_NAME AS LOB_COLUMN_NAME
FROM
    (SELECT
         OWNER,
         SEGMENT_NAME,
         SUM(BYTES) / 1024 / 1024 / 1024 AS GB
     FROM
         DBA_SEGMENTS
     WHERE
         OWNER = 'IDENTITYIQ'
     --AND SEGMENT_TYPE = 'TABLE'
     GROUP BY
         OWNER,
         SEGMENT_NAME
     HAVING
         (SUM(BYTES) / 1024 / 1024 / 1024) > 1) SZ
    LEFT OUTER JOIN DBA_LOBS DL ON SZ.OWNER = DL.OWNER AND SZ.SEGMENT_NAME = DL.SEGMENT_NAME
    LEFT OUTER JOIN DBA_INDEXES DI ON SZ.OWNER = DI.OWNER AND SZ.SEGMENT_NAME = DI.INDEX_NAME
ORDER BY
    GB DESC;
5

@nvk1 Please work with Database team and rebuild the index.

6

Hi @nvk1,

If you are able to get this data in your IdentityIQ database then it will give a clarity on which IdentityIQ table is taking more space and from there you can prune or clean up those objects accordingly.

To get the data, you have to know how much space each table in your database is using

a) total space allocated to the table
b) how much of the table space is used
c) how much space is allocated but not currently used.

you need to join system views that track tables and their indexes, how much data is stored in pages, how many rows in each table.

finally sort the results so the biggest tables with more MB, then those tables you need to start pruning those objects. (Example: syslogevent, provisioning transactions, audit events (if enabled many), task results, certification history).