Deleting audit rows?

Unknown said:

We seem to have experienced a pretty significant increase over the last few months in the number of audit rows created for inserts and deletes on t_cust_rank. I'm seeing some accounts with dozens of inserts and deletes within a couple minutes in the middle of the same night. Initially I thought it may be related to the Membership Update Utility, which causes lp_customer_rank to fire, but we haven't changed our schedule or parameters so I don't know what would have caused the sudden uptick. Has anybody else experienced something similar?

The only apparent issue so far is that it's more challenging to find useful information on somebody's audit tab when one has to scroll through hundreds of t_cust_rank rows, especially considering that we're not currently using the ranking functionality, (though we may start.)

We have done both, i.e. we regularly purge rank updates from the audit table, and have commented out ranks we don't use from lp_customer_rank. The audit data is much more useful now.

I am very new to Tessitura but have ran into this already.  We have turned off the triggers, for the time being.  We also purged the audit table of duplicates.

In researching this a little further, I believe there is a nightly sql agent job that updates rank as well as a trigger on the t_customer table that updates it when a customer record is updated.  You can start to see more if you use select object_name(id) from syscomments where text like '%t_cust_rank%' .

I can send you the script we are going to use (still testing it) to purge the audit table, if you'd like, just let me know.

Thanks for the input, everybody. We'll probably tweak the procedure and dump the old audit rows.

For what it's worth, looking at it in a little more detail I suspect that the increase coincided with our upgrade from 11 to 12.1. In exploring this issue I made a minor edit to a plan on one account and it looks like the ranking procedure got called three times as a result--there are six new audit rows resulting from that one edit!

We ran into the same problem. Had to rewrite the script to just change records that need update rather than update all records.

--Tom

On Thu, Aug 13, 2015 at 10:12 AM, Tessitura Technical Forum wrote:

I am very new to Tessitura but have ran into this already. We have turned off the triggers, for the time being. We also purged the audit table of duplicates.

In researching this a little further, I believe there is a nightly sql agent job that updates rank as well as a trigger on the t_customer table that updates it when a customer record is updated. You can start to see more if you use select object_name(id) from syscomments where text like '%t_cust_rank%' .

I can send you the script we are going to use (still testing it) to purge the audit table, if you'd like, just let me know.
From: Matthew Echert
Sent: 8/12/2015 4:01:17 PM

We seem to have experienced a pretty significant increase over the last few months in the number of audit rows created for inserts and deletes on t_cust_rank. I'm seeing some accounts with dozens of inserts and deletes within a couple minutes in the middle of the same night. Initially I thought it may be related to the Membership Update Utility, which causes lp_customer_rank to fire, but we haven't changed our schedule or parameters so I don't know what would have caused the sudden uptick. Has anybody else experienced something similar?

The only apparent issue so far is that it's more challenging to find useful information on somebody's audit tab when one has to scroll through hundreds of t_cust_rank rows, especially considering that we're not currently using the ranking functionality, (though we may start.)

Related question: I assume it would not be a best practice to modify the triggers on t_cust_rank. Is it safe to periodically delete the t_cust_rank rows from the audit table?



This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!