Support Tip: Upgrade to Operations Manager 2012 R2 may result in Data Warehouse synchronization failures

Support Tip: Upgrade to Operations Manager 2012 R2 may result in Data Warehouse synchronization failures

  • Comments 8
  • Likes

~ Brian McDermott

ToolsHi everyone, Brian McDermott here with another Operations Manager support tip for you. If you have upgraded to System Center 2012 R2 Operations Manager (OpsMgr 2012 R2) from System Center 2012 Operations Manager Service Pack 1 (OpsMgr 2012 SP1) you may receive the following error:

Log Name:      Operations Manager
Source:        Health Service Modules
Event ID:      31565
Task Category: Data Warehouse
Level:         Error
Keywords:      Classic
User:          N/A
Failed to deploy Data Warehouse component. The operation will be retried.
Exception 'DeploymentException': Failed to perform Data Warehouse component deployment operation: Install; Component: DataSet, Id: '0d698dff-9b7e-24d1-8a74-4657b86a59f8', Management Pack Version-dependent Id: '29a3dd22-8645-bae5-e255-9b56bf0b12a8'; Target: DataSet, Id: '23ee52b1-51fb-469b-ab18-e6b4be37ab35'. Batch ordinal: 3; Exception: Sql execution failed. Error 207, Level 16, State 1, Procedure vAlertDetail, Line 18, Message: Invalid column name 'TfsWorkItemId'.

One or more workflows were affected by this. 

Workflow name: Microsoft.SystemCenter.DataWarehouse.Deployment.Component
Instance name: 05910f88-cefb-4471-bd86-c6ddee871293
Instance ID: {750D388B-0CF2-358A-02B0-E528CF813478}
Management group: OMMG

The reason this is happening is that we have updated the Alert tables to add a couple of columns to better enable TFS integration, and unfortunately in some situations the upgrade code that adds these columns to the Data Warehouse tables is failing to complete successfully.

So if you are seeing the errors above when logged on one of your management servers, which, VERY SPECIFICALLY has the following in the description: Message: Invalid column name 'TfsWorkItemId', then you should run the SQL statement below to add those columns to the tables that are missing them.

First, since we are directly editing the database it is absolutely essential that we begin by taking backups of both of your Operations Manager databases. Yes, both the OperationsManager DB and the OperationsManagerDW DB. Recovery of the databases can sometime require both of them so prior to any important change always backup both of them.

Also please note that the Event ID 31565 noted above is a very generic error and you should only run the SQL below if the description identifies that it is the problem with the TFSWorkItemID column. If there is a different description for the 31565 event and you need further assistance in troubleshooting it, or also if you have encountered this problem but are uncertain on how to follow the recovery steps, then please open a case with Microsoft Support and we will be glad to assist.

The Recovery Steps

On your SQL server hosting the Operations Manager Data Warehouse DB, open up SQL Server Management Studio. Connect to the Data Warehouse DB, then paste in the following code and run it to correct the problem.


The code is listed here:

USE OperationsManagerDW
SELECT @GuidString = DatasetId FROM StandardDataset
WHERE SchemaName = 'Alert'
-- update all tables that were already created
   @StandardDatasetTableMapRowId int
  ,@Statement nvarchar(max)
  ,@SchemaName sysname
  ,@TableNameSuffix sysname
  ,@BaseTableName sysname
  ,@FullTableName sysname
SET @StandardDatasetTableMapRowId = 0
              FROM StandardDatasetTableMap tm
              WHERE (tm.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
                AND (tm.DatasetId = @GuidString)
     @StandardDatasetTableMapRowId = tm.StandardDatasetTableMapRowId
    ,@SchemaName = sd.SchemaName
    ,@TableNameSuffix = tm.TableNameSuffix
    ,@BaseTableName = sdas.BaseTableName
  FROM StandardDatasetTableMap tm
          JOIN StandardDataset sd ON (tm.DatasetId = sd.DatasetId)
          JOIN StandardDatasetAggregationStorage sdas ON (sdas.DatasetId = tm.DatasetId) AND (sdas.AggregationTypeId = tm.AggregationTypeId)
  WHERE (tm.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
    AND (tm.DatasetId = @GUIDString)
    AND (sdas.TableTag = 'detail')
    AND (sdas.DependentTableInd = 1)
  ORDER BY tm.StandardDatasetTableMapRowId
  SET @FullTableName = @BaseTableName + '_' + @TableNameSuffix
    AND COLUMN_NAME = N'TfsWorkItemId')
    SET @Statement = 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@FullTableName) + ' ADD TfsWorkItemId nvarchar(256) NULL'
    EXECUTE (@Statement)
    AND COLUMN_NAME = N'TfsWorkItemOwner')
    SET @Statement = 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@FullTableName) + ' ADD TfsWorkItemOwner nvarchar(256) NULL'
    EXECUTE (@Statement)
-- alter cover views
EXEC StandardDatasetBuildCoverView @GUIDString, 0

Hope this helps!

Brian McDermott | Escalation Engineer | Microsoft CTS Management and Security Division

Get the latest System Center news on Facebook and Twitter:

clip_image001 clip_image002

System Center All Up:
System Center – Configuration Manager Support Team blog:
System Center – Data Protection Manager Team blog:
System Center – Orchestrator Support Team blog:
System Center – Operations Manager Team blog:
System Center – Service Manager Team blog:
System Center – Virtual Machine Manager Team blog:

Windows Intune:
WSUS Support Team blog:
The AD RMS blog:

App-V Team blog:
MED-V Team blog:
Server App-V Team blog:

The Forefront Endpoint Protection blog :
The Forefront Identity Manager blog :
The Forefront TMG blog:
The Forefront UAG blog:

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Do this apply when uphrading from scom 2007 r2 to scom 2012? Have seen simial errors after relocating operationsmanager db

  • Hi Avdo, this error and resolution is very specifically for the upgrade to OpsMgr 2012 R2 from OpsMgr 2012, as that is when we add the TFSWorkitemID. The Event ID 31565 is a very generic event so if you see it in other situations you need to refer to the Event details to understand the cause.

  • There is also another element to this problem that may result in 31552 timeout events for Microsoft.SystemCenter.DataWarehouse.Synchronization.Configuration workflows which will stop new MP's getting to the data warehouse and new reports being deployed. Every time the 31565 event shown above fails due to the Invalid column a new row is written to the Domaintable table in the data warehouse as the alertstage tables system object reference changes. The domaintable table should (at time of writing) contain 120 rows but depending on how quickly the above issue is resolved this table can become quite large.

    The stored procedure that is called by the Synchronization.Configuration workflow executes an UPDATE STATISTICS iteratively against all tables that are referenced in this table. If the number of rows becomes too large the workflow will time out and you will see blocking in the db with a spid running UPDATE STATISCTICS for long periods of time. This can have a seriously negative impact on the performance of the data warehouse.

    The following SQL can identify the problem, if everything is fine then it should return 120 rows:

    Select count (*) from domaintable

    If it's more than 120 rows then it's likely that you've been affected by this issue. You can correct the problem by running the following statement (all the usual caveats apply, make sure you have a backup, mileage may vary etc. etc.). This query will remove any rows in the table that are called alertstage but aren't referenced in the sys.sysobjects table meaning that the rows are invalid.

    Delete from domaintable where tablename = ‘alertstage’ AND domaintablerowid NOT IN (select domaintablerowid from DomainTable dt join sys.sysobjects so ON dt.TableObjectId = where dt.TableName = 'AlertStage')

    Hope that helps.

    Dan Rawlings

  • Thank you Dan,
    your query helps.