Share via

Data Warehouse problems after upgrading to SP1

  • Article

Recently I discovered that an otherwise working SCSM installation had begun to fail the MP synchronization after upgrading to SP1.

 

The following error was logged in the event log:

Event ID: 33333

Description:

Data Access Layer rejected retry on SqlError:
Request: Ral_ExecuteSql -- (statement=exec('IF OBJECT_ID(''[dbo].[WorkItemAffectedUserFact_2013_Jan]'') IS NULL
BEGIN
  CREATE TABLE [dbo].[WorkItemAffectedUserFact_2...), (RETURN_VALUE=0)
 Class: 16
 Number: 1779
 Message: Table 'WorkItemAffectedUserFact_2013_Jan' already has a primary key defined on it.

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 the upgrade to SP1. Several other 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

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 e.g. "Message: Table 'ConfigItemServicedByUserFact_2013_Jan' already has a primary key defined on it. ", delete the primary key from the DWRepository DB. For me there were around 70 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 TABLEdbo.BillableTimeHasWorkingUserFact_2013_Jan dropconstraint [PK_BillableTimeHasWorkingUserFact_2013_Jan]

Note that your fact table might be a different year and/or month than shown in the example above.

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. 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.

4. When cleaned up all Primary key errors and started the redelpoy, new errors migth occour and you then need to start with step 2 again, until all the MPs are synced with success

Thanks to Andrew Barton for creating the workaround.

Comments

  • Anonymous
    November 27, 2015
    Thanks for the great info. I really loved this. I would like to apprentice at the same time as you amend your web site, how could i subscribe for a blog site?
    For more info on showbox please refer below sites:
    http://showboxandroids.com/showbox-apk/
    http://showboxappandroid.com/
    Latest version of Showbox App download for all android smart phones and tablets. http://movieboxappdownloads.com/ - It’s just 2 MB file you can easily get it on your android device without much trouble. Showbox app was well designed application for android to watch movies and TV shows, Cartoons and many more such things on your smartphone.
    For showbox on iOS (iPhone/iPad), please read below articles:
    http://showboxappk.com/showbox-for-ipad-download/
    http://showboxappk.com/showbox-for-iphone/
    Showbox for PC articles:
    http://showboxandroids.com/showbox-for-pc/
    http://showboxappandroid.com/showbox-for-pc-download/
    http://showboxforpcs.com/
    There are countless for PC clients as it is essentially easy to understand, simple to introduce, gives continuous administration, effectively reasonable. it is accessible at completely free of expense i.e., there will be no establishment charges and after establishment it doesn't charge cash for watching films and recordings. Not simply watching, it likewise offers alternative to download recordings and motion pictures. The accompanying are the strides that are to be taken after to introduce Showbox application on Android. The above all else thing to be done is, go to the Security Settings on your Android telephone, Scroll down and tap on 'Obscure sources'.