Microsoft KB Archive/887016

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

Article ID: 887016

Article Last Modified on 6/14/2007

-

APPLIES TO


 * Microsoft Operations Manager (MOM) 2005

-





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,  is one of the six main tables, and   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:  Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer. 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. On the toolbar, click SystemCenterReporting in the list of databases. In the Query -  - Untitled1 pane, type the following command, click Query, and then click Execute:

exec p_updategroomdays ' ',

Important Replace  with the name of the table that you want to modify, and replace   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

 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:  Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.</li> 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.</li> On the toolbar, click SystemCenterReporting in the list of databases.</li>  In the Query -  - 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  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 </li> Repeat step 4 for each main table</li></ol>

Additional query words: SCDWGroomJob

Keywords: kbwinservperf kbmgmtservices kbhowto KB887016

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.