大网数据广告活动
启动与系统组件

ACS reports return no more than 42 days of data

· 52CE 我爱测

Fixes an issue in which Audit Collection Services reports return no more than 42 days of data in System Center Operations Manager.

This article helps you fix an issue in which Audit Collection Services (ACS) reports return no more than 42 days of data in System Center Operations Manager.

Original product version:   Microsoft System Center 2012 Operations Manager, System Center 2012 R2 Operations Manager
Original KB number:   2663919

Symptoms

When using System Center Operations Manager, the audit database data retention period is set to 100 days but ACS reports return no more than 42 days of data.

Cause

The ACS collector service uses DbCreatePartition.sql to create the partition tables and uses DbDeletePartition.sql (C:\Windows\System32\Security\AdtServer on the collector server) to delete the partition tables based on the retention period. It also creates the views AdtServer.dvall, AdtServer.dvall5, and AdtServer.dvheader. In DbCreatePartition.sql and DbDeletePartition.sql, the dvall, dvall5, and dvheader views use a union of only the top 42 partition tables.

Resolution

To resolve this issue, complete the following steps:

  1. First create an ACS data retention period as per your requirements. To update the data retention period, run the following SQL query:

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

    For example, to retain 7 days of data, set Value = 8. Data is accumulated at approximately 7.6 MB per day per workstation.

    [!NOTE] Microsoft SQL Server 2005 has a limitation that allows only 255 partition tables in a view. Don't set the value more than 255. The higher the value, the longer it can take to fetch the data in the reports, thus performance may be affected.

  2. On the collector server, navigate to C:\Windows\System32\Security\AdtServer. Edit the DbCreatePartition.sql stored procedure in Notepad. Increase the days to 100 (or as per your requirements) by replacing 42 in the fifth line of for AdtServer.dvall, AdtServer.dvall5, and AdtServer.dvheader as shown below:

    /*************************************************
    *
    * 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 42 PartitionId from dtPartition order by PartitionCloseTime desc
    
    /***************************************************
    *
    * 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 42 PartitionId from dtPartition order by PartitionCloseTime desc
    
    /****************************************************
    *
    * 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 42 PartitionId from dtPartition order by PartitionCloseTime desc
    
  3. On the collector server, navigate to C:\Windows\System32\Security\AdtServer. Edit the DbDeletePartition.sql stored procedure in Notepad. Increase the days to 100 (or as per your requirements) by replacing 42 in the fifth line of AdtServer.dvall, AdtServer.dvall5, and AdtServer.dvheader as shown below:

    /*****************************************************
    *
    * 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 42 PartitionId from dtPartition order by PartitionCloseTime desc
    
    /******************************************************
    *
    * 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 42 PartitionId from dtPartition order by PartitionCloseTime desc
    
    /******************************************************
    *
    * 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 42 PartitionId from dtPartition order by PartitionCloseTime desc
    
  4. Restart the Operations Manager Audit Collection Service on the collector server.

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 04/15/2024
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。