DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 2853442 - Last Review: June 6, 2013 - Revision: 1.0

Symptoms

After upgrading the data warehouse to System Center 2012 Service Manager Service Pack 1 (SP1), if the data warehouse has been running for more than 3 months then Management Pack (MP) Sync jobs may fail. Additionally, transform and load jobs may also fail. The Data Warehouse Base Library MP will show a Deployment Status of “Failed” in the console and many others that are dependent on this MP will show a status of “Waiting”.

If you have not upgraded the data warehouse to SP1 and this problem does not yet affect you, see Scenario 1 in the Resolution section.

Cause

This error can occur due to erroneous database grooming.

Resolution

Scenario 1: Upgrade to SP1 has not happened yet.

If the upgrade has not yet occurred, run the following query on DWRepository to get the actual SQL scripts that are to drop and add constraint of the primary key of fact tables on DWRepository database.

;WITH FactName
AS (
       select w.WarehouseEntityName from etl.WarehouseEntity w
       join etl.WarehouseEntityType t on w.WarehouseEntityTypeId = t.WarehouseEntityTypeId
       where t.WarehouseEntityTypeName = 'Fact'
),FactList
AS (
    SELECT  PartitionName, p.WarehouseEntityName,
            RANK() OVER ( PARTITION BY p.WarehouseEntityName ORDER BY PartitionName ASC ) AS RK
    FROM    etl.TablePartition p
       join FactName f on p.WarehouseEntityName = f.WarehouseEntityName
)
, FactPKList
AS (
    SELECT  f.WarehouseEntityName, a.TABLE_NAME, a.COLUMN_NAME, b.CONSTRAINT_NAME, f.RK,
            CASE WHEN b.CONSTRAINT_NAME = 'PK_' + f.WarehouseEntityName THEN 1 ELSE 0 END AS DefaultConstraints
    FROM    FactList f
    JOIN    INFORMATION_SCHEMA.KEY_COLUMN_USAGE a ON f.PartitionName = a.TABLE_NAME
    JOIN    INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME AND b.CONSTRAINT_TYPE = 'Primary key'
)
, FactWithoutDefaultConstraints
AS (
    SELECT  a.*
    FROM    FactPKList a
    LEFT JOIN FactPKList b ON b.WarehouseEntityName = a.WarehouseEntityName AND b.DefaultConstraints = 1
    WHERE   b.WarehouseEntityName IS NULL AND a.RK = 1
)
, FactPKListStr
AS (
    SELECT  DISTINCT f1.WarehouseEntityName, f1.TABLE_NAME, f1.CONSTRAINT_NAME, F.COLUMN_NAME AS PKList
    FROM    FactWithoutDefaultConstraints f1
    CROSS APPLY (
                    SELECT  '[' + COLUMN_NAME + '],'
                    FROM    FactWithoutDefaultConstraints f2
                    WHERE   f2.TABLE_NAME = f1.TABLE_NAME
                    ORDER BY COLUMN_NAME
                FOR
                   XML PATH('')
                ) AS F (COLUMN_NAME)
)
SELECT  'ALTER TABLE [dbo].[' + f.TABLE_NAME + '] DROP CONSTRAINT [' + f.CONSTRAINT_NAME + ']' + CHAR(13) + CHAR(10) +
        'ALTER TABLE [dbo].[' + f.TABLE_NAME + '] ADD CONSTRAINT [PK_' + f.WarehouseEntityName + '] PRIMARY KEY NONCLUSTERED (' + SUBSTRING(f.PKList, 1, LEN(f.PKList) -1) + ')' + CHAR(13) + CHAR(10)
FROM    FactPKListStr f
ORDER BY f.WarehouseEntityName

Note: After the first query is run the output will be another set of queries that need to be run. Copy the results into new query windows and run all of them.

After the default primary keys have been restored, restart the failed base MP deployment from the Service Manager Console.


Scenario 2: Upgrade to SP1 has occurred without a transform/load job failure.

If you have upgraded your system to SP1 and only observed the MP deployment failure and not a transform/load job failure, you can simply apply the Scenario 1 resolution steps.

Scenario 3: Upgrade to SP1 has happened with a transform/load job failure.

If you have upgraded your system to SP1 and have seen the transform / load job failure, please check in the DWStagingAndConfig to see whether SystemDerivedMp.Microsoft.SystemCenter.Datawarehouse.Base exists or not: run this query on DWStagingAndConfig:

select * from ManagementPack where mpname like '%SystemDerivedMp.Microsoft.SystemCenter.Datawarehouse.Base%'

Most likely the above MP is missing. If so then you will need to restore your database backups to before the upgrade. To do so, please follow the steps below:

1. Perform disaster recovery for the db backups. 

2. Disable the MPSyncJob Schedule.

3. Restore all of the missing Primary Keys in the DWRepository using the SQL script found in Scenario 1.

4. Restart the failed base MP deployment from the console.
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use (http://go.microsoft.com/fwlink/?LinkId=151500) for other considerations.

Applies to
  • Microsoft System Center 2012 Service Manager Service Pack 1
  • Microsoft System Center 2012 Service Manager
Keywords: 
KB2853442
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support