5 Replies Latest reply on Jul 6, 2012 1:40 PM by pcombellick

    How to fully disable AF auditing?

    jharrel

      Reporting from our AF 2010 R3 environment recently came so a near shutdown because of AF auditing.  More specifically, there were 85 million records in the FindChanges_AT table, which is an AF auditing table.  This caused a major slowdown in the sql server database.  When i run afdiag.exe or the function "dbo.isAuditEnabled", it shows that auditing is disabled...obviously, this isnt the case.  Has anyone else experienced this?  Is there a way to fully turn off AF auditing?  If i can't disable the auditing, my plan is to just purge the table daily. 

       

      Also, it appears that AF purges records older than 30 days and i can see a stored procedure (usp_audit_findchanges_delete30days) in sql server database that does this.  How does that purge get triggered?  

       

      Thanks ahead of time for your input!

       

      Josh

        • Re: How to fully disable AF auditing?
          pcombellick

          The FindChanges_at table is used by the AFDatabase.FindChangedItems() and AFDatabase.Refresh() methods.  It is not possible to disable the use of this table.

           

          85 Million rows in FindChanges_AT may indicate that some client process is writting to AFAttributes that should probably be PI Point references.

           

          It is very uncommon to see 85 million records in FindChanges_AT, but it occasionally happens.  Perhaps you can contact OSIsoft Technical Support so that we can understand your specific situation and offer a remedy.

           

          Regards,

           

          Paul

           

          AF Dev

            • Re: How to fully disable AF auditing?
              jharrel

              Thanks Paul.  Do you happen to know the purpose of the audit table?  Seems like disabling the auditing should actually disable ALL of the auditing.  I have an open ticket with support, hopefully they can shed some light on the problem.

                • Re: How to fully disable AF auditing?
                  pcombellick

                  The FindChanges_at table is used by the AFDatabase.FindChangedItems() and AFDatabase.Refresh() methods to determine which AF objects changed since the last call the FindChangedItems() or Refresh().

                    • Re: How to fully disable AF auditing?

                      The difference here though is audit vs sandbox in AF, right?  Sandbox changes (changes made by a User Identity) have to be stored somewhere so that changes are persisted across sessions.

                        • Re: How to fully disable AF auditing?
                          pcombellick

                          Rhys,  You are correct.  In AF, the sandbox and the audit trail are separate concepts and use different tables for storage.  The sandbox holds transient changes and are only visible to a single user.   The audit trail, which is turned off by default, holds a journal of all changes, including field level details, that occurred to any AF2.x object, since the audit trail was enabled.  The FindChanges_at table, which cannot be disabled, contains a journal of object identities that have changed since the FindChanges_at table was trimmed or truncated.  

                           

                          When a client process first starts up, it can call AFDatabase.FindChangedItems() to retreive a cookie that contains a key into the FindChanges_at table.  This cookie can be passed to the AF Server to find any changes that have occurred since the last time the client requested changes.  This can be quite efficient, since the AF Server can cache the last change key in memory and since the key is an identity column in the FindChanges_at table, SQL Server can return the max key value without performing any IO.

                           

                          ProcessBook calls FindChangedItems() every few seconds.  Notification Server also calls FindChangedItems() periodically.  PSE calls FindChangedItems() when the user hits "refresh".

                           

                          Where you run into performance problems, is when the FindChanges_at table grows large (millions of rows) and the client application does not keep track of the cookie, but simply requests all of the contents of the FindChanges_at table.  We have seen a few cases where the FindChanges_at table grows to tens of millions of rows and the cleanup job has failed at purging rows that are older than 30 days.  This warrants investigation as to why there are millions of edits to AF objects within about 30 days.  Perhaps this application is writting to an AFAttribute that is stored in SQL Server when the AFAttribute should reference a PI Point.  SQL Server is not a substitute for the PI Server.

                           

                          Regards,

                           

                          Paul

                           

                          AF Dev