5 Replies Latest reply on May 30, 2009 8:26 PM by pcombellick

    AF Audit Database and SQL Express

    Joe Devine

      We are trying to use SQL Express for our AF work and find that we have reached the limit (4GB) of the SQL Express license primarily due to the Audit train in AF.  Since we programatically modify many values the Audit database grows with every change.  Is there any way to turn this off so that the audit file does not grow so large?  If not does someone know a way to clear the Audit file?

        • Re: AF Audit Database and SQL Express
          pcombellick

          Joe,

           

          Is this a production AF system or a test system?  Iassume that your AF system is v 2.0 or 2.0.4.

           

          If this is a development system, and you have an MSDN subscription, you could use the Developer version of SQL Server, which does not have any of the capacity limits of SQL Express.

           

          Don't try this on a production system, but for your dev/test system, an undocumented method to disable auditing on detail tables is via this sql script:

          USE PIFD; 
          GO
          alter FUNCTION dbo.IsAuditEnabled ( )
          RETURNS BIT AS
          BEGIN
          -- Is the Audit Trail feature enabled?
          RETURN 0 ; -- 1 = N'Enabled' , 0 = not enabled.
          END
          GO

          All audit data is written to tables whose names end in "_AT".  You can use the following script to generate sql to truncate all the audit tables:
          In SQL Server Management Studio (SSMS) , click the "results to text" button then execute the following:

          USE PIFD; 
          GO
          select N'truncate table ' + [name] from sys.tables where [name] like N'%AT';

          This will output a set of truncate table commands, copy the output and execute in the command window of SSMS.  (Don't try this on a production AF database, this will disable the ability to find out which object(s) have changed since the previous "Refresh()" or "FindChanges()".

           

          After truncating the audit tables, you can shrink the database's files:

          DBCC
          SHRINKDATABASE (PIFD, 10);
          GO

          Are you adding and deleted the same set of objects, frequently?  How many AF objects are in your database?

           

          Paul Combellick
          AF Dev Team

            • Re: AF Audit Database and SQL Express
              Joe Devine

              Paul,

               

              Thank you, I had already discovered the Truncate and shrink database scripts and have those running on my development system now to keep the size down.

               

              For my dev system I installed my MSDN SQL Standard edition but I am assuming the Workgroup edition will work as well.

               

              I appreciate your help.

               

              Joe

                • Re: AF Audit Database and SQL Express
                  pcombellick

                  There are some improvements in this area coming in AF2.1.  The af database delete logic has been improved so that deleted af databases actually get cleaned up.  Also, there is a stored procedure (sproc) to brute force delete the audit trail and others to disable/enable audit logging on detail tables (but not "header" tables).

                   

                  In a dev or test environment, consider performing a full sql backup on an "empty" PIFD database, then run your tests, then restore your sql backup to return your PIFD database to "like new" condition.  Certainly don't try this in a production environment.

                   

                  Regards,
                  Paul Combellick
                  AF Dev Team

                    • Re: AF Audit Database and SQL Express
                      pcombellick

                      I recently encountered a use case where the application had written to an attribute value four hundred thousand times and the audit data exceeded 20GB and filled up their disk.  I think that this application should have written to a PI Point, instead of writing the changes to the attribute value stored in the sql server table.  Generally AFElements and AFTables are for configuration data or data that is modified infrequently.  Each time your application writes to a non-configuration attribute value, 7 rows actually get inserted or updated in the sql database (element header, element version, element attributevalue, the audit tables for each, and AF2.1 writes to a table used for FindChanges).  Writes to AFTable are almost as busy.  All these writes are useful for auditing changes and to enable client apps to relatively efficiently find which objects have changed.  For new installs of AF2.1, auditing will be partially disabled by default.  The AF Team will likely refactor the auditing so that it can be completely disabled.

                        • Re: AF Audit Database and SQL Express
                          pcombellick

                          When installing AF 2.0.x, auditing is enabled.  When upgrading to AF2.1, auditing will still be enabled.  This means that any change to AF data, will store all changes to audit tables.  When installing AF2.1 on a new system, auditing logging will be completely disabled, by default.  However, a list of objects that have changed will be maintained for a month.  AF 2.1 supports a commandline tool to enable or disable auditing and to delete all the audit data.