You administer a Microsoft SQL Server 2016 database.

Users report that an application that accesses the database displays an error, but the error does not provide meaningful information.

No entries are found in the SQL Server log or Windows event logs

related to the error.

You need to identify the root cause of the issue by retrieving the error message.

What should you do?

A. Create an Extended Events session by using the sqlserver.error_reported event.

B. Create a SQL Profiler session to capture all

ErrorLog and EventLog events.

C. Flag all stored procedures for recompilation by using sp_recompile.

D. Execute sp_who.

Trapping SQL Server Errors with Extended Events

One very useful usage of Extended Events is the ability to trap SQL Server e

rror without the need to have a server trace running (which, btw, is deprecated), with the additional feature of being able to query the data as soon as it comes in. This means that we a solution to monitor and

trap errors as soon as they happen can be eas

ily created, in order to help developers to fix problems as soon as they are detected. This is really, really, really helpful especially in very big applications, where the code base is quite old and there is no-one really knowing everything of the solutio

n.

To start a Extended Events sessions in order to trap SQL Server errors with severity greater than 10, just run the following script:

CREATE EVENT SESSION [error_trap] ON SERVER

ADD EVENT sqlserver.error_reported

Etc.

References:

http://sqlblog.com/blogs/davide_mauri/archive/2013/03/17/trapping-sql-server-errors-with-extended-events.aspx

You administer a Microsoft SQL Server 2016 database.

The database is currently configured to log ship to a secondary server.

You are preparing to cut over to the secondary server by stopping log-shipping and bringing the secondary database online.

You want to perform a tail-log backup. You need to leave

the primary database in a restoring state.

Which option of the BACKUP LOG command should you use?

A. NO_TRUNCATE

B. NORECOVERY

C. STANDBY

D. FORMAT

You administer a Microsoft SQL Server 2016 database.

You want to make a

full backup of the database to a file on disk.

In doing so, you need to output the progress of the backup.

Which backup option should you use?

A. STATS

B. COMPRESSION

C. CHECKSUM

D. IN IT

You administer a Microsoft SQL Server 2016 database.

You have a SQL Server Agent job instance that runs using the service account.

You have a job step within the job that requires elevated privileges.

You need to ensure t

hat the job step can run using a different user account.

What should you use?

A. a schedule

B. an alert

C. an operator

D. a proxy

You administer a Microsoft SQL Server 2016 database.

You need to ensure that the size of the transaction log file does not exceed 2 GB.

What should you do?

A. Execute

sp_configure max log size, 2G.

B. use the ALTER DATABASE…SET LOGFILE command along with the maxsize parameter.

C. In SQL Server Management Studio, right-click the instance and select Database Settings. Set the maximum size of the file for the transacti

on log.

D. in SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.