Articles Posted in the " Monitoring " Category


  • View Multiple System Health Extended Event Files

    Last week I blogged about increasing retention of System Health session files. In this post I will show how you can open multiple files at once and work with the data as one set.

    If you select multiple System Health extended event files (with extension .xel) and click open each file will open in separate window of SQL Server Management Studio (SSMS).

     

    XEFiles1

    You can see here 4 files opened in 4 different windows. Now you cannot sort, group, apply filter, export data from all 4 windows as one data set.

    XEFiles2

     

    For opening all these files in one window …


  • Monitoring Transaction Replication Latency in Real Time

    Monitoring Transaction Replication Latency in Real Time

    This post will give a practical solution with complete code to monitor SQL server transactional replication latency in real time.  My solution assumes you are using common distribution agent.  With minor change you can also implement this for independent distribution agent.

    This solution is a implementation of Kendra Little’s blog post.

    Concept is to create a table with a single row storing current date-time (updated via a sql job every minute).  Replicate that data to your subscriber and measure the difference between current time and time-stamp in subscriber table.

    Before you run any of these code make sure you read …


  • Deadlock Detection and Reporting part-II

    Deadlock Detection and Reporting part-II

    This is a continuation of the series about deadlock.  First part was how you detect deadlock in SQL Server and collect that information.  This part will be about reporting from the collected information.

    On the 2nd step of the job the code below will purse the xml and send .xdl file along with information in tabular format.

    2nd step on the job:

    SET NOCOUNT ON
    SET QUOTED_IDENTIFIER ON
    IF EXISTS ( SELECT *
    FROM tempdb.sys.tables
    WHERE name LIKE '##tmpDeadlockEvents%' )
    DROP TABLE ##tmpDeadlockEvents
    CREATE TABLE ##tmpDeadlockEvents ( [deadlock-list] XML )
    DECLARE @subjectText VARCHAR(250) ,
    @bodyText NVARCHAR(MAX)
    DECLARE @x XML
    DECLARE 

  • Deadlock Detection and Reporting part-I

    Deadlock Detection and Reporting part-I

    This will be a 2 part series about deadlock. First part will be about how you detect deadlock in SQL Server and collect that information. Second part will be about reporting from the collected information.

    Deadlock is a transient event till one of the process becomes a victim. Unless you persist all the details about the deadlock and get notified it is not easy to solve a problem. This process will help you detecting deadlock as it happen, get details about the deadlock and also find the frequency. Eventually you will be able to identify if there is a trend/pattern …