Restarting the unresponsive Adaptive Job Service using a script

We recently came across an issue where our scheduled jobs on BI 4.1 platform wouldn’t run as expected; they would just not start. We have a clustered server setup and the job servers are running on couple of nodes. When this happens, the job server status on CMC would appear to be fine. We have raised this issue with SAP support and working on finding a resolution. In the meantime, we used the below Power Shell script as a work around to restart the job service when the schedules don’t run as expected.

Solution

We scheduled a simple web intelligence report that runs every 5 mins; Using PS, we checked for the status of this report through Auditing database. When there are no entries made in the auditing database for a set threshold time, we use CCM.exe to restart the JobServer.

#Auditing database credentials
$dataSource = "Auditing Database Server"
$user = "Auditing database user"
$pwd = 'Auditing database password'
$database = "Auditng Database Name"
$threshold_mins = 10
$connectionString = "Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;"
#Win AD authentication to SQL Server
#$connectionString = "Server=$dataSource;Database=$database;Integrated Security=True;"
#Query to check the scheduled report event in auditing database
#-480 mins is to adjust for local time Western Australia Standard Time
$query = "select count(0) from ADS_EVENT where Event_Type_ID=1011 and Object_name='Webi Heart Beat v1.0' and Start_time>=dateadd(minute,-480-$threshold_mins ,getdate())"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
#write-output $result.HasRows
while($result.read())
{
    Write-Output $result.GetValue(0)
    if ($result.GetValue(0) -gt 0)
    {
        Write-Output "Job Server Ok"
    }
    else
    {
    //Code to send email
    send-mailmessage -to "abc@xyz.com" -from "abc@xyz.com" -subject "Job Server being restarted" -SmtpServer smtp.xyz.com.au
    //Code to restart the server
  Invoke-Expression '& "C:\Program Files\SAP BusinessObjects Enterprise XI 4.0\win64_x64\ccm.exe" -managedstop SIA_BOBJ_DEV.AdaptiveJobServer -username administrator -password ######'
    Invoke-Expression '& "C:\Program Files\SAP BusinessObjects Enterprise XI 4.0\win64_x64\ccm.exe" -managedstart SIA_BOBJ_DEV.AdaptiveJobServer -username administrator -password ######'
    }
}
$connection.Close()