Implementing a SQL Data Warehouse — Question 35

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database named DB1 that has change data capture enabled.
A Microsoft SQL Server Integration Services (SSIS) job runs once weekly. The job loads changes from DB1 to a data warehouse by querying the change data capture tables.
Users report that an application that uses DB1 is suddenly unresponsive.
You discover that the Integration Services job causes severe blocking issues in the application.
You need to ensure that the users can run the application as quickly as possible.
Your SQL Server login is a member of only the ssis_admin database role.
Which stored procedure should you execute?

Answer options

• A. catalog.deploy_project
• B. catalog.restore_project
• C. catalog.stop_operation
• D. sys.sp_cdc_add_job
• E. sys.sp_cdc_change_job
• F. sys.sp_cdc_disable_db
• G. sys.sp_cdc_enable_db
• H. sys.sp_cdc_stop_job

Correct answer: E

Explanation

The correct answer is E, sys.sp_cdc_change_job, which allows you to modify the behavior of the change data capture job to mitigate blocking issues. The other options either deploy or restore projects, disable databases, or add jobs, which do not address the immediate need to alter the change data capture job causing the blocking.