Introduction

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.  Rerunning jobs from the consoles gets you no where most of the time.  I’ve had to deal with this on multiple occasions and it isn't 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 fix 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.  

Remove Lock


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.  

Reset Batch Jobs


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 in the SCSM console.

Powershell to Rerun Jobs


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

###############Run in PowerShell with elevated Permissions from your DW Management Server not the DW SQL Server.  Change the $ASServer variable to you Analysis Server FQDN###############
 
[CmdletBinding()]
Param(
    [Parameter(Mandatory = $False)]
       [String] $DWServer = "localhost",
    [Parameter(Mandatory = $False)]
       [String] $ASServer = "ANAYLSIS SERVER FQDN",
    [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!";
###############################################################################

Summary

  • This will disable all of the jobs and schedules and re-enable each job and schedule in the proper order.  Let this finish and voila we are back in business.

See Also