MOM 2005 Reporting – Grooming and Shrinking the Reporting database

How to Groom and Shrink a Reporting database

We had a MOM 2005 reporting database which had not been archiving correctly and had grown to the point that there wasn’t enough free disk space to run the grooming process, due to increases in the size of the log files. Based on business requirements and hardware constraints the decision was made to trim down the content of the reporting database down to 35 days from what was originally data that was more than 500 days old. The original database size was approximately 55 GB with no free space, after successful grooming it was moved down to .5 GB (prior to re-activating the DTS package to transfer from the Operations database to the reporting database).

To accomplish this, we took the following high level process (all scripts referred to are included in full below):

  1. Identify the count of each of the 6 fact tables using the "Table Count" script, and keep track of those statistics going forward.
  2. Identify the oldest data in the tables using the "Oldest Date" script.
  3. Determine the date difference between the oldest date and the current date with the "Date Diff" script.
  4. Set the grooming interval to higher than the highest date using the "Set Grooming" script. This is done to ensure the grooming process will work as long as there is no data that needs to be groomed.
  5. Verify the change tot he grooming settings with the "Check Grooming" script.
  6. Start the reporting grooming job (called SCDWGroomJob) in the SQL Agent jobs section of the SQL 2000 Enterprise Manager.
  7. When the job (hopefully!) succeeds, run the "Truncate Log" script to free up some of the transaction log space.
  8. Change the grooming setting to slightly less than the highest difference so that the next grooming job will be configured to actually groom out data.
  9. Re-run steps 5 though 8 for the new setting.
  10. Continue this process with decreasing values (the oldest data in the database we were working through was 508 days!). For that particular environment we were grooming settings which decreased from: 700, 500, 400, 350, 275, 200, 125, 35.
  11. Finally we were able to decrease the size of the reporting database through the "Shrinkfile" script.
  12. Last, w reactivated the scheduled task to move data from the Operations database to the Reporting database and started growing the data again; but this time up to a maximum of 35 days.

Fact Table Statistics:             700 Days         400 Days         350 Days         275 Days

AlertFact                                          715                  715                 715                  715
AlertHistoryFact                            32,184             27,919             21,481             15,748
AlertToEventFact                          38,969             34,123             27,609             21,151
EventFact                                4,415,065         4,291,870        4,079,922         3,862,984
EventParameterFact                    828,708           828,708            828,708            828,708
SampledNumericDataFact      152,648,971     138,060,142     121,863,087       98,431,535

Fact Table Statistics:             200 Days         125 Days          35 Days

AlertFact                                          715                  715                  26
AlertHistoryFact                              9,234                3,051                   1
AlertToEventFact                          17,230                8,467                   5
EventFact                                3,752,214          3,022,275             3,625
EventParameterFact                    828,708             828,708             3,315
SampledNumericDataFact        87,401,639        47,819,874          413,745

Scripts

Check Grooming

This script checks the current state of the grooming settings.
 
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
 
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_AlertHistoryFact_Table’
And wcs.wcs_mustbegroomed = 1
 
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_AlertToEventFact_Table’
And wcs.wcs_mustbegroomed = 1
 
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_EventFact_Table’
And wcs.wcs_mustbegroomed = 1
 
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_EventParameterFact_Table’
And wcs.wcs_mustbegroomed = 1
 
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_SampledNumericDataFact_Table’
And wcs.wcs_mustbegroomed = 1
 

Set Grooming

Set the grooming date on each of the six fact tables. The last number will vary depending on what the grooming interval needs to be configured for.
 
Exec p_updategroomdays SC_AlertFact_Table, 700
Exec p_updategroomdays SC_AlertHistoryFact_Table, 700
Exec p_updategroomdays SC_AlertToEventFact_Table, 700
Exec p_updategroomdays SC_EventFact_Table, 700
Exec p_updategroomdays SC_EventParameterFact_Table, 700
Exec p_updategroomdays SC_SampledNumericDataFact_Table, 700
 

Table Count

Identify the total number of records in each of the fact tables for later comparison.
 
select count(*) from SC_AlertFact_Table
select count(*) from SC_AlertHistoryFact_Table
select count(*) from SC_AlertToEventFact_Table
select count(*) from SC_EventFact_Table
select count(*) from SC_EventParameterFact_Table
select count(*) from SC_SampledNumericDataFact_Table
 

Oldest Date

Identified the oldest data in the database through the following queries: (very slow to run).
 
SET ROWCOUNT 2
select DateTimeLastModified from SC_AlertFact_Table order by DateTimeLastModified
select DateTimeLastModified from SC_AlertHistoryFact_Table order by DateTimeLastModified
select DateTimeAlertAdded from SC_AlertToEventFact_Table order by DateTimeAlertAdded
select DateTimeGenerated from SC_EventFact_Table order by DateTimeGenerated
select DateTimeEventStored from SC_EventParameterFact_Table order by DateTimeEventStored
select DateTimeAdded from SC_SampledNumericDataFact_Table order by DateTimeAdded
 

Date Diff

Based upon the results of the oldest records, identify the oldest date in the database through using a difference in dates (the first date is the oldest date found in the fact tables)
 
select datediff (day, ‘2005-10-27’, getdate())

Truncate Log

To clear out the SystemCenter Reporting log files.

BACKUP LOG SystemCenterReporting with TRUNCATE_ONLY

 

Shrinkfile

To shrink the reporting database.
 
dbcc shrinkfile(REPLOG,2)
BACKUP LOG SystemCenterReporting WITH TRUNCATE_ONLY
dbcc shrinkfile(REPDATA,2) 

Advertisements
This entry was posted in MOM 2005. Bookmark the permalink.

2 Responses to MOM 2005 Reporting – Grooming and Shrinking the Reporting database

  1. Zbig says:

    SELECT cs.cs_tablename ‘Table Name’,
    ‘Table Name’, replace with one of 6 table names 😉

  2. Zbig says:

    example of working query
    select cs.cs_tablename ‘SC_AlertFact_Table’, 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s