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