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:
alter FUNCTION dbo.IsAuditEnabled ( )
RETURNS BIT AS
-- Is the Audit Trail feature enabled?
RETURN 0 ; -- 1 = N'Enabled' , 0 = not enabled.
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:
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:
SHRINKDATABASE (PIFD, 10);
Are you adding and deleted the same set of objects, frequently? How many AF objects are in your database?
AF Dev Team
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.
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.
AF Dev Team
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.
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.