Question 948 of 919
Automate Index Maintenance Across All Azure SQL Databases Using Elastic Jobs
A company uses Azure SQL Managed Instance. They need to automate index maintenance for all databases in the instance. The solution must minimize administrative overhead and use built-in Azure features. What should you do?
Quick Answer
The correct choice is to create an elastic job agent with a T-SQL script for index maintenance targeting all databases, because this built-in Azure feature is purpose-built to automate index maintenance across databases in Azure SQL Managed Instance with minimal administrative overhead. Elastic jobs manage scheduling, retries, and target group membership natively, eliminating the complexity of manual scripting or external tools. On the Microsoft Azure Database Administrator Associate DP-300 exam, this scenario tests your understanding of native automation options for multi-database administration — a common trap is reaching for SQL Server Agent jobs, which don’t natively target all databases in an instance and add overhead. The search intent to automate index maintenance across databases aligns directly with elastic jobs’ capability to run identical T-SQL scripts against a dynamic, defined group. Memory tip: “elastic” stretches across every database — one job, many targets, zero extra effort.
⚠ Common exam trap
The trap here is that candidates might confuse SQL Agent jobs (which are database-scoped in Managed Instance) with the ability to run cross-database tasks, or assume Azure Automation is the only built-in option, when in fact elastic job agents are the native, low-overhead solution for multi-database automation in Azure SQL Managed Instance.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Create an elastic job agent with a T-SQL script for index maintenance targeting all databases.
An elastic job agent is a built-in Azure feature designed specifically for automating administrative tasks across multiple databases in Azure SQL Managed Instance. It allows you to create a T-SQL script for index maintenance and target all databases in the instance with minimal overhead, as it manages scheduling, retries, and target group membership natively.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use Azure Automation with a PowerShell runbook that connects to each database and runs index maintenance.
Why it's wrong here
This requires custom scripting and additional management, not minimizing overhead.
- ✗
Configure a SQL Agent job on the instance to run index maintenance on the master database.
Why it's wrong here
SQL Agent job runs on a single database, not all databases.
- ✓
Create an elastic job agent with a T-SQL script for index maintenance targeting all databases.
Why this is correct
Elastic job agent is designed for automating tasks across databases in a managed instance with minimal overhead.
- ✗
Use Azure Data Factory to schedule a stored procedure execution for each database.
Why it's wrong here
Data Factory is not optimized for database maintenance tasks and adds complexity.
About these practice questions
Courseiva creates original exam-style practice questions with explanations and wrong-answer analysis. It does not publish real exam questions, exam dumps, or protected exam content. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on DP-300
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. Which TWO methods can be used to automate index maintenance in Azure SQL Database?
hard- ✓ A.Azure Automation runbooks executing T-SQL scripts
- B.SQL Server Agent jobs
- C.Azure Data Factory pipelines
- ✓ D.Elastic jobs with T-SQL steps
- E.Automatic tuning
Why A: Azure Automation runbooks can execute T-SQL scripts against Azure SQL Database using the Invoke-SqlCmd cmdlet or similar, enabling scheduled index maintenance tasks such as rebuilding or reorganizing indexes. This method is fully supported in Azure SQL Database, which lacks SQL Server Agent, and allows for flexible, cloud-native automation.
Last reviewed: Jun 11, 2026
This DP-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-300 exam.
Question Discussion
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
Sign in to join the discussion.