Delete rows in spt_provisioning_transaction

IdentityIQ (IIQ) IIQ Discussion and Questions
1

Hi all,

I have a problem with table size after deleting rows from ‘Filtered’ Provisioning Transactions older than 1 month (epoch type). Database is set for RCSI SI level as IIQ documentation requires. I see rows number decreased after cleanup however clustered index size stayed the same and keeps increasing every day, space is not releasing.

How to release that space from data that has been deleted?
Index has to be rebuild to clean those deleted rows?
Is it increasing because it’s still in ‘version store’ in case someone wants to read that deleted rows? They are not needed anymore and increasing storage is not a resolution here.

Much appreciated for your replies

2

Hi @Undisputted,

which type of DB do you have?

3

SQL Server 2019 Standard

4

ok. Its a best practive have a maintenance plan on DB and schedule it periodically, depending on workload. Usually, running it monthly is enough but if you have an high numeber of changes can you run it biweekly or weekly.

In a maintenance plan, you can rebuild\reorganize index, shrink the db, organize the backups, etc…

Every DB, over time, grows in size, it is inevitable or almost. When you delete a row a DB leave an unused but not free unallocated space and the size grows.
To limit it, you can resize the db(shrink) and rebuild the indexes.

SHRINK
This operation delete the unused space and reduce the size of db. You can apply it on each file(data or log) or on entire DB:

DBCC SHRINKFILE (LogicalFileName, TargetSize);
TargetSize → The desired final size of the file in MB

DBCC SHRINKDATABASE (DBName, TargetPercent);
TargetPercent → The percentage of free space you want to leave in the data or log file.

INDEXES
Usually, if fragmentation is less of 30%, you can reorganize, if it is more, rebuild it
The commands are those:

ALTER INDEX Index_Name ON Table_Name REBUILD

ALTER INDEX Index_Name ON Table_Name REORGANIZE

Those operation helps to reducing the size and improving the performance; in very case, if you not an expert on DB, make those operations with a DBA.

There you can find a topic of mine on db maintenance.

1 Like
5

@enistri_devo

It’s already set every weekend - reindex maintenance for reorganize and rebuild.
Size increase occured during week and cleanup was done from application side day after in spt_provisioning_transaction, so reindex maintenace was not yet executed after.

The problem is that:
When you delete a row a DB leave an unused but not free unallocated space and the size grows.
actually delete didn’t release it as unused. It’s still allocated and fully used in table - clustered index just as it was before delete action.
Shrink won’t help here because .mdf datafile of database keeps decreasing significantly in freespace to use daily due to growth for a single table. There’s nothing to shrink because it looks like nothing was deleted exactly. Delete didn’t release any space - before and after size and unallocated space has been checked. The only difference is in number of rows that decreased.

Normally on all other databases not IIQ related when delete operation remove rows it also releases space in datafile, table, index - in this case in IIQ it doesn’t and I suppose it’s due to RCSI and that reindexation has not been executed yet since that time as it’s waiting for maintenance window.
I hope it will commit those deletes, because it’s simply looks like those deletes were not commited.

So that’s why I ask those 3 questions:
Is RCSI responsible for such behavior? Here’s exact example that occurs within IIQ, that’s why I suspect RCSI: Can deleting rows make a table...bigger? - Brent Ozar Unlimited®
How to release that space from data that has been deleted? Logically within object (table/index) , not physically with datafile shrink
Index has to be rebuild to clean those deleted rows?

Thanks for investigating

6

Ok, RCSI can cause this problem but on tempdb because RCSI use this db to stored the snapshots.
The only way to reduce the size is using rebuild index and shrink.

About your case, how did you delete the lines?
Depending of the configuration, it could possible you had delete the lines and the transaction is still open.

7

Ok, RCSI can cause this problem but on tempdb because RCSI use this db to stored the snapshots.
tempdb is all good, no issues at all - configured as IIQ documentation suggests and best practices

The only way to reduce the size is using rebuild index and shrink.
I will verify that after rebuild tomorrow, however wanted to know if it’s known issue here when housekeeping need to be done before reindex(still don’t know if it will help though). Regarding shrink - only when that space will be seen in datafile as available free space to use again, right now space needs to be extended everyday because keeps growing and not enough.

About your case, how did you delete the lines?
It was done in smaller batches not to overflow whole system:
delete top(10000) database.[spt_provisioning_transaction] where integration = ‘Filtered’ and created < epochdate

Depending of the configuration, it could possible you had delete the lines and the transaction is still open.
I didn’t saw it after execution in Active sessions, so it ended and rows has decreased -that’s also a sign it finished.

Do you have any suggestion regarding above smaller batches method? Any IIQ application specific recommendation with such delete statements here and RCSI for housekeeping large tables(indexes)?

8

the batch is ok. banal question: did you commit the transaction?

Ganerally, Sailpoint recommends use rule(or console commands) into IIQ to remove an object because it could be build with some different tables, but this is not the case for provisioning transaction.
For massive deletions, Sailpoint says you can also use sql statements. Take a look on this page.

9

Yes, it was committed. Even when I do select data from that table where integration = ‘Filtered’ and created < epochdate used for delete doesn’t show any row. Why table is same size then and unallocated space is not changing to higher number that can be reused?

Rebuild has been executed on clustered indexes and it didn’t help release the space in any table, the size of datafile is the same and keeps growing, so unfortunately reindexing is not resolving the issue.
3 milion of rows has been deleted without any space released - based on comparison before/after rowcount and Disk Usage by Top Tables report in SQL Server.

The exact approach has been followed from those articles but it doesn’t work well and didn’t cleanup space. Especially delete without specifing top(10000) rows caused performance issues.

Any other ideas how to approach for such housekeeping in IIQ database?
Some best practices, general rules to follow?
I can see in database snapshot active transactions view that there are implicit transactions since 98hours but it’s for:
select from spt_link_external_attr and select from spt_bundle nothing more related to spt_provisioning_transaction that could hold it

10

can you run this query on IIQ db?

SELECT 
    OBJECT_NAME(object_id) AS TableName, 
    index_id, 
    index_type_desc, 
    ghost_record_count 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED');

take a look if you some ghost records on the table. Also, you can delete the ghost record with:

DBCC FORCEGHOSTCLEANUP (database_id);

1 Like
11

This ran quite long(3hours for now), I can see ghost record count of:
spt_identity_entitlement non clustered index - 248461
spt_identity_entitlement non clustered index - 253772
spt_iidentity_entitlement non clustered index - 258569
spt_identity_entitlement non clustered index - 245651
spt_identity_entitlement non clustered index - 200789
+8 non clustered index in spt_identity_entitlement with close number to those above
spt_link has also many of those on clustered and nonclustered
spt_identity clustered index only - 411
spt_provisioning_transaction clustered index only- 224
spt_entitlement_group clustered index only - 87

there are couple of 1 and most of 0’s in other tables.

DBCC FORCEGHOSTCLEANUP (database_id);- is this safe to run?
From time to time I can see GhostCleanupTask in database snapshot active transactions view is processing.

What I also can see simple reorganize of nonclustered index can run for 3hours. That’s way too long, instead rebuild takes 1-2minutes on clustered index.

Friday 20-12-2024 table spt_provisioning_transaction:
number of rows 11mill, data size 650GB, unused 6GB,
Today 22-12-2024 table spt_provisioning_transaction after cleanup:
number of rows 9mill, data size 672GB, unused 5,7GB

12

I don’t know what happened, but 160GB of space has been just released to .mdf datafile of database and there’s no implicit transactions holding in database snapshot active transactions dynamic view anymore,

spt_provisioning_transaction
number of rows 9mill, data size 635GB, unused 25GB

1 Like
13

good, I think some transaction was closed(which was blocking the update) or most probabily some retencion policy was execute but I can be sure without knowing the entire configuration.

but do you really have 9 million transactions in a month? how many identies you have?(if you can tell)

1 Like
14

What I see is that ghost record count numbers have been cleaned up by GhostCleanupTask - 0 everywhere.
Is that possibly some retencion policy as well? I’ll try to understand all of that with the business and is there a possibility to schedule additional retention policy during week

Unfortunately I cannot tell how many identities.

15

Before this, its better understand you have this behavior on db and if the problem is generated from IIQ or DB. Review the configuration, check which functionality are active and disable the ones you don’t need.
Later, you can configure a daily cleanup on IIQ using the Performance maintenace and set the minimun retention period that you can set for task, syslog, prov.trans ecc…You can also schedule your script.
Otherwise, you can check all this prov. transaction are right, for example NCD can cause a large number of trasaction if it is misconfigured.

If cant apply these countermeasures or find the reason of this behavior, the only way is increase the aviable space for DB.

16

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.