Questions and answers for exam Preparation

MCQs

Total Questions : 12 | Page 1 of 2 pages

Question 1. __________ gives DBAs an option to shut down the server in case of an audit log failure.

Shut down server on partial failure
Shut down server on audit log failure
Shut down server on complete failure
None of the mentioned

Discuss Question

Answer: Option B. -> Shut down server on audit log failure

Queue delay (in milliseconds) defines the time interval after which the events will be written in the logs.

Question 2. ______ waits occur when a worker requests access to a resource that is not available because the resource is being used by some other worker or is not yet available.

Resource
Queue
Lock
External

Discuss Question

Answer: Option A. -> Resource

Examples of resource waits are locks, latches, network and disk I/O waits. Lock and latch waits are waits on synchronization objects.

Question 3. Point out the wrong statement:

Users with the ALTER ROLE permission can create server audit specifications and bind them to any audit
SQL Server audit uses Extended Events to help create an audit
You can have multiple audits per SQL Server instance
You can create one server audit specification per audit

Discuss Question

Answer: Option A. -> Users with the ALTER ROLE permission can create server audit specifications and bind them to any audit

Users with the ALTER ANY SERVER AUDIT permission can create server audit specifications and bind them to any audit.

Question 4. 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 Question

Answer: Option B. -> Change data capture requires exclusive use of the cdc schema and cdc user

If either a schema or a database user named cdc currently exists in a database, the database cannot be enabled for change data capture until the schema and or user are dropped or renamed.

Question 5. Which of the following is catalog view used for change tracking ?

sys.change_tracking_tables
sys.change_tracking_views
sys.change_tracking_columns
None of the mentioned

Discuss Question

Answer: Option A. -> sys.change_tracking_tables

sys.change_tracking_tables returns one row for each database in the instance of SQL Server that has change tracking enabled.

Question 6. Which of the following tool is used for purpose of data auditing for SQL Server only?

ApexSQL
SQL Ninja
SQL Audit
Idera

Discuss Question

Answer: Option A. -> ApexSQL

ApexSQL Audit is a tool for auditing SQL Server data changes specifically for Microsoft SQL Server.

Question 7. Which of the following catalog view is used for SQL Server Extended Events ?

sys.server_sessions
sys.server_event_sess
sys.server_event_session_actions
All of the mentioned

Discuss Question

Answer: Option C. -> sys.server_event_session_actions

sys.server_event_session_actions returns a row for each action on each event of an event session.

Question 8. Point out the wrong statement :

When the query processor parameterizes the query, the parameter can appear in the audit event log instead of the column values of the query
To access the triggering event we can use the EventData function in our DDL trigger
AUDIT_DATABASE_CHANGE_GROUP event is raised whenever CREATE DATABASE AUDIT is issued
None of the mentioned

Discuss Question

Answer: Option C. -> AUDIT_DATABASE_CHANGE_GROUP event is raised whenever CREATE DATABASE AUDIT is issued

AUDIT_ CHANGE_GROUP event is raised whenever CREATE SERVER AUDIT is issued.

Question 9. Which of the following stored procedure will stop the trace ?

sp_trace_create.
sp_trace_setfilter
sp_trace_setstatus
sp_stop_status

Discuss Question

Answer: Option C. -> sp_trace_setstatus

Stop and Close the the trace with sp_trace_setstatus.

Question 10. Point out the wrong statement :

If a filter is set, all events of the selected event classes are returned in the trace output
A trace captures data based on selected event classes, data columns, and filters. For example, you can create a trace to monitor exception errors
A data column is an attribute of an event classes captured in the trace
All of the mentioned

Discuss Question

Answer: Option A. -> If a filter is set, all events of the selected event classes are returned in the trace output

When you create a trace or template, you can define criteria to filter the data collected by the event. To keep traces from becoming too large, you can filter them so that only a subset of the event data is collected.