ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-764 All Questions

View all questions & answers for the 70-764 exam
Go to Exam

Exam 70-764 topic 1 question 74 discussion

Actual exam question from Microsoft's 70-764
Question #: 74
Topic #: 1
[All 70-764 Questions]

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.
Show Suggested Answer Hide Answer
Suggested Answer: A 🗳️
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
by TheSwedishGuy at Jan. 24, 2020, 2:30 p.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
TheSwedishGuy
5 years, 4 months ago
sp_recompile Causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run. In a SQL Server Profiler collection, the event SP:CacheInsert is logged instead of the event SP:Recompile.
upvoted 1 times
...
TheSwedishGuy
5 years, 5 months ago
Since SQL Server Profiler is deprecated, there is only one available answer.
upvoted 1 times
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel