How to identify root cause when there is no SQL error logs or Windows error logs?

Problem: How to identify root cause when there is no SQL error logs or Windows error logs?

Solution: Extended events Starting SQL Server 2012

Extended events can be used to capture the error logs and tracing sessions and queries as they are architect ed to log with minimal impact of the server while tracing, Whereas SQL Server profile can cause a performance hit and take a toll on the resources when used for long time.

There are many events that can be captured by using an extended events session.

    1.     Application Timeouts

    2.     Deadlocks

    3.     Blocking Queries

    4.     Long Running Queries

Extended Events have four components:

       ·         Events packages

       ·         Events Sessions

       ·         Events Engine

       ·         Events Targets

Event packages or sessions can be created both by Wizard and T-SQL.

Each sessions capture target into an XML where errors or events triggered can be captured during the session.

Example:

In this example, I will show we can obtain the target data of an extend event through the extended event sessions:

Step 1: Expand the management folder in the SSMS and toggle over the extended sessions

Step 2: Click on the sessions folder view on the target data folder to get the sessions, errors and log.

Step 3: Click on the target data XML and refresh for every time interval to capture the sessions live data.

 You can expand the XML and look for specific text