What should you do?

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.

View Answer

Answer: A

Explanation:

Trapping SQL Server Errors with Extended Events

One very useful usage of Extended Events is the ability to trap SQL Server error 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 easily 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 solution.

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