Exam 70-767 topic 1 question 57 discussion

Actual exam question from Microsoft's 70-767

Question #: 57
Topic #: 1

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?

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

Show Suggested Answer

Suggested Answer: E 🗳️
sys.sp_cdc_change_job modifies the configuration of a change data capture cleanup or capture job in the current database.
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-change-job-transact-sql?view=sql-server-2017

by Dieter at May 17, 2020, 3:30 p.m.

Comments

Submit Cancel

DudeHere

4 years, 7 months ago

I'm going with C. 1) Membership to the ssis_admin database role 2) Stops a validation or instance of execution in the Integration Services catalog. https://docs.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-stop-operation-ssisdb-database?view=sql-server-ver15 Wrong answers: Changes to a job do not take effect until the job is stopped by using sp_cdc_stop_job and restarted by using sp_cdc_start_job. Permissions Requires membership in the db_owner fixed database role. https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-change-job-transact-sql?view=sql-server-2017

upvoted 2 times

...

melvin9900

4 years, 9 months ago

catalog.stop_operation - Stops a validation or instance of execution in the Integration Services catalog, This stored procedure requires one of the following permissions: READ and MODIFY permissions on the validation or instance of execution Membership to the ssis_admin database role Membership to the sysadmin server role

upvoted 1 times

...

Anette

4 years, 9 months ago

C. catalog.stop_operation Stops a validation or instance of execution in the Integration Services catalog E. sys.sp_cdc_change_job Modifies the configuration of a change data capture cleanup or capture job in the current database. H. sys.sp_cdc_stop_job Stops a change data capture cleanup or capture job for the current database. I ma going with C, looks the most appropriate answer here

upvoted 1 times

...

Kartheek285

4 years, 9 months ago

The correct answer must be C, it stops the execution of SSIS job using that proc. What H option does is - Stops a change data capture cleanup or capture job for the current database.

upvoted 2 times

...

othman_ee

4 years, 10 months ago

Correct answer C

upvoted 3 times

...

ThomasKwakman

5 years ago

"Your SQL Server login is a member of only the ssis_admin database role." How is it possible then to execute the sp_cdc_change_job when it needs the db_owner role? Is there something that I am missing? see https://docs.microsoft.com/fr-fr/sql/relational-databases/system-stored-procedures/sys-sp-cdc-change-job-transact-sql?view=sql-server-ver15#permissions

upvoted 3 times

...

Dieter

5 years ago

In my opinion the answer should be H (sys.sp_cdc_stop_job) to fulfill requirements that the users can access the application as quickly as possible. The task does not say how to change the cdc job. Any thoughts to that?

upvoted 1 times

Dieter

5 years ago

Retought my answer: since "Your SQL Server login is a member of only the ssis_admin database role." it has to be catalog.stop_operation. https://docs.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-stop-operation-ssisdb-database?view=sql-server-2017

upvoted 9 times

...