All posts by Jonathan Almquist

How to perform an offline defrag of the Health Service Store

 

Introduction

Periodically, the Health Service Store may become badly fragmented. There is a rule in SCOM that performs a daily defrag of this database file, but it’s an online defrag and doesn’t always do the trick if there are frequent instance space changes occurring.

Performing the Defrag

On the Root Management Server, perform the following steps.

1. Open command propmpt and navigate to “%Program Files%\System Center Operations Manager 2007\Health Service state\health service store“.

2. Type net stop HealthService

a. If service does not shutdown clean, see recoverybelow before continuing.

3. Type esentutl /d HealthServiceStore.edb

4. Type net start HealthService

Recovery

If Health Service incurs a dirty shutdown, replay transaction log before continuing with defragmentation.

1. Type esentutl /r <HealthServiceStoreFile>.edb

a. If recovery doesn’t work, try esentutl /p <HealthServiceStoreFile>.edb

2. Continue with step 3 above

Desired data warehouse retention settings–in my opinion

 

clip_image002

Check retention settings for all core datasets

/*

Check retention setting for common data types

*/

USE OperationsManagerDW

SELECT DS.DatasetDefaultName AS ‘Dataset’, SDA.MaxDataAgeDays AS ‘Days’, SDA.AggregationTypeId AS ‘Type’,

SDA.BuildAggregationStoredProcedureName AS ‘Agg SP’, SDA.AggregationIntervalDurationMinutes AS ‘Agg Min’,

SDA.GroomStoredProcedureName AS ‘Groom SP’, SDA.GroomingIntervalMinutes AS ‘Groom Min’, SDA.MaxRowsToGroom AS ‘Groom max rows’,

SDA.LastGroomingDateTime AS ‘Last groomed’

FROM Dataset AS DS INNER JOIN

StandardDatasetAggregation AS SDA ON DS.DatasetId = SDA.DatasetId

WHERE (DS.DatasetDefaultName NOT LIKE ‘%Microsoft%’)

ORDER BY ‘Dataset’

Update retention settings for Alert Dataset (400 to 90)

/*

Update retention settings for selected types

*/

DECLARE @Type AS INT, @Dataset AS VARCHAR(max), @DaysToKeep AS INT

SET @Type = 0 /* Raw=0, Hourly=20, Daily=30 */

SET @Dataset = ‘Alert data set’

SET @DaysToKeep = 90

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = @DaysToKeep

FROM Dataset INNER JOIN

StandardDatasetAggregation ON Dataset.DatasetId = StandardDatasetAggregation.DatasetId

WHERE (Dataset.DatasetDefaultName = @Dataset) AND (StandardDatasetAggregation.AggregationTypeId = @Type)

Update retention settings for Event Dataset (100 to 30)

/*

Update retention settings for selected types

*/

DECLARE @Type AS INT, @Dataset AS VARCHAR(max), @DaysToKeep AS INT

SET @Type = 0 /* Raw=0, Hourly=20, Daily=30 */

SET @Dataset = ‘Event data set’

SET @DaysToKeep = 30

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = @DaysToKeep

FROM Dataset INNER JOIN

StandardDatasetAggregation ON Dataset.DatasetId = StandardDatasetAggregation.DatasetId

WHERE (Dataset.DatasetDefaultName = @Dataset) AND (StandardDatasetAggregation.AggregationTypeId = @Type)

Update retention settings for State RAW Dataset (180 to 30)

/*

Update retention settings for selected types

*/

DECLARE @Type AS INT, @Dataset AS VARCHAR(max), @DaysToKeep AS INT

SET @Type = 0 /* Raw=0, Hourly=20, Daily=30 */

SET @Dataset = ‘State data set’

SET @DaysToKeep = 30

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = @DaysToKeep

FROM Dataset INNER JOIN

StandardDatasetAggregation ON Dataset.DatasetId = StandardDatasetAggregation.DatasetId

WHERE (Dataset.DatasetDefaultName = @Dataset) AND (StandardDatasetAggregation.AggregationTypeId = @Type)

Update retention settings for State HOURLY Dataset (400 to 90)

/*

Update retention settings for selected types

*/

DECLARE @Type AS INT, @Dataset AS VARCHAR(max), @DaysToKeep AS INT

SET @Type = 20 /* Raw=0, Hourly=20, Daily=30 */

SET @Dataset = ‘State data set’

SET @DaysToKeep = 90

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = @DaysToKeep

FROM Dataset INNER JOIN

StandardDatasetAggregation ON Dataset.DatasetId = StandardDatasetAggregation.DatasetId

WHERE (Dataset.DatasetDefaultName = @Dataset) AND (StandardDatasetAggregation.AggregationTypeId = @Type)

Update retention settings for State DAILY Dataset (400 to 90)

/*

Update retention settings for selected types

*/

DECLARE @Type AS INT, @Dataset AS VARCHAR(max), @DaysToKeep AS INT

SET @Type = 30 /* Raw=0, Hourly=20, Daily=30 */

SET @Dataset = ‘State data set’

SET @DaysToKeep = 90

UPDATE StandardDatasetAggregation

SET MaxDataAgeDays = @DaysToKeep

FROM Dataset INNER JOIN

StandardDatasetAggregation ON Dataset.DatasetId = StandardDatasetAggregation.DatasetId

WHERE (Dataset.DatasetDefaultName = @Dataset) AND (StandardDatasetAggregation.AggregationTypeId = @Type)

Default Management Pack Clean-up steps for UI deleted AD Integration Rules (uncommon scenario–do not try without MSFT support)

1. Export the Default MP.

2. Find the Rules and DisplayStrings sections relating to the deleted AD Integration rules, and delete everything highlighted in yellow (below example) for each instance found.

3. Save and Import cleaned Default MP.

4. Delete associated security groups in Active Directory.

  <Monitoring>

    <Rules>

<Rule ID=”OPSMGRLAB_omms_1_opsmgrlab.com” Enabled=”true” Target=”SC!Microsoft.SystemCenter.RootManagementServer” ConfirmDelivery=”false” Remotable=”true” Priority=”Normal” DiscardLevel=”100″>

        <Category>Maintenance</Category>

        <DataSources>

          <DataSource ID=”DS1″ TypeID=”Windows!Microsoft.Windows.LdapProvider”>

            <Domain>opsmgrlab.com</Domain>

            <Query>(&amp;(sAMAccountType=805306369)(objectCategory=computer)(cn=admin*))</Query>

            <Params>

              <Param>distinguishedName</Param>

              <Param>dNSHostName</Param>

            </Params>

            <UserAndDomain />

            <Password />

            <Frequency>3600</Frequency>

          </DataSource>

        </DataSources>

        <WriteActions>

          <WriteAction ID=”WA1″ TypeID=”SC!Microsoft.SystemCenter.ADWriter”>

            <ManagementServerName>72f2e063-3332-7c1b-b393-e8cf2d83545d</ManagementServerName>

            <Domain>opsmgrlab.com</Domain>

            <UserAndDomain />

            <Password />

            <SecureReferenceId />

            <dNSXPath>DataItem/Property[@Name=’dNSHostName’]</dNSXPath>

            <distinguishedNameXPath>DataItem/Property[@Name=’distinguishedName’]</distinguishedNameXPath>

            <FailoverList>

              <ManagementServerGuid>bd1b6aed-27d3-71ec-dd34-66b9f7818c90</ManagementServerGuid>

              <ManagementServerGuid>696e6c4c-c80d-6e61-5dc2-7ab6aff975f0</ManagementServerGuid>

            </FailoverList>

          </WriteAction>

        </WriteActions>

      </Rule>

    </Rules>

Also the Display Strings associated

<DisplayString ElementID=”OPSMGRLAB_omms_1_opsmgrlab.com”>

          <Name>AD rule for Domain: opsmgrlab.com, ManagementServer: OPSMGRLAB\omms-1</Name>

          <Description>AD agent assignment rule for Domain: opsmgrlab.com, ManagementServer: OPSMGRLAB\omms-1</Description>

        </DisplayString>