Changing the OperationsManagerACS Database Retention Period

Operations Manager grooms, or removes, data from its various databases at regular configurable intervals. For the Operations database, this is done in the Operations console. For the ACS database, these settings are configured during setup with a default value of 14 days. This means that every day, all database partitions (and their data) that are older than 14 days are dropped or deleted. Because of the volume of data that ACS can accumulate, 14 days is not an unreasonable setting. However, some companies might need to retain data for longer periods and they must have already planned for that when making the sizing and performance calculations for their environment. You can change the retention period for the ACS data by following this procedure.


To change the ACS data-retention period:
1. Log on to the computer running SQL Server that hosts the ACS database with an account that has administrative rights to the ACS database.
2. Open the SQL Server Management Studio tool, and connect to the database engine.
3. Expand the Databases folder, and select the OperationsManagerAC database.
4. Right-click to open the context menu and select New Query.
5. In the Query pane, type the following, where number of days to retain data + 1 equals the number of days you want to pass before data that has aged past that point is deleted. For example, if you want to retain data for 30 days, type 31.

USE OperationsManagerAC UPDATE dtConfig SET Value = <number of days to retain data + 1> WHERE Id = 6 and then click the Execute button on the toolbar. This runs the query and then opens the Messages pane, which should read (1 row(s) affected).

6. To view the new setting, delete the previous query text from the Query pane and type SELECT * FROM dtConfig. This opens the Results pane below the Query pane.
7. Look at the value in the sixth row; it should now equal the value you entered for <number of days to retain data + 1>.
8. Restart the Operations Manager Audit Collection Service for this to take effect.

If you are not that fimiliar with SQL or do not have the correct privileges to do it on SQL then you can always do it from the SCOM ACS Collector server.

Each Collector has AdtAdmin.exe command to configure ACS related settings and options. This AdtAdmin command is mainly use to define the filter to collect the security events. However, 2 parameters of this command are: -getpartitions and -setpartitions. Which are to show and manipulate the partition settings of the ACS database. –getpartitions is to get the current number of partitions settings and the –setpartitions is to update the desire value on the database according to retention period. The procedure to update the period using the –setpartitions is:

1. Log on to the collector server
2. Open the command prompt (if Windows 2008 or 2008 R2 or 2012, open with elevated access)
3. Browse to C:\Windows\System32\Security\AdtServer
4. Type the following command:

AdtAdmin.exe -getpartitions -collector:<FQDN of ACS collector server>

This will show the current number of partitions settings from the database

5. Type the following command to update the database:

AdtAdmin -setPartitions -collector:<FQDN of ACS collector server> -value:<number of days to retain data + 1>

6. Type the following command again:

AdtAdmin.exe -getpartitions -collector:<FQDN of ACS collector server>

This will show the updated value

7. Restart the Operations Manager Audit Collection Service for this to take effect.

Hope that this post was helpful.