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

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