70-465 Question #102: Real Exam Question with Answer & Explanation

Question

You have a SQL Server instance on a server named Server1. You need to recommend a solution to perform the following tasks every week: - Rebuild the indexes by using a new fill factor. - Run a custom T-SQL command. - Back up the databases. What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

Options

• AA trigger
• BAn alert
• CA maintenance plan
• DWindows PowerShell
• EA system policy

Explanation

SQL Server Maintenance Plans provide a built-in, wizard-driven framework for scheduling recurring database administration tasks including index rebuilds, custom T-SQL execution, and backups.

Common mistakes.

• A. Triggers fire in response to DML or DDL events on database objects, not on a scheduled time basis, so they cannot be used to perform weekly scheduled maintenance tasks.
• B. SQL Server Alerts respond to specific error conditions or performance thresholds and are not designed to schedule or execute recurring maintenance tasks like index rebuilds or backups.
• D. While PowerShell scripts could technically perform these tasks, they would require additional scheduling infrastructure (e.g., Task Scheduler) and are not a native, integrated SQL Server solution, making them less optimal than a Maintenance Plan.
• E. SQL Server Policy-Based Management system policies are used to enforce configuration standards and compliance conditions on SQL Server objects, not to schedule or execute recurring maintenance operations.

Concept tested. SQL Server Maintenance Plans for scheduled database administration

Reference. https://learn.microsoft.com/en-us/sql/relational-databases/maintenance-plans/maintenance-plans

No community discussion yet for this question.