How to modify the number of days to retain data in the SystemCenterReporting database in Microsoft Operations Manager 2005

Article translations Article translations
Article ID: 887016 - View products that this article applies to.
Expand all | Collapse all

On This Page

INTRODUCTION

This article describes how to modify the number of days to retain data in the Microsoft Operations Manager (MOM) 2005 Reporting database that is named SystemCenterReporting.

MORE INFORMATION

The number of days to retain data in the MOM 2005 database that is named OnePoint can be modified in the Database Grooming properties that are in the Global Settings folder of the MOM 2005 Administrator Console.

However, there is no user interface to modify the number of days that data is retained in the SystemCenterReporting database. By default, the period to retain data in the SystemCenterReporting database is 395 days. For various reasons, the default value of 395 days may have to be changed in some installations.

When you install the MOM 2005 Reporting component, a stored procedure that is named p_updategroomdays is created in the SystemCenterReporting database. You can use this stored procedure to configure the number of days that data is retained in the SystemCenterReporting database.

The syntax of this stored procedure is as follows:
exec p_updategroomdays 'TableName', DaysToRetainData
In this example, TableName is one of the six main tables, and DaysToRetainData is the number of days to retain data in that table. The following six main tables in the SystemCenterReporting database represent a different type of data that is collected by MOM 2005:
  • SC_AlertFact_Table
  • SC_SampledNumericDataFact_Table
  • SC_EventParameterFact_Table
  • SC_AlertToEventFact_Table
  • SC_EventFact_Table
  • SC_AlertHistoryFact_Table

How to modify the number of days to retain data in the SystemCenterReporting database

To modify the number of days to retain data in the SystemCenterReporting database, follow these steps:
  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.
  2. In the SQL Server box, type the name of the Microsoft SQL Server where the SystemCenterReporting database is installed, type the logon credentials, and then click OK.
  3. On the toolbar, click SystemCenterReporting in the list of databases.
  4. In the Query - ServerName - Untitled1 pane, type the following command, click Query, and then click Execute:
    exec p_updategroomdays 'TableName', DaysToRetainData
    Important Replace TableName with the name of the table that you want to modify, and replace DaysToRetainData with the number of days to retain data for that table.

    For example, to retain data in the SC_AlertFact_Table for 300 days, use the following command:
    exec p_updategroomdays 'SC_AlertFact_Table', 300
  5. Repeat step 4 for each main table.

How to verify the number of days to retain data in the SystemCenterReporting database

To verify the number of days to retain data in the SystemCenterReporting database, follow these steps:
  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.
  2. In the SQL Server box, type the name of the Microsoft SQL Server where the SystemCenterReporting database is installed, type the logon credentials, and then click OK.
  3. On the toolbar, click SystemCenterReporting in the list of databases.
  4. In the Query - ServerName - Untitled1 pane, type the following command, click Query, and then click Execute:
    select cs.cs_tablename 'Table Name', wcs.wcs_groomdays 'Groom Days' from warehouseclassschema wcs
    join classschemas cs
    on cs.cs_classID = wcs.wcs_classID
    where cs.cs_tablename = 'TableName'
    and wcs.wcs_mustbegroomed = 1
    
    Note Replace TableName with one of the six main table names.

    For example, to verify the number of days that the SC_AlertFact_Table retains data, use the following command:
    select cs.cs_tablename 'Table Name', wcs.wcs_groomdays 'Groom Days' from warehouseclassschema wcs
    join classschemas cs
    on cs.cs_classID = wcs.wcs_classID
    where cs.cs_tablename = 'SC_AlertFact_Table'
    and wcs.wcs_mustbegroomed = 1
    
  5. Repeat step 4 for each main table

Properties

Article ID: 887016 - Last Review: June 14, 2007 - Revision: 2.3
APPLIES TO
  • Microsoft Operations Manager (MOM) 2005
Keywords: 
kbwinservperf kbmgmtservices kbhowto KB887016

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com