SQL Server 2012 Extended Events Add-in to Manage 2008/R2 Instances

Extended Events are a powerful new way of troubleshooting problems with SQL Server, and the addition of UI support in SQL Server 2012 Management Studio has helped increase the awareness and usage of this feature. One short-coming is that the new UI for Extended Events only works for SQL Server 2012 instances leaving administrators that manage multiple versions, no way to explore the option of using Extended Events on their SQL Server 2008/R2 instances.  The Extended Event Manager Add-in that I wrote for SQL Server 2008/R2 is not compatible with SQL Server 2012 and because of the new UI I never planned on making it function in Management Studio 2012.  However, I’ve had a lot of requests for this to provide backwards compatibility, and I’ve been reminded by Erin every time she presents on Extended Events about how useful it would be if I would make the Add-in work in Management Studio 2012.

Today, we’re releasing a new SQL Server 2012 Extended Events Add-in  to provide backwards compatibility with SQL Server 2008 and SQL Server 2008R2 for Extended Events by providing the following features:

  • View Extended Events Metadata for all Available Objects
  • View event sessions
  • Start/Stop event sessions
  • Create new event sessions
  • Alter event sessions
  • Drop event session
  • Script all operations
  • View target data for active event sessions
  • Configurable UI options

The SQL Server 2012 Extended Events Add-in is built on the code for the 2008 version of the add-in (available on Codeplex) with updates to resolve known bugs and allow integration in SQL Server 2012 Management Studio. A full walkthrough of the UIs in the add-in can be found on my blog post An XEvent a Day (12 of 31) – Using the Extended Events SSMS Addin.

Many thanks go out to Dan Taylor (@DBABullDog) and Paul Timmerman (@mnDBA) for beta testing the add-in over the last week, providing feedback and bug reports that were critical to getting the add-in modified for SQL Server 2012.  Also thanks to Erin (@erinstellato) for the constant reminders that this would be a useful feature.

13 thoughts on “SQL Server 2012 Extended Events Add-in to Manage 2008/R2 Instances

  1. Hi Jonathan,

    This is quite useful. However in my case ‘Extended Event Information’ window is empty. Could you tell me why this could be please ?

    Thanks
    Raj

  2. I uninstalled both exteneded event SSMS 2008 and 2012 add in and installed it again, now it works like treat !!! Thanks Jonathan.

    Thanks
    Raj

  3. Hi Jonathan,

    Is there way to capture the Lock Timeout error using SQL 2008 Extended events? I would like to find out what is the root cause of this error. We are having this issue in one of our busy production system once in day in random. The web sphere app Server driver has a default lock timeout property for 2000 ms. We cannot change this value. It is very difficult troubleshoot this issue because it last only for 2 sec in random intervals.

    Could you please help me

    Regards
    Praveen

    1. Hey Praveen,

      You could catch the error, but that’s not going to help you identify the root cause because it doesn’t tell you who the blocker is, which is the root cause of the problem. Polling DMVs is going to be your best way of finding the root cause.

  4. Hi, I have SQL Server 2012 Dev edition on my Windows 8 laptop. After installing SQL 2012 Extended events add-i SQL 2008/R2, I found in SSMS “Show Extended Event Session Explorer” under the View Menu. When I click on it, SQL Server Management Studio crashed saying “this application has stopped working”.

    Any idea? Am I supposed to install some other components first? As I don’t have SQL 2008 installad, can I run Extended Event directly on SQL 2012?

    Many thanks

      1. See error log below from event viewer. I have tried to uninstall and reinstall mdac but the error is the same. Many thanks.

        Log Name: Application
        Source: Application Error
        Date: 31/07/2013 14:33:51
        Event ID: 1000
        Task Category: (100)
        Level: Error
        Keywords: Classic
        User: N/A
        Computer: Ivan_S7_Laptop
        Description:
        Faulting application name: Ssms.exe, version: 2011.110.2100.60, time stamp: 0x4f35e2d9
        Faulting module name: KERNELBASE.dll, version: 6.2.9200.16451, time stamp: 0x50988950
        Exception code: 0xe0434352
        Fault offset: 0x00014b32
        Faulting process ID: 0x1bdc
        Faulting application start time: 0x01ce8df1a23c60c4
        Faulting application path: C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe
        Faulting module path: C:\windows\SYSTEM32\KERNELBASE.dll
        Report ID: d5dafef3-f9e5-11e2-be85-c8f733e1ca52
        Faulting package full name:
        Faulting package-relative application ID:
        Event Xml:
        ……………
        …………..

        1000
        2
        100
        0x80000000000000

        9072
        Application
        Server\110\Tools\Binn\ManagementStudio\Ssms.exe
        C:\windows\SYSTEM32\KERNELBASE.dll
        d5dafef3-f9e5-11e2-be85-c8f733e1ca52

  5. Hi,

    I’m not a database person but my boss wants me to monitor the servers’ event log. I would like to know if this add in will centralize all the events from all servers and store in the SQL database?? If yes, do you have any procedures can help me to set it up?? If not, do you have any suggestions??

    Thanks and sorry for the stupid questions!!!!

    Hons.

    1. Hey Hons,

      No that is not the purpose of this addin. There are plenty of ways you could go about doing what you are asking about, most can be found by searching online, but rather than reinventing the wheel, consider looking at the third party monitoring tools like SQL Sentry that would centralize logs and so much more in a dashboard for you.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.