SCSM Datawarehouse Jobs Failing


Anybody who works with SCSM knows the DW can be a finicky mistress.  You have to treat her well or she will make your life miserable.    Jobs failing, Cubes not processing.  Rerruning jobs from the consoles gets you no where most of the time.  I’ve had to deal with this on multiple occasions and it isnt fun when CAB is quickly approaching and no data is coming into the reports.  This is one of my bigger issues so I thought I would share the information I recieved from Microsoft support on how to get things running smoothly again.

First, verify if there are any locks on the DWStagingAndConfig database.  These queries will need to be ran on the SCSM DW DB.

Select * FROM DWStagingAndConfig.dbo.LockDetails

If this returns nothing then we are clear on locks.  If you get something like

ResourceName               LockOwner         IsLocked

Infra.ProcessCategory   MPSync                1

then you know that the MPSync job is causing the lock and needs to be removed.  Run this query against the DWStagingAndConfig database

Use DWStagingAndConfig

EXEC

dbo.ReleaseLock

@ResourceName = ‘Infra.ProcessCategory’, @LockRequester = ‘mpsync’

You can replace ‘%MPSync%’ with whatever is the lockowner from the first query.  Once that is complete we need to reset the batches for all of the DW jobs.

use [DWStagingAndConfig]

DECLARE @Batches TABLE (

Id INT IDENTITY(1, 1),

BatchId BIGINT,

ProcessName NVARCHAR(MAX)

)

 

INSERT INTO @Batches (

BatchId,

ProcessName

) (

SELECT B.BatchId,

P.ProcessName FROM infra.Batch B JOIN infra.Process P ON P.ProcessId = B.ProcessId WHERE B.StatusId NOT IN (3,6)

AND P.ProcessCategoryId IN (1,4,6,7,8,9)

)

 

DECLARE @RowCount INT

 

SET @RowCount = (

SELECT COUNT(Id)

FROM @Batches

)

 

DECLARE @Counter INT

 

SET @Counter = 1

 

WHILE (@Counter <= @RowCount)

BEGIN

DECLARE @BatchId BIGINT

DECLARE @ProcessName NVARCHAR(MAX)

 

SET @BatchId = (

SELECT BatchId

FROM @Batches

WHERE Id = @Counter

)

SET @ProcessName = (

SELECT ProcessName

FROM @Batches

WHERE Id = @Counter

)

 

UPDATE infra.WorkItem

SET StatusId = 6

WHERE BatchId = @BatchId

UPDATE infra.Batch

SET StatusId = 5

WHERE BatchId = @BatchId

 

EXEC infra.CreateBatch @ProcessName

SET @Counter = @Counter + 1

END

You should see a bunch of rows affected and more importantly all of the DW Jobs should have new Batch ID’s and will go to a “Not Started” status.

Next we will run this PowerShell script on the datawarehouse management server.

###############Run in PowerShell with elevated Permissions from your DW Management Server###############

###############Also make sure to specific the proper values to the Parameters highlighted###################

[CmdletBinding()]

Param(

    [Parameter(Mandatory = $False)]

       [String] $DWServer = localhost,

    [Parameter(Mandatory = $False)]

       [String] $ASServer = localhost,

    [Parameter(Mandatory = $False)]

       [String] $ASDBName = DWASDatabase,

    [Parameter(Mandatory = $False)]

       [int] $Wait = 10

)

$props = Get-ItemProperty “HKLM:\SOFTWARE\Microsoft\System Center\2010\Common\Setup”

$instdir = $props.InstallDirectory

$dwpsd = $instdir + “Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1”

Import-Module -Name $dwpsd

$JSList = Get-SCDWJobSchedule -ComputerName $DWServer;

$JList = Get-SCDWJob -ComputerName $DWServer;

function Run-DWJob([String]$DWServer, [String]$JobName) {

    Write-Host “Enabling and running Job:” $JobName;

    Enable-SCDWJob -ComputerName $DWServer -JobName $JobName;

    Start-SCDWJob -ComputerName $DWServer -JobName $JobName;

    $currentJobStatus = Get-SCDWJob -JobName $JobName -ComputerName $DWServer | Select Status

    while($currentJobStatus.Status -eq “Running”) {

       Start-Sleep -s $Wait

       $currentJobStatus = Get-SCDWJob -JobName $JobName -ComputerName $DWServer | Select Status

       $moduleList = Get-SCDWJobModule -JobName $JobName -ComputerName $DWServer

       foreach($obj in $moduleList) {

          if([String]::IsNullOrEmpty($obj.ModuleErrorSummary) -ne $true) {

             Write-Host “There is no need to wait anymore for Job” $JobName “because there is an error in module” $obj.ModuleName “and the error is:” $obj.ModuleErrorSummary;

             exit;

          }

       }

    }

    if($currentJobStatus.Status -ne “Not Started”) {

       Write-Host “There is an error with” $JobName “and we will exit this – please inspect the status”;

       exit;

    }

}

foreach($obj in $JSList) {

    Write-Host “Disabling Schedule for Job: “ $obj.Name;

    Disable-SCDWJobSchedule -ComputerName $DWServer -JobName $obj.Name;

}

foreach($obj in $JList) {

    Write-Host “Stoping and disabling Job: “ $obj.Name;

    Stop-SCDWJob -ComputerName $DWServer -JobName $obj.Name;

    Start-Sleep -s $Wait

    Disable-SCDWJob -ComputerName $DWServer -JobName $obj.Name;

}

$maintenanceList = New-Object System.Collections.ArrayList;

$MPSyncList = New-Object System.Collections.ArrayList;

$extractList = New-Object System.Collections.ArrayList;

$transformList = New-Object System.Collections.ArrayList;

$loadList = New-Object System.Collections.ArrayList;

$cubeList = New-Object System.Collections.ArrayList;

foreach($obj in $JList) {

    if($obj.Name -match “Extract”) {

       $extractList.Add($obj.Name) | Out-Null;

    } elseif($obj.Name -match “Transform”) {

       $transformList.Add($obj.Name) | Out-Null;

    } elseif($obj.Name -match “Load”) {

       $loadList.Add($obj.Name) | Out-Null;

    } elseif($obj.Name -match “Maintenance”) {

       $maintenanceList.Add($obj.Name) | Out-Null;

    } elseif($obj.Name -match “MPSync”) {

       $MPSyncList.Add($obj.Name) | Out-Null;

    } else {

       $cubeList.Add($obj.Name) | Out-Null;

    }

}

foreach($obj in $maintenanceList) {

    Run-DWJob $DWServer $obj;

}

foreach($obj in $MPSyncList) {

    Run-DWJob $DWServer $obj;

}

foreach($obj in $extractList) {

    Run-DWJob $DWServer $obj;

}

foreach($obj in $transformList) {

    Run-DWJob $DWServer $obj;

}

foreach($obj in $loadList) {

    Run-DWJob $DWServer $obj;

}

foreach($obj in $cubeList) {

    Run-DWJob $DWServer $obj;

}

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”) | Out-Null;

$Server = New-Object Microsoft.AnalysisServices.Server;

$Server.Connect($ASServer);

$Databases = $Server.Databases;

$DWASDB = $Databases[$ASDBName];

$Dimensions = New-Object Microsoft.AnalysisServices.Dimension;

$Dimensions = $DWASDB.Dimensions;

foreach($dim in $Dimensions) {

    Write-Host “Processing Cube Job” $Dim.Name;

    $dim.Process(“ProcessFull”);

    Start-Sleep -s $Wait;

}

foreach($obj in $JSList) {

    Write-Host “Enabling Schedule for Job: “ $obj.Name;

    Enable-SCDWJobSchedule -ComputerName $DWServer -JobName $obj.Name;

}

Write-Host “”;

Write-Host “FINISHED!”

###############################################################################

This will disable all of the jobs and schedules and reenable each job and schedule in the proper order.  Let this finish and voila we are back in business.  Eventually I will wrap this up into an Orchestrator runbook so I only have to click a button so stay tuned.

Advertisements

3 thoughts on “SCSM Datawarehouse Jobs Failing

  1. Pingback: SCSM Datawarehouse Jobs Failing “Automated” – System Center

  2. Just wanted to say thanks. This solved a DW mess of my own rather instantly. As both locks and jobs are reset, this seems to be a “one size fits all” approach to DW-related issues. Definitely a keeper!

    Like

    1. I appreciate the feedback and I’m glad I could save someone else the misery of figuring that mess out :). If you’re fortunate enough to be using Orchestrator (which I highly recommend since you already own it if you’re using SCSM) check out my automated approach. You can fix your DW issues with a single click of a button.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s