Configuring Resource Activity database maintenance
The resource activity deletion and compression utilities provided with Data Governance Edition can help you manage the growth of your database. Settings for the activity compression and deletion utilities can be set in the Data Governance server configuration file, DataGovernanceEdition.Service.exe.config, which is located in the Data Governance server directory.
Enabling and setting up activity database maintenance here in the configuration file ensures that activity is compressed and deleted on a schedule.
The section in the configuration file that controls deletion/compression for activity is as follows:
<!--
Activity compression configuration.
enabled: Indicates whether the scheduled compression is enabled or not.
dailyExecutionTime: Time of day to perform the compression. Format is (h:m:s:[z]). If the time zone [z] is not specified universal time is assumed.
compressOlderThan: The default lower bound for activity data compression. Any activity data older than this value will be compressed.
deleteOlderThan: The lower bound for activity data deletion. Any activity data older than this value will be deleted. Do not specify this value if deletion is not desired.
deletionBatchSize: The batch size used during deletion.
-->
<activityCompressionConfiguration enabled="false" dailyExecutionTime="23:00:0" compressOlderThan="0d" deleteOlderThan="180d" deletionBatchSize="5000">
<!--
NOTE: These values are not to be changed without the assistance of support personnel.
Defines the activity compression passes to perform.
compressOlderThan: The lower bound for activity data compression for a given pass. Any activity data older than this value will be compressed. If this value is not specified the default is used.
aggregationPeriod: The aggregation period to use in a given pass.
-->
<passes>
<add aggregationPeriod="1h"/>
<add aggregationPeriod="1d"/>
<add aggregationPeriod="30d"/>
<add compressOlderThan="180d" aggregationPeriod="180d"/>
<add compressOlderThan="720d" aggregationPeriod="360d"/>
</passes>
</activityCompressionConfiguration>
<!-- Application settings -->
Related Topics
Scheduling activity compression and deletion
Scheduling activity compression and deletion
To ensure that activity data remains manageable and usable, it is recommended that you schedule the Data Governance service to compress or delete activity data once per day. The scheduled compression process aggregates similar activity entries within a given time span into one entry.
Note: Once activity data has been compressed, it cannot be uncompressed.
To schedule activity compression
- Browse to the DataGovernanceEdition.Service.exe.config file (located in the Data Governance Service installation directory).
-
Open the file and navigate to the xml node ‘activityCompressionConfiguration’.
The default values are as follows:
<activityCompressionConfiguration
enabled="false"
dailyExecutionTime="23:00:0"
compressOlderThan="0d"
deleteOlderThan="180d"
deletionBatchSize="5000">
- Enable the scheduled compression, by changing the value of the ‘enabled’ attribute to true.
-
Set the daily execution time through the ‘dailyExecutionTime’ attribute.
The valid format is ‘h:m:s:z’, where ‘h’ is hours, ‘m’ is minutes, ‘s’ is seconds and ‘z’ is the optional time zone offset value. If the time zone [z] is not specified, universal time is assumed.
- Configure which data to compress through the ‘compressOlderThan’ attribute. Any activity data older than this value is compressed.
- Configure which data to delete through the ‘deleteOlderThan’ attribute. Any activity data older than this value is deleted. No deletion occurs if this value is not specified.
- Configure the maximum number of rows to delete at a time through the 'deletionBatchSize' attribute.
- Save your selections.
Manually running the activity compression utility
The Activity Compression utility can be used to further manage the database. It enables the compression of activity from multiple rows into a single row in the database. This utility is located in the Data Governance Server installation directory, %ProgramFiles%\One Identity\One Identity Manager Data Governance Edition\Server\ActivityCompression.exe.
Best practices
Before using this utility:
- Backup the activity database and transaction log. This clears completed transactions from the transaction log file to free space for new transactions generated by running the utility. For additional information, refer to Books Online for SQL Server - Transaction log backups.
Note: The utility will change the SQL Server recovery model to bulk-logged model. This ensures that the transaction log will not grow too large, using up available disk space, while the utility is running.
While running the utility:
-
The utility can compress activity data while managed hosts, with resource activity tracking enabled, continue to monitor changes. However, it will run faster if resource activity tracking is disabled on all managed hosts.
When you specify a large compression granularity (such as 1 year) the utility may use a large percentage of the SQL Server's resources and there is a risk of the SQL commands timing out. To prevent this, stage the compression by first running with a compression granularity of 1 week and then running the utility again specifying a compression granularity of 1 year.
Once you have run the utility:
-
If bulk-logged recovery model is not suitable for your recovery model, change the database recovery model to either simple or full recovery model. See Books Online for SQL Server – Change recovery model.
If the database size and log size are still too large, backup the database again to clear completed transactions from the log file and then execute “DBCC SHRINKDATABASE” on the database to shrink the database and log to the correct size. For more information, see Books Online for SQL Server – DBCC SHRINKDATABASE.
Installation
The ActivityCompression utility is a console program.
To install the utility
- Copy the ActivityCompression utility to the database server or a member server.
- Open a command prompt and enter ActivityCompression to see usage instructions.
Using the utility
ActivityCompression
[-ConnectionString] <"String">
[[-RemainUncompressed] <integer>]
[[-CompressionGranularity] <integer>]
[[-DatabasePlatformOracle] <string>]
Where:
-ConnectionString: The string used to connect to the database.
- SQL Server Authentication: -ConnectionString "Data Source=myServerAddress; Initial Catalog=myDatabase; User Id=myUser; Password=myPassword"
- Windows Authentication: -ConnectionString "Server=myServerAddress;Database=myDatabase;Trusted_Connection=True"
-RemainUncompressed: The amount of time (in days) that will remain uncompressed. By default, this utility will compress all but the 7 days from the current date time. This allows the newest activity in the database to remain uncompressed for more in depth analysis.
Example: -RemainUncompressed 7
-CompressionGranularity: The amount of time (in days, default 7 days) to compress the fine grain activity entries to. If your database is configured with daily activity granularity, setting -CompressionGranularity to 7 will compress all activity within those seven existing timespans into a single timespan with the similar activity over the seven days compressed into single records.
Example: -CompressionGranularity 7
-DatabasePlatformOracle: The database platform used for the activity database. If not specified, this defaults to SQL Server.
Examples
Compress all activity to a 7 day granularity
-ConnectionString "Server=myServerAddress;Database=myDatabase; Trusted_Connection=True" -RemainUncompressed 0 -CompressionGranularity 7
Compress all but the 30 days from current time to a 7 day granularity
-ConnectionString "Server=myServerAddress;Database=myDatabase;Trusted_Connection=True" -RemainUncompressed 10 -CompressionGranularity 7
Notes on running the utility
- The first compression is the most resource intensive. Once the initial compression is completed, future scheduled compressions take a fraction of the time to complete.
- As the number of managed hosts increases, the processing time per record will also increase. This may cause large deployment of 1000+ managed hosts to take a week or more to complete the initial compression.
- The recommended maximum “CompressionGranularity” on the first compression is seven days. This number may be higher depending on the specifications of the SQL Server.
- You can run the compression utility in a production environment. However, we recommend scheduling this process to run on a weekly basis.
- You may run the compression multiple times to create differing levels of detail the further back in time you go.
Assume a batch file running the compression three times with the following settings:
-CompressionGranularity 7 –RemainUncompressed 7
-CompressionGranularity 30 –RemainUncompressed 30
-CompressionGranularity 365 –RemainUncompressed 365
The first run will compress the activity into week long blocks, keeping the most recent week uncompressed. The second run will then further compress the database, compressing activity older than 30 days into month long blocks. The final compression run will compress anything older than 365 days into year time blocks. This will ensure that the most recent activity can be examined at a very detailed level while summing older activity to keep the database size reasonable.
- If you experience timeouts, reduce the CompressionGranularity to one day. Compress further from there. This timeout does not cause any data issues, but it does interrupt the compression and it will need to be run again.
Manually running the activity deletion utility
You can manually run the Activity Deletion utility to remove activities that are no longer useful and are using hard drive space. This utility is located in the Data Governance Server installation directory, %ProgramFiles%\One Identity\One Identity Manager Data Governance Edition\Server\ActivityDeletion.exe.
Best practices
Before using this utility ensure that you:
- Back up the activity database and transaction log. This clears completed transactions from the transaction log file to free space for new transactions generated by the utility. For additional information, see Books Online for SQL Server — Transaction log backups.
Note: The utility changes the SQL Server recovery model to bulk-logged model. This ensures that the transaction log does not grow too large, using up available disk space, while the utility is running.
While running the utility:
-
The utility can delete activity data while managed hosts have activity tracking turned on; however, it is recommended to turn it off while the utility runs to increase performance.
If activity is turned off, we recommend increasing the number of rows to delete from the tables to 100 K. You can increase the batch size by specifying –ActivityDeleteBatchSize 100000 on the command line.
If activity tracking is turned on or you have a database server that is not dedicated as the resource activity database, use the default batch size (5k) or less to avoid having the utility consume all the database servers resources.
Once you have run the utility:
- If bulk-logged recovery model is not suitable for your recovery model, change the database recovery model to either simple or full recovery model. See Books Online for SQL Server — Change recovery model.
Installation
The ActivityDeletion utility is a console program.
To install the utility
- Copy the ActivityDeletion utility to the database server or a member server with .NET 4.5.1 or lower installed.
- Open a command prompt and enter ActivityDeletion to see usage instructions.
Using the ActivityDeletion utility
Note: Ensure that you back up your database before running the utility.
Ensure that the utility has finished processing before running a new instance.
ActivityDeletion
[-ConnectionString] <"String">
[[-DaysOfActivityToKeep] <integer>]
[[-ActivityDeleteBatchSize] <integer>]
[[-DatabasePlatformOracle] <string>]
Where:
-ConnectionString: The string used to connect to the database.
- SQL Server Authentication: -ConnectionString "Data Source=myServerAddress; Initial Catalog=myDatabase; User Id=myUser; Password=myPassword"
- Windows Authentication: -ConnectionString "Server=myServerAddress;Database=myDatabase;Trusted_Connection=True"
-DaysOfActivityToKeep: The amount of time (in days) that remains undeleted. By default, this utility deletes all but 60 days from the current date time.
Example: -DaysOfActivityToKeep 30
-ActivityDeleteBatchSize: The number of rows to delete (at one time) from the database's activity tables. If you have chosen to turn off resource activity tracking while this utility deletes data, then a batch size of 100000 is recommended; otherwise, use the default of 5000.
Example: -ActivityDeleteBatchSize 10000
-DatabasePlatformOracle: The database platform used for the resource activity database. If not specified, this defaults to SQL Server.
Example
Resource activity tracking is turned off; delete all activity older than 30 days:
-ConnectionString "Server=myServerAddress;Database=myDatabase; Trusted_Connection=True" –DaysOfActivityToKeep 30 –ActivityDeleteBatchSize 100000
Troubleshooting
A log file named "ActivityDeletionLog.txt" is generated in the location where the ActivityDeletion utility was started.
The ActivityDeletionLog shows the progress of the utility and any errors it encounters.
