none
Problem after install SP1 RRS feed

  • Question

  • Hi all
    Problems when upgrading to SP1 in test lab.
    I have two servers

    SM - Windows 2008R2 SP1, SQL 2008r2 SP2
    DW- Windows 2008R2 SP1, SQL 2008r2 SP2.

    I successfully installed SP1 on both servers.
    Data warehouse Jobs have been running, but some MPs don't sync (see below)

    Error in Operations Manager log on DW server:
    Имя журнала:   Operations Manager
    Источник:      DataAccessLayer
    Дата:          27.01.2013 1:25:17
    Код события:   33333
    Категория задачи:Отсутствует
    Уровень:       Предупреждение
    Ключевые слова:Классический
    Пользователь:  Н/Д
    Компьютер:     DW12.contoso.loc
    Описание:
    Data Access Layer rejected retry on SqlError:
     Request: Ral_ExecuteSql -- (statement=exec('IF OBJECT_ID(''[dbo].[WorkItemAffectedUserFact_2012_May]'') IS NULL
    BEGIN
      CREATE TABLE [dbo].[WorkItemAffectedUserFact_2...), (RETURN_VALUE=0)
     Class: 16
     Number: 1779
     Message: Table 'WorkItemAffectedUserFact_2012_May' already has a primary key defined on it.
    Xml события:
    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="DataAccessLayer" />
        <EventID Qualifiers="32768">33333</EventID>
        <Level>3</Level>
        <Task>0</Task>
        <Keywords>0x80000000000000</Keywords>
        <TimeCreated SystemTime="2013-01-26T19:25:17.000000000Z" />
        <EventRecordID>5617</EventRecordID>
        <Channel>Operations Manager</Channel>
        <Computer>DW12.contoso.loc</Computer>
        <Security />
      </System>
      <EventData>
        <Data>Ral_ExecuteSql -- (statement=exec('IF OBJECT_ID(''[dbo].[WorkItemAffectedUserFact_2012_May]'') IS NULL
    BEGIN
      CREATE TABLE [dbo].[WorkItemAffectedUserFact_2...), (RETURN_VALUE=0)</Data>
        <Data>16</Data>
        <Data>1779</Data>
        <Data>Table 'WorkItemAffectedUserFact_2012_May' already has a primary key defined on it.</Data>
      </EventData>
    </Event>

    How to fix this error?

    Monday, January 28, 2013 5:38 AM

Answers

  • In case it's missed in the other thread regarding this, here is what we did to resolve this:

    This is caused by a problem with the management packs trying to sync to the Data Warehouse.

    What's happening is that some of the data warehouse management packs are trying to redeploy after your upgrade to SP1.  As you've noticed, a handful of the management packs under the Data Warehouse wunderbar are in a "waiting" state, and the "System Center Data Warehouse Base Library" MP is in a "failed" state.  Looking at a DB trace, you can see that when the MP tries to sync, the T-SQL is querying to see if a "base" PK exists, finds that it does not, and tries to create it:

     IF

    OBJECT_ID(''''[PK_ConfigItemServicedByUserFact]'''') IS NULL
    BEGIN
      ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
      ALTER COLUMN [ConfigItemDimKey]   INT    NOT NULL ;
      ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
      ALTER COLUMN [ConfigItemServicedByUser_UserDimKey]   INT    NOT NULL ;
      ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
      ALTER COLUMN [DateKey]   INT    NOT NULL ;
      EXEC(''''ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
      ADD CONSTRAINT [PK_ConfigItemServicedByUserFact] PRIMARY KEY NONCLUSTERED ([ConfigItemDimKey], [ConfigItemServicedByUser_UserDimKey], [DateKey])'''');

    END

    That's where the error in the event log comes from.

    To recover from this:

    1. Create a backup of your DWDataMart, DWStagingAndConfig, and DWRepository DBs.

    2. For each primary key that shows up in your event logs with the message "Message: Table 'ConfigItemServicedByUserFact_2013_Jan' already has a primary key defined on it.", delete the primary key from the DWRepository DB.  For us there were 25 of them in total that were causing the "System Center Data Warehouse Base Library" MP to fail.  This can be done with a T-SQL statement such as the following:

    USE DWRepository ALTER TABLE dbo.BillableTimeHasWorkingUserFact_2013_Jan drop constraint [PK_BillableTimeHasWorkingUserFact_2013_Jan]

    Note that your fact table might be a different year and/or month than what we saw, but we saw this with the following 25 fact tables:

    'BillableTimeHasWorkingUserFact_2013_Jan'
    'ComputerHasPrimaryUserFact_2013_Jan'
    'ComputerHostsLogicalDiskFact_2013_Jan'
    'ComputerHostsNetworkAdapterFact_2013_Jan'
    'ComputerHostsOperatingSystemFact_2013_Jan'
    'ComputerHostsPhysicalDiskFact_2013_Jan'
    'ComputerHostsProcessorFact_2013_Jan'
    'ConfigItemGroupContainsEntitiesFact_2013_Jan'
    'ConfigItemImpactsCustomersFact_2013_Jan'
    'ConfigItemOwnedByUserFact_2013_Jan'
    'ConfigItemRelatesToConfigItemFact_2013_Jan'
    'ConfigItemServicedByUserFact_2013_Jan' -starting
    'GroupContainsConfigItemFact_2013_Jan'
    'ServiceContainsConfigItemFact_2013_Jan'
    'ServiceImpactsUserFact_2013_Jan'
    'WorkItemAboutConfigItemFact_2013_Jan'
    'WorkItemAffectedUserFact_2013_Jan'
    'WorkItemAssignedToUserFact_2013_Jan'
    'WorkItemCreatedByUserFact_2013_Jan'
    'WorkItemGroupContainsWorkItemFact_2013_Jan'
    'WorkItemHasBillableTimeFact_2013_Jan'
    'WorkItemHasParentWorkItemFact_2013_Jan'
    'WorkItemImpactsServiceFact_2013_Jan'
    'WorkItemRelatesToConfigItemFact_2013_Jan'
    'WorkItemRelatesToWorkItemFact_2013_Jan'

    3. Go to the management packs under the Data Warehouse wunderbar and click on the "redeploy" task on the failed MP.

    Once you delete the primary keys, the SQL call will be able to successfully create the new primary keys on the affected tables and the "System Center Data Warehouse Base Library" MP will now be imported, allowing the MPs in a "waiting" state to continue.  You will have additional primary keys that you will need to do the same with as well, as the MPs that were waiting will try to create their own primary keys.

    It is very important that you check your event logs for a listing to see which tables and primary keys are affected instead of relying on my list above, as you may have different months/years.  If you no longer have these events, you can click on the "redeploy" task on the failed MP to get a new set of events written to the logs.

    Friday, February 15, 2013 11:06 PM

All replies

  • Hi All,

    we have a similar Problem after installing SP1

    OM Log on DW Server

    ----------------------------

    Deployment related exception has been caught during ETL. This is either due to a failure in Deployment or we have exceeded the number of retry attempts. Work item will be marked as failed.

    Additional information:

    ETLModule: etlMod=NULL

    RetryAttempts: 0

    WorkItem: WorkItemId = 37310251, processModuleId = 8442, isDirty = True, BatchId = 93422, status = Failed, RetryCount = 0, ErrorCount = 88, TakenTime = 02/01/2013 11:35:27, Module = ModuleName = TransformIncidentResolutionCategory, ModuleType = System, ModuleDescription = Deployment Execution Step, ProcessCategoryName = Transform, ProcessName = Transform.Common, DeletedBatchId=0

    Exception details:

    Exception type: Microsoft.EnterpriseManagement.Common.ObjectNotFoundException

    Exception message: An object of class ManagementPackWarehouseModuleType with ID dfdfdfdfdfdfdfdfdfdfdfdfdf6c was not found.

    Stack trace:

    at Microsoft.EnterpriseManagement.DataWarehouseManagement.GetWarehouseModuleType(Guid id)

    at Microsoft.SystemCenter.Warehouse.Utility.Store.GetWarehouseModule(Guid id)

    at Microsoft.SystemCenter.Etl.ETLModule.GetModule(WorkItem etlWI)

    at Microsoft.SystemCenter.Etl.ETLModule.OnDataItem(DataItemBase dataItem, DataItemAcknowledgementCallback acknowledgedCallback, Object acknowledgedState, DataItemProcessingCompleteCallback completionCallback, Object completionState)

    ---------------------------

    Friday, February 1, 2013 12:13 PM
  • any news on this ??

    THX

    Markus

    Wednesday, February 13, 2013 2:56 PM
  • No news

    Wednesday, February 13, 2013 3:04 PM
  • We're also seeing the same thing.
    Thursday, February 14, 2013 10:03 PM
  • In case it's missed in the other thread regarding this, here is what we did to resolve this:

    This is caused by a problem with the management packs trying to sync to the Data Warehouse.

    What's happening is that some of the data warehouse management packs are trying to redeploy after your upgrade to SP1.  As you've noticed, a handful of the management packs under the Data Warehouse wunderbar are in a "waiting" state, and the "System Center Data Warehouse Base Library" MP is in a "failed" state.  Looking at a DB trace, you can see that when the MP tries to sync, the T-SQL is querying to see if a "base" PK exists, finds that it does not, and tries to create it:

     IF

    OBJECT_ID(''''[PK_ConfigItemServicedByUserFact]'''') IS NULL
    BEGIN
      ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
      ALTER COLUMN [ConfigItemDimKey]   INT    NOT NULL ;
      ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
      ALTER COLUMN [ConfigItemServicedByUser_UserDimKey]   INT    NOT NULL ;
      ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
      ALTER COLUMN [DateKey]   INT    NOT NULL ;
      EXEC(''''ALTER TABLE [dbo].[ConfigItemServicedByUserFact_2013_Jan]
      ADD CONSTRAINT [PK_ConfigItemServicedByUserFact] PRIMARY KEY NONCLUSTERED ([ConfigItemDimKey], [ConfigItemServicedByUser_UserDimKey], [DateKey])'''');

    END

    That's where the error in the event log comes from.

    To recover from this:

    1. Create a backup of your DWDataMart, DWStagingAndConfig, and DWRepository DBs.

    2. For each primary key that shows up in your event logs with the message "Message: Table 'ConfigItemServicedByUserFact_2013_Jan' already has a primary key defined on it.", delete the primary key from the DWRepository DB.  For us there were 25 of them in total that were causing the "System Center Data Warehouse Base Library" MP to fail.  This can be done with a T-SQL statement such as the following:

    USE DWRepository ALTER TABLE dbo.BillableTimeHasWorkingUserFact_2013_Jan drop constraint [PK_BillableTimeHasWorkingUserFact_2013_Jan]

    Note that your fact table might be a different year and/or month than what we saw, but we saw this with the following 25 fact tables:

    'BillableTimeHasWorkingUserFact_2013_Jan'
    'ComputerHasPrimaryUserFact_2013_Jan'
    'ComputerHostsLogicalDiskFact_2013_Jan'
    'ComputerHostsNetworkAdapterFact_2013_Jan'
    'ComputerHostsOperatingSystemFact_2013_Jan'
    'ComputerHostsPhysicalDiskFact_2013_Jan'
    'ComputerHostsProcessorFact_2013_Jan'
    'ConfigItemGroupContainsEntitiesFact_2013_Jan'
    'ConfigItemImpactsCustomersFact_2013_Jan'
    'ConfigItemOwnedByUserFact_2013_Jan'
    'ConfigItemRelatesToConfigItemFact_2013_Jan'
    'ConfigItemServicedByUserFact_2013_Jan' -starting
    'GroupContainsConfigItemFact_2013_Jan'
    'ServiceContainsConfigItemFact_2013_Jan'
    'ServiceImpactsUserFact_2013_Jan'
    'WorkItemAboutConfigItemFact_2013_Jan'
    'WorkItemAffectedUserFact_2013_Jan'
    'WorkItemAssignedToUserFact_2013_Jan'
    'WorkItemCreatedByUserFact_2013_Jan'
    'WorkItemGroupContainsWorkItemFact_2013_Jan'
    'WorkItemHasBillableTimeFact_2013_Jan'
    'WorkItemHasParentWorkItemFact_2013_Jan'
    'WorkItemImpactsServiceFact_2013_Jan'
    'WorkItemRelatesToConfigItemFact_2013_Jan'
    'WorkItemRelatesToWorkItemFact_2013_Jan'

    3. Go to the management packs under the Data Warehouse wunderbar and click on the "redeploy" task on the failed MP.

    Once you delete the primary keys, the SQL call will be able to successfully create the new primary keys on the affected tables and the "System Center Data Warehouse Base Library" MP will now be imported, allowing the MPs in a "waiting" state to continue.  You will have additional primary keys that you will need to do the same with as well, as the MPs that were waiting will try to create their own primary keys.

    It is very important that you check your event logs for a listing to see which tables and primary keys are affected instead of relying on my list above, as you may have different months/years.  If you no longer have these events, you can click on the "redeploy" task on the failed MP to get a new set of events written to the logs.

    Friday, February 15, 2013 11:06 PM
  • abarton425

    Your solution works
    thanks

    Sunday, February 17, 2013 7:00 PM
  • This worked for me as well, however, I needed to go through this process several times (delete primary keys, re-deploy the mp, look for new errors as the other mps are synced, delete primary keys, etc).  Ended up deleting ~70 primary keys.
    Friday, February 22, 2013 12:00 AM
  • This worked for me, but now our Load.Common job is failing.  On the DW server, I did Get-SCDWJobModule -JobName Load.Common and saw that there were 10 failed modules:

    ID          Name

    2476      LoadSMDWDataMartComputerHostsProcessorFact

    2511      LoadSMDWDataMartWorkItemCreatedByUserFact

    2510      LoadSMDWDataMartWorkItemAssignedToUserFact

    2516      LoadSMDWDataMartWorkItemHasParentWorkItemFact

    2515      LoadSMDWDataMartWorkItemHasBillableTimeFact

    2509      LoadSMDWDataMartWorkItemAffectedUserFact

    2485      LoadSMDWDataMartConfigItemServicedByUserFact

    2484      LoadSMDWDataMartConfigItemRelatesToConfigItem

    2508      LoadSMDWDataMartWorkItemAboutConfigItemFact

    2491      LoadSMDWDataMartGroupContainsConfigItemFact

    The Event Log on the DW Server for these failed items look like this  (Event ID 33503):

    An error countered while attempting to execute ETL Module:
     ETL process type: Load
     Batch ID: 200825
     Module name: LoadSMDWDataMartGroupContainsConfigItemFact
     Message: UNION ALL view 'SMDWDataMart.dbo.GroupContainsConfigItemFactvw' is not updatable because a primary key was not found on table '[SMDWDataMart].[dbo].[GroupContainsConfigItemFact_2013_Apr]'.
    
     Stack:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at Microsoft.SystemCenter.Warehouse.Utility.SqlBulkOperation.PartitionedViewUpdate(SqlConnection sourceConnection, String sourceQuery, String destinationTable, Dictionary`2 mapping, SqlConnection destinationConnection, Collection`1 pkColumns)
       at Microsoft.SystemCenter.Warehouse.Utility.SqlBulkOperation.PartitionedViewUpsert(String sourceConnectionString, String sourceQuery, String destinationTable, Dictionary`2 mapping, String destinationConnectionString, Collection`1 pkColumns, Int32& insertCount, Int32& updateCount, DomainUser sourceSecureUser, DomainUser destSecureUser, SqlResourceStore targetStore)
       at Microsoft.SystemCenter.Warehouse.Etl.ADOInterface.PartitionedViewUpsert(DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
       at Microsoft.SystemCenter.Warehouse.Etl.ADOInterface.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
       at Microsoft.SystemCenter.Warehouse.Etl.LoadModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser, Int32 loadBatchSize)
       at Microsoft.SystemCenter.Warehouse.Etl.LoadModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
       at Microsoft.SystemCenter.Etl.ETLModule.OnDataItem(DataItemBase dataItem, DataItemAcknowledgementCallback acknowledgedCallback, Object acknowledgedState, DataItemProcessingCompleteCallback completionCallback, Object completionState)

    Any thoughts?

    Tuesday, June 4, 2013 9:28 PM
  • Hi

    we have tried a lot of things without succuess

     

    http://dougsigmon.wordpress.com/2012/09/20/dwmaintenance-stalled-processes-kill-the-scsm-2012-data-warehouse/

    http://ariessysadmin.blogspot.com.es/2012/11/scsm-2012-data-warehouse-cube.html

    http://blogs.technet.com/b/servicemanager/archive/2010/06/07/troubleshooting-the-data-warehouse-an-overview.aspx

    Nothing was working. We have opened a Support call at MS, finally they came back with the Solution to reinstall the DW. What we have done. Then the same Problem occurs again. We have added some more CPUs and increased the Memory to 32 GB on the DB Server. We have also changed the Settings for the Max used CPU's while a query is running in SSAS. It seesm that this Problem is based on a SQL Server Perfromance Problem. However strange to believe. We have running currently the DW and operational Database on the same instance. Hope it will be working now more than 4 weeks..;-)

    Thinkt the next step will be to seperate the DW component from the operational DB, I mean a complete new Server fro SQL were the DW is then running. It seems also that this Problem can happend If SQL Standard is used.

    However .... very very strange ....

    rgds,

    markus

    Wednesday, June 5, 2013 4:33 AM
  • Please try this.

    http://www.scsm.se/?p=881

    Step 1 – Disable the Cube Processing jobs

    Step 2 – Manual processing of the cubes

    Step 3 – Re-enable the Cube Processing jobs in SCSM

    or

    upgrade to SP1's UR2 ASAP


    Kirpal Singh

    Thursday, July 25, 2013 2:32 PM
  • Did you ever find a solution to your primary key was not found on table error?  We are now seeing it in our Production enviroment?

    Tuesday, August 6, 2013 3:09 PM
  • Hi,

    In our prod env scsm 2012 R2  ,reports are not updated and the data in DWdatamart is not updated

    the load.common job is failing with the below error

    a
    ETL Module Execution failed:
     ETL process type: Load
     Batch ID: 120162
     Module name: LoadDWDataMartIncidentTierQueueDurationFact
     Message: UNION ALL view 'DWDataMart.dbo.IncidentTierQueueDurationFactvw' is not updatable because a primary key was not found on table '[DWDataMart].[dbo].[IncidentTierQueueDurationFact_2014_Oct]'.

    this is repeated for 5 tables

    in our case we didnt do any upgrade from sp1 to 2012 r2

    Any solution ???

    Monday, March 9, 2015 12:26 PM
  • 2. For each primary key that shows up in your event logs with the message "Message: Table 'ConfigItemServicedByUserFact_2013_Jan' already has a primary key defined on it.", delete the primary key from the DWRepository DB.  For us there were 25 of them in total that were causing the "System Center Data Warehouse Base Library" MP to fail.  This can be done with a T-SQL statement such as the following:

    Look at the even logs carefully (specifically the MP Element ID), some of these issues can also be on the DWDataMart DB, and the script needs to be adjusted accordingly. Easily missed if your looking just at the table. 

    Example event log:

    MP Element ID: WorkItemAboutConfigItemFact_DWDataMart

    MP name: Microsoft.SystemCenter.Datawarehouse.Base

    MP version: 7.5.2905.0

    Operation: Update

    Error message: Table 'WorkItemAboutConfigItemFact_2018_Oct' already has a primary key defined on it.

    Could not create constraint. See previous errors.

    Thursday, November 1, 2018 2:33 PM