One of the other questions i have is the huge size of two CDC tables:
- cdc.dbo_AFElementAttributeValue27_CT has 5M6 records consuming almost 22Gb. What are the possible causes of that? We cannot have had that many changes on the values/ We don't (or should not) have Analysis writing back to Attibutes. We do have a lot of Ad-Hoc Analysis (Analysis DataReference), but my assumption is that those should not cause this # of records. The dbo.AFElementAttributeValue table is just 2765 records summing to 2.7Mb, which is a ratio (on records) of 2000:1. Does that mean i have 2000 changes captured for every attribute???
- cdc.dbo_AFEventFrameAttributeValue27-CT has 31M records consuming almost 19Gb. The underlyhing table dbo_AFEventFrameAttributeValue has 7M5 records for 2Gb. That is a ratio of 3:1 for the changes, meaning data base been recaptured at least 3 times on average?
You might want to take stock of the change records broken down by attributes like __$operation or rid.
The __$operation would give you insight on what types of changes are being made. For example, a lot of updates would mean a lot of records with __$operation 3 (old value) and 4 (new value).
The rid would give you insight on if changes are being made to the same attribute values over and over.
Given the size of the tables, I suspect there is one workflow that regularly updates element attribute values and another workflow which occasionally updates event frame attribute values.
If you need additional assistance, I suggest reaching out to our Technical Support team.
Thanks for the excellent info! Really helpful, you showed me the smoking gun. We indeed have an application that updates EventFrame attributes regularly, and that causes the bulk of the Audit Trail records. I will look into pruning specific sets of data for this AF database that we don't need to Audit in the first place.
I noticed another application that causes a lot of records, and that is the Integrator for Business Analytics, which keeps a DataCache in AF. Just a quick analysis shows about 50% to 75% of the Audit Trail records are caused by entries for the Integrator in the configuration database.
As that is a real issue, i will create a techsupport ticket for that...
There are 2 SQL Agent jobs to service the audit trail feature, a:
- Capture job (e.g. cdc.PIFD_capture)
- Cleanup job (e.g. cdc.PIFD_cleanup)
The cleanup job can help you prune your change tables. You'll want to:
- Configure the cleanup job (e.g. set the desired retention period)
- Enable the cleanup job
The cleanup job can be configured by executing sys.sp_cdc_change_job. The following example changes PIFD's CDC cleanup job to have a 1 year retention period:
--configure cleanup job retention for 1 year (525600 minutes) USE PIFD; EXEC sys.sp_cdc_change_job @job_type = N'cleanup', @retention=525600;
To confirm the settings for the job, we can use sys.sp_cdc_help_jobs:
--confirm settings USE PIFD; EXEC sys.sp_cdc_help_jobs;
Enabling the cleanup job can be done with SSMS or with TSQL (dbo.sp_update_job). In SSMS:
- Expand SQL Server Agent
- Expand Jobs
- Right-click on the cleanup job and click Enable
Further customizations of the cleanup job might be editing its threshold value (by default it deletes in chunks of 5000) or changing its schedule (by default it runs nightly at 2am).
Great answer! I'll go ahead and work with this to get a grip on my space usage. I would be good to add this information in some way to the documentation, but on the other hand, PI Square is the place to go for some more in-depth topics anyway.
I am wondering if there is a way to modify the cleanup job in such a way that the retention is configurable for each AF database. E.g. i don't need an Audit Trail for the OSI Configuration DB, but i do need that for the DB that users use.\
I also created a feedback item on this: Configurable pruning of the Audit Trail – Customer Feedback for OSIsoft & the PI System
The stock CDC functionality does have features to remove specific tables from the CDC: sys.sp_cdc_disable_table (Transact-SQL) | Microsoft Docs so that might be useful to remove e.g. the EventFrameAttributeValues table, but i can't do that on the ElementAttributeValues table without impacting the reason we need the Audit Trail. Some food for thought to think about, but will need to do that later. Customizing CDC seems possible after some web reading, but it seems best to be left to a competent DBA which i consider myself not to be...