ACS Data Retrieval Limited: Operations Manager Flags 42-Day Window

Table of Contents

Managing data retention for Audit Collection Services (ACS) in Microsoft System Center Operations Manager (SCOM) is a critical administrative task. Proper configuration ensures that audit data is retained for compliance, reporting, and analysis purposes while also managing database size and performance. The default retention period in ACS is often set to a relatively short duration, such as 42 days, to balance storage consumption and immediate reporting needs. However, many organizations require longer retention periods to meet regulatory requirements or internal policies. Adjusting this period involves modifications to both the ACS database configuration and the stored procedures responsible for managing database partitions. This process requires careful planning and execution to avoid performance degradation or data loss.

Understanding the structure of the ACS database is essential before making changes to retention settings. The database, typically named OperationsManagerAC, stores a significant volume of audit event data. To handle this large amount of data efficiently, the database utilizes partitioning. Partitioning breaks down large tables into smaller, more manageable pieces based on a specified criteria, often time. This approach improves query performance for recent data and simplifies data archiving or deletion strategies for older data. The effectiveness of partitioning is tied directly to the number of partitions managed by the database and the views used to query across them.

One of the primary factors influencing how much data the ACS database accumulates is the number of workstations and servers being audited. Each audited system generates a stream of security and system events that are forwarded to the ACS collector and then inserted into the database. The volume of data can vary significantly depending on the type and activity level of the audited systems, as well as the specific audit policies configured. While an approximation like 7.6 MB per day per workstation provides a general guideline, actual data growth should be monitored closely to inform storage planning. Predicting future growth based on this rate and the desired retention period is a necessary step in capacity planning for the database server.

Adjusting the Primary Data Retention Period¶

The most straightforward step in extending ACS data retention involves updating a configuration value within the OperationsManagerAC database itself. This value dictates the nominal number of days the system is configured to retain data. However, as we will see, this is not the only setting that needs adjustment, and changing only this value may not achieve the desired result or could even lead to inconsistencies.

To modify this setting, a SQL query is executed directly against the ACS database. This query targets the dtConfig table, which holds various configuration parameters for the ACS service. Specifically, the parameter with Id = 6 controls the data retention period used by certain internal processes.

The SQL command required is as follows:

USE OperationsManagerAC
UPDATE dtConfig
SET Value = <number of days to retain data + 1>
WHERE Id = 6

In this command, <number of days to retain data + 1> should be replaced with the desired retention period in days, plus one. For instance, if the goal is to retain 7 days of data, the Value should be set to 8. The addition of ‘1’ is a nuance of how the internal processes interpret this configuration value, often related to boundary conditions or the number of partitions to consider.

It is crucial to understand that historical versions of SQL Server, such as SQL Server 2005 mentioned in some documentation, had limitations on the number of partitions that could be referenced within a single view. A common limit cited was 255 partitions. While modern versions of SQL Server have significantly higher limits, the underlying principle remains: referencing an excessive number of partitions in a view can negatively impact query performance. Setting the Value in dtConfig to a very high number, even if technically possible with modern SQL Server, can result in slower reporting times and increased load on the database server. Therefore, performance considerations must weigh heavily when deciding on the new retention value.

Furthermore, relying solely on this dtConfig value might not fully control the actual deletion of older data partitions or how much historical data is visible in the default ACS reports. The ACS database management relies on scheduled jobs and stored procedures that manage the creation, maintenance, and deletion of partitions. These procedures often contain hardcoded limits or logic that must also be adjusted to align with the new desired retention period set in dtConfig. This is why the subsequent steps involving stored procedures are equally, if not more, important.

Before executing this SQL query, it is strongly recommended to perform a full backup of the OperationsManagerAC database. Modifying database configuration values directly carries inherent risks, and a backup provides a recovery point in case of errors or unexpected behavior. Additionally, scheduling this change during a maintenance window is advisable to minimize potential disruption to reporting or data collection, although this specific query is typically low impact.

Modifying Stored Procedures for Partition Management¶

Simply changing the value in the dtConfig table is often insufficient because the stored procedures responsible for creating and deleting database partitions, and perhaps more importantly, the views that query across partitions for reporting, may have their own internal logic or hardcoded limits. The default configuration in older versions of ACS often included stored procedures that limited the number of partitions considered by key reporting views to a specific number, such as 42. This limit was likely intended to maintain reporting performance by restricting the scope of queries to a manageable number of partitions, directly tying back to the performance considerations mentioned earlier regarding the number of partitions in a view.

Two key stored procedures located on the ACS collector server are typically involved in this partition management: DbCreatePartition.sql and DbDeletePartition.sql. These files contain the SQL code that is executed by the ACS service or related scheduled jobs to manage the database.

Adjusting Partition View Creation Logic¶

The DbCreatePartition.sql file contains the code executed when the ACS system determines that a new data partition is needed (typically daily). Within this script are definitions for several views that provide aggregated access across the daily partitions. Views like AdtServer.dvall, AdtServer.dvall5, and AdtServer.dvheader are fundamental for ACS reporting. dvall typically provides access to all event data, dvall5 might limit data to the first few hundred characters of the event string (for performance), and dvheader might access only the header information without joining to the potentially large string tables.

In older or default configurations, the definition within these view creation blocks often includes a SELECT TOP clause that limits the number of most recent partitions included in the view. A common default seen is SELECT TOP 42 PartitionId. This means that even if the database contains data older than 42 days, the standard reporting views will only query the most recent 42 days of partitions, effectively limiting reportable data to that window regardless of the dtConfig value.

To extend the reporting window, this TOP limit within the stored procedure must be increased to match or exceed the desired retention period set in dtConfig. The process involves editing the DbCreatePartition.sql file, which is typically found in C:\\Windows\\System32\\Security\\AdtServer on the ACS collector server. Inside this file, locate the sections defining the views mentioned (dvall, dvall5, dvheader). Within each section, find the DECLARE cPartition CURSOR FOR SELECT TOP 42 PartitionId... line and change 42 to the new desired number of days (e.g., 100, or whatever matches your dtConfig value).

/*************************************************
*
* Create or update dvAll, the view across all partition views
*
**************************************************/

declare @iIsFirst int
declare @vchStmt nvarchar(max)
declare @vchPartitionId nchar(36)
declare cPartition cursor for
select top 100 PartitionId from dtPartition order by PartitionCloseTime desc -- Changed 42 to 100

/***************************************************
*
* Create or update dvAll5, the view across all partition views limited to the first 5 strings
*
****************************************************/

declare @iIsFirst int
declare @vchStmt nvarchar(max)
declare @vchPartitionId nchar(36)
declare cPartition cursor for
select top 100 PartitionId from dtPartition order by PartitionCloseTime desc -- Changed 42 to 100

/****************************************************
*
* Create or update dvHeader, the view across all partition views with no dtstring joins
*
*****************************************************/

declare @iIsFirst int
declare @vchStmt nvarchar(max)
declare @vchPartitionId nchar(36)
declare cPartition cursor for
select top 100 PartitionId from dtPartition order by PartitionCloseTime desc -- Changed 42 to 100

(Note: The SQL code above is illustrative, showing the change from 42 to 100. The actual content of the file is much larger and defines the entire stored procedure and view definitions.)

After editing and saving the file, the stored procedure needs to be re-registered with the database. This is typically done by running the modified .sql script against the OperationsManagerAC database using SQL Server Management Studio (SSMS) or the sqlcmd utility. Running the script effectively drops and recreates the stored procedures and the associated views with the new TOP limit.

Adjusting Partition Deletion Logic¶

The DbDeletePartition.sql file contains the code responsible for cleaning up older data partitions. While the dtConfig value suggests the desired retention, the actual deletion process might also be governed by logic within this script. Similar to DbCreatePartition.sql, this script might contain logic that determines which partitions are eligible for deletion, potentially based on the same TOP limit seen in the create script.

Editing the DbDeletePartition.sql file involves navigating to the same C:\\Windows\\System32\\Security\\AdtServer directory. Examine the script for any sections that might reference a hardcoded number of partitions to keep before deleting the rest. While the provided example code snippet in the prompt appears identical to the DbCreatePartition.sql snippet (referencing the views dvall, dvall5, dvheader), this strongly suggests that the views defined by the creation script are also implicitly used or referenced by the deletion script’s logic to determine the range of partitions to manage. Therefore, ensuring consistency between the TOP limit in both DbCreatePartition.sql and DbDeletePartition.sql (specifically, in the view definitions they contain or reference) is critical. Change any instances of SELECT TOP 42 PartitionId within the view definitions or any logic related to partition selection for deletion to the new desired number (e.g., 100).

/*****************************************************
*
* Create or update dvAll, the view across all partition views
*
******************************************************/\
-- Note: The actual deletion logic is elsewhere in the script,
-- but the views defined here might influence which partitions
-- are considered 'current' or within the reporting window
-- by the deletion logic. Consistency is key.
declare @iIsFirst int
declare @vchStmt nvarchar(max)
declare @vchPartitionId nchar(36)
declare cPartition cursor for
select top 100 PartitionId from dtPartition order by PartitionCloseTime desc -- Changed 42 to 100

/******************************************************
*
* Create or update dvAll5, the view across all partition views limited to the first 5 strings
*
*******************************************************/

declare @iIsFirst int
declare @vchStmt nvarchar(max)
declare @vchPartitionId nchar(36)
declare cPartition cursor for
select top 100 PartitionId from dtPartition order by PartitionCloseTime desc -- Changed 42 to 100

/******************************************************
*
* Create or update dvHeader, the view across all partition views with no dtstring joins
*
*******************************************************/

declare @iIsFirst int
declare @vchStmt nvarchar(max)
declare @vchPartitionId nchar(36)
declare cPartition cursor for
select top 100 PartitionId from dtPartition order by PartitionCloseTime desc -- Changed 42 to 100

(Again, the SQL code above is illustrative, showing the change from 42 to 100 in the view definition sections. The full script’s deletion logic would also need review.)

Just like with DbCreatePartition.sql, after editing DbDeletePartition.sql, the script must be executed against the OperationsManagerAC database to update the stored procedure definition. This ensures that the database partition management processes utilize the revised logic for both creating and deleting partitions and defining the reporting views.

It is paramount to treat these .sql files with extreme caution. They are core components of the ACS database management. Before making any changes, create backup copies of the original files. Test the modified scripts in a non-production environment if possible. Errors in these scripts can halt partition management, leading to unbounded database growth or incorrect data deletion.

Considerations for Extended Retention¶

Extending ACS data retention beyond the default 42 days, or even significantly increasing it, has several important implications that must be carefully considered and planned for.

Storage Requirements¶

The most immediate impact of increased retention is on disk space. Audit data accumulates daily, and retaining it for a longer period directly translates to requiring more storage space on the database server. Based on the estimate of 7.6 MB per day per workstation, retaining data for 100 days instead of 42 for 1000 workstations would require an additional (100 - 42) * 7.6 MB/workstation * 1000 workstations which is approximately 58 * 7.6 * 1000 MB = 440,800 MB or about 440 GB of additional storage over the long term, not including database overhead, indexes, or temporary space needed for queries. Accurate capacity planning based on actual data growth observed in your environment is crucial to avoid running out of disk space.

Database Performance¶

Increasing the number of partitions that reporting views must query significantly impacts performance. While modern SQL Server versions handle partitioning much better than older ones, querying across hundreds or even thousands of partitions for complex reports will take longer and consume more resources (CPU, RAM, I/O).

• Query Execution Time: Reports will take longer to generate, potentially timing out or negatively affecting user experience.
• Database Server Load: Increased query complexity and data volume will put more load on the SQL Server, potentially impacting other databases hosted on the same instance.
• Index Maintenance: Managing indexes across a larger number of partitions requires more resources and time during maintenance windows.
• Partition Management Jobs: The daily or scheduled jobs that manage partitions (creation, deletion, index maintenance) will also take longer to complete.

Performance testing in a test environment with a representative data volume and retention period is highly recommended before implementing changes in production. Optimizing database hardware (faster I/O, more RAM, faster CPU) might be necessary to support longer retention periods without unacceptable performance degradation.

Backup and Recovery¶

Larger databases take longer to back up and restore. A 1 TB ACS database will require a different backup strategy and recovery time objective (RTO) than a 100 GB database. Ensure your backup infrastructure and processes can handle the increased size and that your disaster recovery plan accounts for the longer time required to restore the larger ACS database.

Monitoring and Maintenance¶

With increased retention, proactive monitoring of the ACS database becomes even more critical. Monitor database size, free disk space, query performance, and the success/failure of partition management jobs. Regularly review SQL Server error logs and SCOM event logs for any issues related to ACS database operations. Regular database maintenance tasks, such as index rebuilds/reorganisations and integrity checks, become more time-consuming and require careful scheduling.

Alternatives and Archiving¶

For very long retention requirements (e.g., years), keeping all data online in the primary ACS database might not be the most efficient or cost-effective approach. Consider implementing a data archiving strategy. This typically involves exporting older data partitions from the online database and storing them in a separate archive database or file storage for long-term, less frequent access. While SCOM ACS itself doesn’t provide a built-in archiving mechanism, custom solutions using SQL Server features (like SSIS packages) or third-party tools can be developed. Archiving helps keep the online database size manageable while still meeting compliance requirements.

Step-by-Step Implementation Summary¶

To summarize the process of extending ACS data retention:

1. Plan: Determine the required retention period based on compliance or reporting needs. Calculate estimated storage growth. Assess the current performance of the ACS database and server.
2. Backup: Perform a full backup of the OperationsManagerAC database.
3. Modify dtConfig: Execute the UPDATE dtConfig SQL query to set the desired retention value (+1).
4. Backup Stored Procedures: Make backup copies of C:\\Windows\\System32\\Security\\AdtServer\\DbCreatePartition.sql and C:\\Windows\\System32\\Security\\AdtServer\\DbDeletePartition.sql.
5. Edit Stored Procedures: Edit the backup copies of DbCreatePartition.sql and DbDeletePartition.sql using a text editor like Notepad. Locate instances of SELECT TOP 42 PartitionId within the view definitions (dvall, dvall5, dvheader) and change 42 to your new desired retention value. Save the modified files.
6. Update Stored Procedures in DB: Execute the modified DbCreatePartition.sql and DbDeletePartition.sql scripts against the OperationsManagerAC database using SSMS. This will drop and recreate the stored procedures and affected views.
7. Monitor: Closely monitor the ACS database size, disk space, and performance over the following days and weeks. Check the event logs on the ACS collector and SQL Server for any errors related to partition management. Verify that older data is being retained and that new data is being collected.

This process requires careful attention to detail. Making errors in the SQL scripts or modifying the wrong values can lead to significant issues. If possible, test these changes in a lab environment that mimics your production setup.

Visualizing Partition Management¶

A simple way to think about how partitions are managed is through a timeline. Each day, a new partition is created. The system then manages a “window” of recent partitions for reporting views and defines a “cutoff” point for deletion.

Here’s a conceptual representation using Mermaid syntax:

```mermaid
graph TD
A[Day 1 Partition] → B(Day 2 Partition);
B → C(Day 3 Partition);
C → D[…]
D → E[Day N Partition];
E → F[New Partition Today];

subgraph Reporting Window (TOP X Partitions)
    E -- Reports query --> F;
end

subgraph Deletion Logic (Delete Partitions older than Y days)
    A -- Scheduled Job Deletes --> Cleanup;
end

F -- Created by --> DBCreatePartition.sql;
Cleanup -- Influenced by --> DbDeletePartition.sql;
Reporting Window -- Defined in Views in --> DBCreatePartition.sql;
Y -- Configured in --> dtConfig Table;

```

This diagram illustrates that DbCreatePartition.sql helps define the reporting window by creating views over the most recent partitions (e.g., TOP X), DbDeletePartition.sql handles the removal of old partitions, and the dtConfig table influences the target retention (Y). For the system to work correctly with extended retention, the TOP X limit in the view definitions (modified via DbCreatePartition.sql and potentially referenced by DbDeletePartition.sql) must align with or exceed the desired Y value from dtConfig.

Relevant Video Resource¶

While specific videos about modifying these exact ACS stored procedures might be rare due to their highly technical and potentially risky nature, understanding SCOM database maintenance and performance is crucial. The following video provides general insights into SCOM database considerations, which are relevant when planning for increased data retention:

(Note: Replace ‘YOUR_VIDEO_ID’ and the placeholder image source with an actual relevant YouTube video ID and its thumbnail URL found by searching for “SCOM database maintenance” or “Operations Manager database performance”. Ensure the video is publicly available and relevant to SCOM database administration in general, even if it doesn’t cover ACS specifics.)

For example, searching for “SCOM database maintenance” or “Operations Manager database” on YouTube might yield videos discussing SCOM database sizing, performance tuning, or general maintenance strategies that are applicable to the ACS database as well. Choose a video that appears professional and covers relevant database administration topics within the SCOM context.

Conclusion¶

Modifying the ACS data retention period in SCOM Operations Manager is a multi-step process that involves updating database configuration and adjusting stored procedures that manage data partitioning and reporting views. While updating the dtConfig table is the first step, correctly modifying DbCreatePartition.sql and DbDeletePartition.sql to remove or extend hardcoded limits (like the default 42-day window in view definitions) is equally important to ensure that the database retains data for the desired duration and that reports can access this historical information.

This change has significant implications for storage capacity and database performance. Thorough planning, capacity estimation, performance testing, and robust backup procedures are essential before implementing extended retention in a production environment. Ongoing monitoring and maintenance are also critical to ensure the long-term health and performance of the ACS database. Always approach modifications to core database scripts with caution, and back up everything before starting.

Have you attempted to modify ACS data retention in your SCOM environment? What challenges did you face, particularly regarding performance or storage? Share your experiences and insights in the comments below!