SCSM 2012 SP1 Data Warehouse Jobs failed with error message of <xxxx>vw is not updatable because a partitioning column was not found.

Wyatt Wong 116

I found 4 data warehouse jobs have a Failed status. After further checking, the error messages mentioned "UNION ALL view '<xxxx>vw' is not updatable because a partitioning column was not found." How can I resolve the issue ?

Load.Common
Transform.Common
Load.CMDWDataMart
Load.OMDWDataMart

Song, Michael 6 Reputation points

2020-12-03T00:49:49.313+00:00

I am also having the same issue with SCSM2016 1807
Georges Mouawad 11 Reputation points

2020-12-03T11:03:55.883+00:00

we are facing the same issue with SCSM 2012 R2 CU9

I tried to restore databases, even restore the VMs with no success.

Accepted answer

D B 111 Reputation points

2020-12-03T23:38:36.76+00:00
steps provided by MS Support...I did these with all the job schedules disabled...

on the datawarehouse SQL server...

select max(datekey) from DWRepository..DateDim --should be 20501230
select max(datekey) from DwDataMart..DateDim --should be 20501230
select max(datekey) from CMDwDataMart..DateDim --should be 20501230
select max(datekey) from OMDwDataMart..DateDim --should be 20501230

If your errors are caused by this issue, the above queries will return 20201230

If date keys are from Dec 2020, please use the below steps:

Backup all DW DBs

Add additional date keys until 2050
Exec DWRepository.dbo.PopulateDateDim '20210101','20501231'
Exec DwDataMart.dbo.PopulateDateDim '20210101','20501231'
Exec CMDwDataMart.dbo.PopulateDateDim '20210101','20501231'
Exec OMDwDataMart.dbo.PopulateDateDim '20210101','20501231'

rebuild the constraints on all 2021 fact tables by using the following script Run the following on DWRepository, DWDatamart, CMDwDatamart, OMDwDatamart

declare @objName sysname, @newChk as nvarchar(max)
declare c cursor local FORWARD_ONLY READ_ONLY for
select name from sys.sysobjects where xtype='C' and name like '%_2020_Dec_Chk' escape '\' order by 1
open c; while 1=1 begin; fetch c into @objName if @@Fetch _STATUS<>0 break;
set @objName = replace(@objName,'_2020_Dec_','_2021_Jan_')
set @objName = replace(@objName,'_Chk','')
set @newChk=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan] WITH CHECK ADD CONSTRAINT [XYZFact_2021_Jan_Chk] CHECK (([DateKey]>=(20210101)));ALTER TABLE [dbo].[XYZFact_2021_Jan] CHECK CONSTRAINT [XYZFact_2021_Jan_Chk];','XYZFact_2021_Jan',@objName)
exec sp_executesql @newChk
end; close c; deallocate c;

Restart Microsoft Monitoring Agent

After this I was able to manually start each of the failed jobs, waited for completion, then enabled my job schedules.
Please sign in to rate this answer.

6 people found this answer helpful.
Andrew Gordon 11 Reputation points

2020-12-04T01:00:54.287+00:00

This looks like it is working for me. A million, billion thank yous.

Andrew Gordon 11 Reputation points

2020-12-04T03:05:00.183+00:00

Yes, that means that you have to do the steps listed above. I got the same results that you did, performed the SQL scripts and then processed. Everything is going through perfectly again. Yea!!!

Zakaria Muhammad 161 Reputation points

2020-12-04T03:36:27.463+00:00

what does this XYZFact means on this line, "set @newChk=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan]"?

i don't have any table like XYZFact_2021...

Wyatt Wong 116 Reputation points

2020-12-04T03:42:58.363+00:00

I also want to know that. I have executed the above script for 4 databases DWRepository, DWDatamart, CMDwDatamart, OMDwDatamart and got no errors.

Wyatt Wong 116 Reputation points

2020-12-04T04:05:44.193+00:00

I have executed the steps provided by DB-7386 and now checking the result.

D B 111 Reputation points

2020-12-04T05:10:36.517+00:00

I'm no SQL expert, but it appears to be a variable for each job name that needs updated. If you add these lines before the exec sp_executesql @newChk line you will see...if you are concerned, remove the sp_executesql line...

Print (@objName)
Print (@newchk)

first example from mine:
ALTER TABLE [dbo].[ActivityRelatesToActionLogFact_2021_Jan] WITH CHECK ADD CONSTRAINT [ActivityRelatesToActionLogFact_2021_Jan_Chk] CHECK (([DateKey]>=(20210101)));ALTER TABLE [dbo].[ActivityRelatesToActionLogFact_2021_Jan] CHECK CONSTRAINT [ActivityRelatesToActionLogFact_2021_Jan_Chk];

Wyatt Wong 116 Reputation points

2020-12-04T05:28:00.697+00:00

Here is the result before execution of the above SQL statements:

Here is the result after execution of the above above SQL statements and re-run of the SCDW Jobs. The BatchId of Load.Common, Transform.Common, Load.CMDWDataMart and Load.OMDWDataMart have successfully changed to a new value, which means the the previous BatchId have successfully completed.

Wyatt Wong 116 Reputation points

2020-12-04T08:04:42.463+00:00

I believed ZakariaMuhammad-8648 was asking a different thing. He mentioned that he could not find any table called XYZFact_2021_Jan. I'll check if I can find out more information.

ST1285 11 Reputation points

2020-12-04T12:06:46.817+00:00

Thanks for sharing, but we're getting absolutely no luck with this. We run the above in SQL, get 'Command Completed Successfully', but still get the 'partitioning column was not found' error on the transform.common and the three Load.x jobs.

However, there are 31 days in Dec - should the top bits not be 20501231?

Pablo Schlegel 6 Reputation points

2020-12-04T15:52:11.11+00:00

Solve!, Thanks

mohammad saad 21 Reputation points

2020-12-07T10:14:19.473+00:00

Thanks, It worked for us as well, we have been facing this issue since 01-DEC-2020, and now it is resolved.

Thank you!

Wyatt Wong 116 Reputation points

2020-12-08T04:05:34.16+00:00

I modified the above SQL script as follows:

USE [DWRepository]

declare @objName sysname, @newChk as nvarchar(max)
declare c cursor local FORWARD_ONLY READ_ONLY for
select name from sys.sysobjects where xtype='C' and name like '%_2020_Dec_Chk' escape '\' order by 1
open c; while 1=1 begin; fetch c into @objName if @@Fetch _STATUS<>0 break;
set @objName = replace(@objName,'2020_Dec','2021_Jan')
set @objName = replace(@objName,'_Chk','')
set @newChk=replace('ALTER TABLE [dbo].[XYZFact_2021_Jan] WITH CHECK ADD CONSTRAINT [XYZFact_2021_Jan_Chk] CHECK (([DateKey]>=(20210101)));ALTER TABLE [dbo].[XYZFact_2021_Jan] CHECK CONSTRAINT [XYZFact_2021_Jan_Chk];','XYZFact_2021_Jan',@objName)
print @newChk
end; close c; deallocate c;

Please find a sample output of the @newChk variable as follows:

Jaime Esquivel 6 Reputation points

2020-12-09T22:09:31.303+00:00

This just worked for us! Thank you!!

Wyatt Wong 116 Reputation points

2020-12-10T00:52:25.523+00:00

Have you run the following SQL statements

Exec DWRepository.dbo.PopulateDateDim '20210101','20501231'
Exec DwDataMart.dbo.PopulateDateDim '20210101','20501231'
Exec CMDwDataMart.dbo.PopulateDateDim '20210101','20501231'
Exec OMDwDataMart.dbo.PopulateDateDim '20210101','20501231'

and then execute the SQL script ?

According to @D B , you need to run the SQL script in each of the DWRepository, DWDatamart, CMDwDatamart, OMDwDatamart databases. You can add the USE [database name] command first before executing the SQL script

You also need to restart the System Center Health Service in the SCSM Data Warehouse management server and re-run each SCDW Job of Load.Common, Transform.Common, Load.CMDWDataMart and Load.OMDWDataMart

Camilo Andres Vanegas Ramirez 11 Reputation points

2020-12-30T21:52:11.837+00:00

Muchas gracias, me funciono, la ejecución de los job's se ejecutaron de forma correcta, de nuevo muchas gracias

Salim Assaf 6 Reputation points

2020-12-31T19:14:20.06+00:00

To everyone looking at this, you may want to double check your dates... Dec 31, 2020 does not exist in the DateDim tables therefore the below should likely be adjusted as it will cause you problems.

Exec DWRepository.dbo.PopulateDateDim '20201231','20501231'
Exec DwDataMart.dbo.PopulateDateDim '20201231','20501231'
Exec CMDwDataMart.dbo.PopulateDateDim '20201231','20501231'
Exec OMDwDataMart.dbo.PopulateDateDim '20201231','20501231'

Camilo Andres Vanegas Ramirez 11 Reputation points

2021-01-04T13:50:25.597+00:00

realice el procedimiento el día 31 de diciembre y fue satisfactorio, sin embargo validando el día de hoy nuevamente se presentan las mismas fallas, agradezco su colaboración si hace falta ejecutar algo adicional, gracias

C Sharp Conner 1 Reputation point

2021-01-07T00:19:35.577+00:00

Still having issue on SCSM 2012 R2 UR9. Error slightly different from yours, believed to have happened Dec 30th. Error 4457.

Operation Manager Error 4457 - The attempted insert or update of the partitioned view failed because the value of the partitioning column does not belong to any of the partitions.

Eventually the TransformEntityManagedTypeFact module fails as a result with same 4457 error…. Details say On Step 5 Inserting Facts (Which are relationships) … the others say "Inserting into destination fact"

I think it stands to reason the "value" is bad data like a NULL being entered for a relationship.

Hypothesis: The LIVE Database has bad data in the dbo.relationship table and when it is given to the ARCHIVE, the ARCHIVE is unable to insert this bad data (like NULLs) for Primary Keys or something like that...

Wyatt Wong 116 Reputation points

2021-01-07T05:35:03.747+00:00

Can you post the screen output of "Get-SCDWJob" and share to us ?

Wyatt Wong 116 Reputation points

2021-01-07T05:37:39.04+00:00

I wrote a SQL script to list out all SCSM DW Jobs but excluding the Completed Jobs. Here it is:

USE [DWStagingAndConfig]

SELECT p.ProcessName, w.WorkItemId, w.ProcessModuleId, pm.VertexName AS [ModuleName], w.BatchId, w.StatusId, s.StatusName, w.RetryCount, w.ErrorCount, w.ErrorSummary, w.InsertTime, w.TakenTime, w.UpdateTime, w.NotToBePickedBefore, w.CustomInfo
FROM Infra.[Process] p INNER JOIN Infra.[Batch] b ON p.ProcessId = b.[ProcessId]
INNER JOIN Infra.WorkItem w ON b.BatchId = w.[BatchId]
INNER JOIN Infra.[Status] s ON w.StatusId = s.StatusId
INNER JOIN Infra.[ProcessModule] pm ON w.ProcessModuleId = pm.ProcessModuleId
WHERE b.StatusId <> 6 AND
w.StatusId <> 6
ORDER BY StatusId DESC, p.ProcessName ASC

@Jim Finke @Zakaria Muhammad @mohammad saad @Salim Assaf

Wyatt Wong 116 Reputation points

2021-01-07T05:42:29.533+00:00

An enhanced version include a list of commented items.

To check the failed SCSM DW Jobs, simply comment out the "w.StatusId <> 6' and uncomment "w.StatusId = 2"

To look for a specific SCSM DW Jobs, simply uncomment one of the "-- AND ProcessName = 'XXXXXXXXXX'" entry

@Jim Finke @Zakaria Muhammad @mohammad saad @Salim Assaf

mohammad saad 21 Reputation points

2021-01-07T06:13:07.113+00:00

great!!!

Thank you

Wyatt Wong 116 Reputation points

2021-01-07T10:33:14.44+00:00

The script listed out all the SCSM DW Jobs (including their modules) with BatchId as well as the StatusId and the Status Description. I created the script so that I can find out the BatchId of each SCSM DW Jobs more quickly than using the Powershell command Get-SCDWJob

Daniel Luck 1 Reputation point

2021-01-09T21:23:08.703+00:00

Dear All,

Please help, i have run the this fix and it was working up until 30th December 2020. All jobs fail now and there is not reporting after 30th December. I have tried all items in this forum and none seems to work.

This is the fix I did. So anything after this date doesn't show.

I am very keen to get his fixed. If someone has a script to fix it? It appears that 31st December is not in the database?

Thanks

Daniel

Fix: DB-7386

Daniel Luck 1 Reputation point

2021-01-09T21:24:02.46+00:00

Dear All,

Please help, i have run the this fix and it was working up until 30th December 2020. All jobs fail now and there is not reporting after 30th December. I have tried all items in this forum and none seems to work.

This is the fix I did. So anything after this date doesn't show.

I am very keen to get his fixed. If someone has a script to fix it? It appears that 31st December is not in the database?

Thanks

Daniel

Fix: DB-7386

James Johnson 1 Reputation point

2021-02-03T19:01:13.19+00:00

Is this supposed to create the xyzFact_2021_JAN tables? I've tried running your modified script (thanks for posting it) but I still don't have any Jan tables and I keep getting the partitioning column error on the Transform.Common job.

Wyatt Wong 116 Reputation points

2021-02-04T03:11:31.74+00:00

There is no need to create any tables. You can send e-mail to me at <wyattwong @ outlook . com> for further assistance.
Sign in to comment

38 additional answers

Wyatt Wong 116 Reputation points

2021-01-20T02:37:10.677+00:00

@Andrew Gordon I was unable to locate your post. You may contact me directly at

wyattwong @ outlook . com
Please sign in to rate this answer.
Andrew Gordon 11 Reputation points

2021-01-20T19:01:54.363+00:00

Frustrating computers not doing what I'm asking. I'll try again here.58736-cmdwdatamart.txt 58822-dwdatamart.txt 58806-dwrepository.txt 58700-omdwdatamart.txt

I'll send this to your e-mail again to cover both contingencies.

Wyatt Wong 116 Reputation points

2021-01-21T01:27:34.783+00:00

I sampled check your CMDWDataMart database and found the following constraints are incorrect

dbo.ComputerHostsLogicalDiskFact_2018_Jan Table Check constraint ComputerHostsLogicalDiskFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.ComputerHostsOperatingSystemFact_2018_Jan Table Check constraint ComputerHostsOperatingSystemFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.ComputerHostsProcessorFact_2018_Jan Table Check constraint ComputerHostsProcessorFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.DeployedComputerRunsWindowsComputerFact_2018_Jan Table Check constraint DeployedComputerRunsWindowsComputerFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.GroupContainsConfigItemFact_2018_Jan Table Check constraint GroupContainsConfigItemFact_2018_Jan_Chk ([DateKey]<=(20180131))
dbo.ServiceContainsConfigItemFact_2018_Jan Table Check constraint ServiceContainsConfigItemFact_2018_Jan_Chk ([DateKey]<=(20180131))

Andrew Gordon 11 Reputation points

2021-01-21T01:36:00.987+00:00

Yes, I tried correcting those but when I ran the script to do so, SQL output attached.58927-computerhostslogicaldiskfact-2018-jan.txt

Wyatt Wong 116 Reputation points

2021-01-21T01:39:32.71+00:00

However, I am not sure if it is the causing your data warehouse jobs failed. If they are really the issues, then you should have encountered Data Warehouse jobs failed issue back in 2018 Jan.

However, you can run the following SQL scripts to fix the constraints problem for ComputerHostsLogicalDiskFact_2018_Jan table

ALTER TABLE [dbo].[ComputerHostsLogicalDiskFact_2018_Jan] DROP CONSTRAINT [ComputerHostsLogicalDiskFact_2018_Jan_Chk];
ALTER TABLE [dbo].[ComputerHostsLogicalDiskFact_2018_Jan] WITH CHECK ADD CONSTRAINT [ComputerHostsLogicalDiskFact_2018_Jan_Chk] CHECK (([DateKey]>=(20180101) AND [DateKey]<=(20180131)));
ALTER TABLE [dbo].[ComputerHostsLogicalDiskFact_2018_Jan] CHECK CONSTRAINT [ComputerHostsLogicalDiskFact_2018_Jan_Chk];

Andrew Gordon 11 Reputation points

2021-01-21T01:43:15.657+00:00

Thank you. Tried that. Sadly, error.

Msg 3728, Level 16, State 1, Line 1
'ComputerHostsLogicalDiskFact_2018_Jan_Chk' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.

We've been running Service Manager since October, 2012, successfully until this nightmare started seven weeks ago. Maybe this is final straw so that we can dump SM and move onto something new.

Wyatt Wong 116 Reputation points

2021-01-21T07:15:13.347+00:00

Can you manually delete the constraint from the Object Inspector in SQL Server Management Studio ?

Andrew Gordon 11 Reputation points

2021-01-21T19:19:31.877+00:00

Yes, that is what I have to do before I can run any scripts against it. I've attached the output for the data contained in the table. I find it odd that there is data going back to October, 2012 but no Facts listed for before January, 2018. Maybe I have to create the missing Facts manually?

59245-computerhostslogicaldiskfact-2018-jantop10000rows.txt

Andrew Gordon 11 Reputation points

2021-01-22T01:22:19.287+00:00

For this one Fact, I have recreated all the missing monthly Facts but am still getting the error

UNION ALL view 'CMDWDataMart02.dbo.ComputerHostsLogicalDiskFactvw' is not updatable because a partitioning column was not found

three times for each run.

Wyatt Wong 116 Reputation points

2021-01-22T02:02:39.013+00:00

There is NO need to retrieve the rows from the ComputerHostsLogicalDiskFact_2018_Jan table. Can you manually delete the constraint from the UI and then execute the following SQL scripts?

-- ALTER TABLE [dbo].[ComputerHostsLogicalDiskFact_2018_Jan] DROP CONSTRAINT [ComputerHostsLogicalDiskFact_2018_Jan_Chk];
ALTER TABLE [dbo].[ComputerHostsLogicalDiskFact_2018_Jan] WITH CHECK ADD CONSTRAINT [ComputerHostsLogicalDiskFact_2018_Jan_Chk] CHECK (([DateKey]>=(20180101) AND [DateKey]<=(20180131)));
ALTER TABLE [dbo].[ComputerHostsLogicalDiskFact_2018_Jan] CHECK CONSTRAINT [ComputerHostsLogicalDiskFact_2018_Jan_Chk];

Andrew Gordon 11 Reputation points

2021-01-22T02:18:41.04+00:00

This is the output that I get after deleting the constraint and running your script.

Msg 3728, Level 16, State 1, Line 1
'ComputerHostsLogicalDiskFact_2018_Jan_Chk' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.

If I remove the drop constraint line from your script, I get this output.

Msg 547, Level 16, State 0, Line 3
The ALTER TABLE statement conflicted with the CHECK constraint "ComputerHostsLogicalDiskFact_2018_Jan_Chk". The conflict occurred in database "CMDWDataMart02", table "dbo.ComputerHostsLogicalDiskFact_2018_Jan", column 'DateKey'.

Thank you.

Wyatt Wong 116 Reputation points

2021-01-22T07:36:02.25+00:00

Do you have enough permission to drop and create the constraints ? Are you using an account with sufficient privileges ?

Wyatt Wong 116 Reputation points

2021-01-27T03:15:58.593+00:00

I found a post below:

https://dba.stackexchange.com/questions/115637/the-alter-table-statement-conflicted-with-the-check-constraint

According to the answer, if you encounter error when you execute "ALTER TABLE ...... WITH CHECK ADD CONSTRAINT .... CHECK ....." command. That could mean at least 1 row in your dbo.ComputerHostsLogicalDiskFact_2018_Jan table have DateKey value that violate the constraint condition of:

([DateKey] >= (20180101) AND [DateKey] <= (20180131))

So could you do a SELECT statement on dbo.ComputerHostsLogicalDiskFact_2018_Jan table to see if there is any DateKey value which is outside the above date range ?

Andrew Gordon 11 Reputation points

2021-01-31T23:04:14.267+00:00

Yes I do. I have no problem deleting the constraint, and I can see it is deleted after doing so.

Wyatt Wong 116 Reputation points

2021-02-03T04:49:45.047+00:00

@Andrew Gordon I have successfully assisted you and resolved your SCSM DW Jobs failed issues by e-mail, thank you very much for your patience.
Sign in to comment
Anton Tolmachev 11 Reputation points

2021-03-03T12:57:17.12+00:00

Hey again, folks. Almost two months passed after the nasty error with Dates of Dec 2020 and Jan 2021 was fixed, so far so good, all was quiet.
Recently a new little gem popped up, not 100% percent sure if it's correlated with the Dates initial cause, on the other hand again.. damn Dec 2020 is involved. Got this one at DW server EventLog:
Unhandled exception in data warehouse maintenance:
Work item: 198356596
Maintenance action: PerformWarehouseGrooming Exception details:
Exception message: ErrorNumber="50000" Message="ErrorNumber="50000" Message="ErrorNumber="547" Message="The ALTER TABLE statement conflicted with the CHECK constraint "ComputerHasPrimaryUserFact_2020_Dec_Chk". The conflict occurred in database "DWRepository", table "dbo.ComputerHasPrimaryUserFact_2020_Dec", column 'DateKey'." Severity="16" State="0" ProcedureName="(null)" LineNumber="1" Task="Executing CHKScriptTemplate"" Severity="18" State="0" ProcedureName="DropCheckConstraintForTable" LineNumber="145" Task="Opening MIN Check constraint for the next Partition"" Severity="18" State="0" ProcedureName="DropPartition" LineNumber="108" Task="Executing groomingStoredProcedure: EXEC etl.DropPartition @WarehouseEntityId=@WarehouseEntityId, @WarehouseEntityType=@WarehouseEntityType, @EntityGuid=@EntityGuid, @PartitionId=@PartitionId, @GroomActiveRelationship=1"

as per @Wyatt Wong advice i've checked table dbo.ComputerHasPrimaryUserFact_2020_Dec (since the error contains "The ALTER TABLE statement conflicted with the CHECK constraint" as well) to see if any DateKey value for whatever reason is out of the constraint condition (which is ([DateKey]>=(20201201) AND [DateKey]<=(20201231)) - but it's NOT, all the values are inside the range.
All the DW Jobs in overall (including DWMaintenance itself) DO complete Successfully.
Not sure at all for now why this is happening and the first Error event was dropped accurately on 1st March..duh.
Found similar older thread correlated unhandled-exception-in-data-warehouse-maintenance seems someone had that too and couldn't figure out what it is, though all was working in overall as well.
If someone experiences this as well or got any clue - would be highly appreciated.
Please sign in to rate this answer.
Wyatt Wong 116 Reputation points

2021-03-04T01:36:35.737+00:00

What does your "Get-SCDWJob" looks like. Did you see any SCSM Data Warehouse Jobs show error and stuck in the same BatchId ?

Anton Tolmachev 11 Reputation points

2021-03-04T08:16:56.233+00:00

Good day, i've attached the DWJobs screen, no any errors (except mentioned one in the EventLog, i assume it comes during DWMaintenance task hence repeats and logged hourly) and no stuck Batches as well, as per EventLog all jobs complete Successfully in overall (DWMaintenance too)

Wyatt Wong 116 Reputation points

2021-03-04T08:55:56.3+00:00

You may keep monitoring the "Get-SCDWJob" for a few days and take note of any changes in the BatchId. If the SCSM DW Jobs are working properly, you should see the BatchId change. Also double check the "StartTime" and "EndTime" column. If a SCSM DW Job "StartTime" was stuck in yesterday's date or a few dates before, then that means your specific SCSM DW Jobs was stuck and need to resolve.

In your current situation, I think you don't need to do anything as you only saw the errors in the System Center Operations Manager Event Log.

Anton Tolmachev 11 Reputation points

2021-03-04T09:22:28.933+00:00

BatchIDs are changing i kept an eye on those starting yesterday, no stuck "StartTime" anywhere as well..the jobs start @ Current time and complete Successfully, as per schedules every hour. Seems there's nothing more to dig for now,yeah..the points are that it's in scope of DWMaintenance job and correlated to Grooming process, but DWMaintenance in overall is not failed and complete Successfully as stated earlier. Just those _2020_Dec thing did hit my eye when i've seen Error logged and made feel a bit creepy again :) But for now - guess no clues and no points to add to this.

Wyatt Wong 116 Reputation points

2021-03-05T04:46:03.503+00:00

As I said before I don’t think your SCSM Data Warehouse Jobs have any issues. I saw some error messages in the System Center Operations Manager Event Log from time to time..

Wyatt Wong 116 Reputation points

2021-03-08T01:45:46.3+00:00

A few days have passed. Have you run Get-SCDWJob and checked there was no errors ? If so, I thought your SCSM is healthy.

Anton Tolmachev 11 Reputation points

2021-03-09T06:15:20.297+00:00

Good day! Thanks for keeping in touch about this. Get-SCDWJob shows no errors really, as earlier. Though the initial DWMaintenance error at the EventLog is still there and keeps occuring every hour. Not sure if it'll go away by itself, have no idea why it popped up strictly on March 1st :) maybe i'll give it a try restarting both SCSM servers some time later, but also have the feeling - it won't fix it, seems like it stumbled on something weird trying to go through this Grooming process or do some checks, but can't figure out - what it is :)

Wyatt Wong 116 Reputation points

2021-03-09T08:34:31.597+00:00

You may try to run the following SQL statement written by myself. To check the meaning of the StatusId, please refer to the following URL:
https://learn.microsoft.com/en-us/troubleshoot/system-center/scsm/troubleshoot-stuck-data-warehouse-jobs

USE [DWStagingAndConfig]

SELECT p.ProcessName, w.WorkItemId, w.ProcessModuleId, pm.VertexName AS [ModuleName], w.BatchId, w.StatusId, s.StatusName, w.RetryCount, w.ErrorCount, w.ErrorSummary, w.InsertTime, w.TakenTime, w.UpdateTime, w.NotToBePickedBefore, w.CustomInfo
FROM Infra.[Process] p INNER JOIN Infra.[Batch] b ON p.ProcessId = b.[ProcessId]
INNER JOIN Infra.WorkItem w ON b.BatchId = w.[BatchId]
INNER JOIN Infra.[Status] s ON w.StatusId = s.StatusId
INNER JOIN Infra.[ProcessModule] pm ON w.ProcessModuleId = pm.ProcessModuleId
WHERE b.StatusId <> 6 AND
w.StatusId = 2
ORDER BY StatusId DESC, p.ProcessName ASC

Anton Tolmachev 11 Reputation points

2021-03-09T11:59:38.91+00:00

Thanks for sharing this. I've executed the statement - no output (0 rows affected).

Wyatt Wong 116 Reputation points

2021-03-10T01:21:35.327+00:00

If no rows were selected, that means there is no failed SCSM DW Jobs which match with the result from Get-SCDWJob output. May I know the Event ID of the event that you found out from the Windows Event Viewer ?

According to the error message, it looks like to be related to the Grooming.

Wyatt Wong 116 Reputation points

2021-03-10T01:26:53.383+00:00

After checking the Event Log in SCSM DW Server, I also found the similar Event with Event ID 33522.

Anton Tolmachev 11 Reputation points

2021-03-10T06:11:01.03+00:00

Yeah, same Event ID (33522). Definitely related to Grooming process, yes. So you've got it as well ? is table mentioned inside of it - same as i have in mine ?
Message="The ALTER TABLE statement conflicted with the CHECK constraint "ComputerHasPrimaryUserFact_2020_Dec_Chk". The conflict occurred in database "DWRepository", table "dbo.ComputerHasPrimaryUserFact_2020_Dec", column 'DateKey'."

Wyatt Wong 116 Reputation points

2021-03-11T01:06:09.103+00:00

The message I got is different from you:

"The ALTER TABLE statement conflicted with the CHECK constraint "ConfigItemGroupContainsEntitiesFact_2020_Dec_Chk". The conflict occurred in database "DWRepository", table "dbo.ConfigItemGroupContainsEntitiesFact_2020_Dec", column 'DateKey'

Wyatt Wong 116 Reputation points

2021-03-11T02:16:40.247+00:00

I referred to the error messages in the SCSM DW Event Log and found the following procedure name:

DropCheckConstraintForTable
DropPartition

And I found the task name as follows:

Executing groomingStoredProcedure: EXEC etl.DropPartition @WarehouseEntityId=@WarehouseEntityId, @WarehouseEntityType=@WarehouseEntityType, @EntityGuid=@EntityGuid, @PartitionId=@PartitionId, @GroomActiveRelationship=1

I was able to find the Stored Procedure etl.DropCheckConstraintForTable and etl.DropPartition under DWRepository Database, however I have no idea what is the actual value of the parameters for DropPartition stored procedure so I was unable to execute the stored procedure manually.

I also found that the CHECK constraint in the "ConfigItemGroupContainsEntitiesFact_2020_Dec_Chk" table was already dropped.

Wyatt Wong 116 Reputation points

2021-03-11T04:43:37.477+00:00

I think I found out the error is related to the constraint:

I executed the following statement and got the following error message (refer to the Error.png attachment):

======================
USE [DWRepository]

EXEC etl.PerformGrooming

Notice the statement with the CHECK constraint of (DateKey <= 20201230).

ALTER TABLE IncidentStatusDurationFact_2020_Dec WITH CHECK ADD CONSTRAINT IncidentStatusDurationFact_2020_Dec_Chk CHECK (DateKey <= 20201230)

However, the actual CHECK constraint for the IncidentStatusDurationFact_2020_Dec table is ([DateKey] >= 20201201 AND [DateKey] <= 20201231). I have modified the CHECK constraint to (DateKey] <= 20201231) but it still failed due to the the date is 20201231 instead of 20201230.

I checked that the etl.PerformGrooming will execute the Stored Procedure etl.DropPartition and I am still troubleshooting the issue.

Wyatt Wong 116 Reputation points

2021-03-11T06:28:12.64+00:00

I have finally resolved the problem.

First of all, the etl.PerformGrooming Stored Procedure will retrieve the value of "GroomingStoredProcedure" from etl.WarehouseEntityGroomingInfo table and then execute it. The GroomingStoredProcedure looks like the following:

======

EXEC etl.DropPartition @WarehouseEntityId=@WarehouseEntityId, @WarehouseEntityType=@WarehouseEntityType, @EntityGuid=@EntityGuid, @PartitionId=@PartitionId, @GroomActiveRelationship=1

Then in the etl.DropPartition Stored Procedure, it will perform a series of process, including the execution of another Stored Procedure etl.DropCheckConstraintForTable. One of the parameters to pass into the etl.DropCheckConstraintForTable Stored Procedure is the @followingPartitionName. The value of the @followingPartitionName was retrieved from a table called etl.TablePartition.

Wyatt Wong 116 Reputation points

2021-03-11T06:36:51.717+00:00

In the etl.DropCheckConstraintForTable Stored Procedure, you can find a large section of comments which include a series of EXEC etl.DropCheckConstraintForTable and ALTER TABLE statements.

Outside the comment block, the Stored Procedure try to build the @CHKScript and then execute it. In the @CHKScript variable, there is a RangeEndDate value, which is retrieved from etl.TablePartition table.

You can execute the following statement to find out RangeEndDate of 20201230 in the etl.TablePartition table:

====
USE [DWRepository]

SELECT * FROM etl.TablePartition

WHERE RangeEndDate = 20201230

Since the CHECK constraint end date is 20201231, you can execute the following statement to patch the etl.TablePartition table:

====
USE [DWRepository]

UPDATE etl.TablePartition
SET RangeEndDate = 20201231

WHERE RangeEndDate = 20201230

Wyatt Wong 116 Reputation points

2021-03-11T06:38:36.577+00:00

After patching the etl.TablePartition, you can execute the following statement and the Grooming should finished without any errors:

USE [DWRepository]

EXEC etl.PerformGrooming

It will take a few minutes to execute and after successful execution, you should see some messages about which tables were groomed.

If you execute "EXEC etl.PerformGrooming" again, you should receive the message "Checking to break the loop. @maxPartitionsToGroom: IS NULL @tempEntityId: IS NULL @tempPartitionId: IS NULL" which means there are NO more tables that need to be groomed.

Anton Tolmachev 11 Reputation points

2021-03-12T08:17:05.843+00:00

Good day ! First of all a BIG thank you for this great follow up. I've followed your TS plan step by step and it was just the case. Executing UPDATE of RangeEndDate on etl.TablePartition fixed 61 row with '20201230' Dates and after that Grooming ran smoothly and did it's thing, no initial error was spotted repeating after that. All good. And i think it'll help others, i'm more than sure many ppl may have this error shown up as well. Again great TS steps, your help appreciated much with this.

Wyatt Wong 116 Reputation points

2021-03-12T08:52:35.657+00:00

You are welcome. Your discovery on the Grooming Error in the SCSM DW Operations Manager Event Log is very valuable. It was fun for me to troubleshoot the issue and that I was able to find out the root cause and resolve the issue.

I checked that there are no more Grooming Errors in the SCSM DW Operations Manager Event Log after fixing the Grooming Error issue.
Sign in to comment
Bill Crum 56 Reputation points

2021-06-10T07:25:04.183+00:00

So I was very late to the game on this, We dont use SCSM for much anymore, but I just happened to do some windows updates and noticed my DW was broken. Research lead me here, and pursuing the solutions here did not help me much, but I must also say this DW has been jacked up for 2 years, so it has alot MORE wrong with it to, however I couldnt free myself of these unique errors, so I kept working.

Well, I think I made it worse by customizing the script some, but all in all I couldnt find any bad constraints myself, so I went in search elsewhere and ran across this script below, which was from Microsoft, and it is far better than the ones here from what I can tell.

Note: There is a Boolean at the top, if you set it at 0 it only runs in practice mode... BUT it will give you a message that there are no errors, THIS IS MISLEADING. I checked all my DBs and it showed good, so I flipped the boolean to 1 to actually let it run, and it reports on how many breaks you have, and I had a LOT. It will tell you to rerun it to check again, and when it runs a pass without any fixes, it does not put all those Altars on the page, just tells you it was good.

Errors:

Clean Database:
104060-image.png
Please sign in to rate this answer.
Bill Crum 56 Reputation points

2021-06-10T07:38:21.637+00:00

https://learn.microsoft.com/en-us/answers/questions/182935/scsm-2012-sp1-data-warehouse-jobs-failed-with-erro.html?childToView=430050#answer-430050

104161-image.png
Sign in to comment
Bill Crum 56 Reputation points

2021-06-10T07:39:20.283+00:00

Here is a screenshot of a clean run of the script with it in ACTIVE mode.... Notice we ONLY see the successful no errors messages? When you run it in test mode, it says no errors also, but for me it had TONs of lines of commands that would fix problems also.

I am having issues getting the upload to work on this 10kb txt file :(, but if I cant upload it, I'll post it somewhere and link it.

I hope this helps others like me who arent very adept at SQL and understanding everything involved, I do not know yet if my jobs run, Im just kicking them off, but I do know this script fixed over 1k errors on my DW after I couldnt find ANY bad constraints with my terrible skills.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment