Troubleshooting Common issues with Elastic Jobs in Azure SQL Database

Determining the Cause of Failure

Assume for the purposes of demonstration that I have created a job with a typo (missing parenthesis before the CREATE) in it:

I run the job several times, but it fails. We can see this in the portal, or through Azure Cloud Shell:

We can confirm the failure by issuing the following query in Sql Server Management Studio when connected to the job database (this hosts my Elastic Jobs agent) directly:

SELECT *  
FROM jobs.job_executions  
WHERE job_name = 'demo123'
ORDER BY start_time DESC 

Using any of these three methods confirms that the job failed. So, the first step is to drill down onto the failure message by executing the following query:

SELECT last_message  
FROM jobs.job_executions 
WHERE job_name = 'demo123' AND step_name <> 'NULL'

 This yields the following error message:

Fixing the Firewall Rules

This error means that the Elastic Job Agent cannot connect to the target server(s) because the target has some firewall rules blocking the connection requests.  Indeed - it is required that every target in the target group allows connections from Azure Services in order for Elastic Jobs to work.  To fix this, I go to the target server in the Azure Portal and click on the “Firewalls and virtual networks” item under “Security”.  Next, I toggle the "Allow Azure services" from OFF to ON, and save my changes.  

Fixing Typos or Syntax Errors in Job Steps 

Now, we re-execute the job, but the execution fails again.  If I use SQL Server Management Studio to execute the same query as before, I can get the most recent failure message.  This time, the error is: 

Recall that we expected this, since we knew there was a typo in the T-SQL used to define the step.  I am going to update the T-SQL with the correct parenthesis.  There are two ways to update a job step when you want to make this kind of a change - T-SQL or PowerShell.  If you are using T-SQL, you will want to use the stored procedure jobs.sp_update_jobstep .  In PowerShell you will use the Set-AzSqlElasticJobStep command from the specific job step you want to change.  I am going to update my job step using PowerShell.  To do this, first we need to bind the correct T-SQL text, then bind the specific job step we want to change, and then update it: 

:\> $sqlText1 = "if not exists (select * from sys.Tables where object_id = object_id('T')) create table T (a int, b int)" 
:\> $js = $job | Get-AzSqlElasticJobStep -Name step1
:\> $js | Set-AzSqlElasticJobStep -CommandText $sqlText1

Now that the job step has been updated, we wait for all running instances of the job to complete (if there are any) and re-run the job.  And, of course, check on its execution.

:\> $job | Start-AzSqlElasticJob 

Next, we can check the status by executing our query about job executions from Management Studio.  Note the job_version is now 2 instead of 1 (because of the update).  The job is running, and can complete successfully.

This post concludes my series covering Elastic Jobs on Azure SQL Database. I hope that you found it informative and helpful.

This blog is part of a series about Elastic Jobs on Azure SQL Database.

Code samples are also available on GitHub.  

1. Elastic Jobs in Azure SQL Database – What and Why
2. Fundamental Concepts for Elastic Jobs in Azure SQL Database
3. Creating an Elastic Jobs Agent, Credentials, and Jobs for Azure SQL Database in PowerShell
4. Creating an Elastic Jobs Agent, Credentials, and Jobs for Azure SQL Database using T-SQL
5. Running, Scheduling and Monitoring Elastic Jobs in Azure SQL Database
6. Troubleshooting Common issues with Elastic Jobs in Azure SQL Database