Welcome to the Monitoring and Auditing MCQs Page

Dive deep into the fascinating world of Monitoring and Auditing with our comprehensive set of Multiple-Choice Questions (MCQs). This page is dedicated to exploring the fundamental concepts and intricacies of Monitoring and Auditing, a crucial aspect of SQL Server. In this section, you will encounter a diverse range of MCQs that cover various aspects of Monitoring and Auditing, from the basic principles to advanced topics. Each question is thoughtfully crafted to challenge your knowledge and deepen your understanding of this critical subcategory within SQL Server.

Check out the MCQs below to embark on an enriching journey through Monitoring and Auditing. Test your knowledge, expand your horizons, and solidify your grasp on this vital area of SQL Server.

Note: Each MCQ comes with multiple answer choices. Select the most appropriate option and test your understanding of Monitoring and Auditing. You can click on an option to test your knowledge before viewing the solution for a MCQ. Happy learning!

Monitoring and Auditing MCQs | Page 8 of 10

Explore more Topics under SQL Server

01

SQL Server Basics

02

Data Modification and Constraints

03

Beyond Relational

04

Data Definition Language

05

Development with SQL Server

06

Data Connectivity

07

Enterprise Data Management

08

Data Security

09

Performance Tuning and Optimization

10

Business Intelligence and Reporting Services

Q71.

Which of the following script will enable CDC on HumanResources.Shift table?

USE AdventureWorks
GO
EXEC sys.sp_cdc_enable
@source_schema = N'HumanResources',
@source_name = N'Shift',
@role_name = NULL
GO

USE AdventureWorks
GO
EXEC sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name = N'Shift',
@role_name = NULL
GO

USE AdventureWorks
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'HumanResources',
@source_name = N'Shift',
@role_name = NULL
GO

All of the mentioned

Discuss

Answer: (c).USE AdventureWorks
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'HumanResources',
@source_name = N'Shift',
@role_name = NULL
GO

Q72.

Point out the correct statement.

To determine if a database is already enabled, query the is_cdc_enabled column in the sys.database catalog view

Change data capture requires exclusive use of the cdc schema and cdc user

If a change data capture enabled database is dropped, change data capture jobs are not removed

All of the mentioned

Discuss

Answer: (b).Change data capture requires exclusive use of the cdc schema and cdc user

Q73.

Which of the following disable database for Change Data Capture?

EXEC sys.sp_cdc_ensable_db

EXEC sys.sp_disable_db

EXEC sys.sp_cdc_disable_db

All of the mentioned

Discuss

Answer: (c).EXEC sys.sp_cdc_disable_db

Q74.

If you do not want to use a gating role, explicitly set the @role_name parameter to ______________

NULL

None of the mentioned

Discuss

Answer: (b).NULL

Q75.

Point out the wrong statement.

By default, all of the columns in the source table are not identified as captured columns

By default, the change table is located in the default filegroup of the database

The purpose of the named role is to control access to the change data

None of the mentioned

Discuss

Answer: (a).By default, all of the columns in the source table are not identified as captured columns

Q76.

Which of the following query determine sessions that had an empty scan?

SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0

SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count = 0

SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count < 0

SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count > 0

Discuss

Answer: (a).SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0

Q77.

Which of the following script enable a table Without Using a Gating Role?

USE MyDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'MyTable',
@role_name = 1,
@supports_net_changes = 1
GO

USE MyDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'MyTable',
@role_name = 0,
@supports_net_changes = 1
GO

USE MyDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'MyTable',
@role_name = NULL,
@supports_net_changes = 1
GO

None of the mentioned

Discuss

Answer: (c).USE MyDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'MyTable',
@role_name = NULL,
@supports_net_changes = 1
GO

Q78.

Which of the following script enable a Table for all and Net Changes Queries?

\USE MyDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'MyTable',
@role_name = N'MyRole',
@supports_net_changes = 1
GO

\USE MyDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'MyTable',
@role_name = N'MyRole',
@supports_net_changes = 0
GO

\USE MyDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'MyTable',
@role_name = N'MyRole',
@supports_net_changes = NULL
GO

All of the mentioned

Discuss

Answer: (a).\USE MyDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'MyTable',
@role_name = N'MyRole',
@supports_net_changes = 1
GO

Q79.

Upper bound on expected throughput of the capture job is calculated using which of the following computation?

(maxtrans * maxscans) / number of seconds between scans

(maxtrans * maxscans) * number of seconds between scans

(maxtrans * maxscans) – number of seconds between scans

None of the mentioned

Discuss

Answer: (a).(maxtrans * maxscans) / number of seconds between scans

Q80.

The cleanup job is initiated by running the parameterless stored procedure is ___________

sp_MScdc_cleanup_job

sp_cdc_cleanup_change_job

sp_cdc_cleanup_change_tables

none of the mentioned

Discuss

Answer: (a).sp_MScdc_cleanup_job

Page 8 of 10

Welcome to the Monitoring and Auditing MCQs Page

Monitoring and Auditing MCQs | Page 8 of 10

Explore more Topics under SQL Server

SQL Server Basics

Data Modification and Constraints

Beyond Relational

Data Definition Language

Development with SQL Server

Data Connectivity

Enterprise Data Management

Data Security

Performance Tuning and Optimization

Business Intelligence and Reporting Services

Suggested Topics

Computer Architecture

Visual Basic

Computer Hardware

MS Office

Cyber Security

Data Warehousing and OLAP

Systems Analysis and Design Methods

Formal Languages and Automata Theory

Discrete Mathematics