How To: Gather a SQL Trace & Simple Query Suggestions

Version 7


    The following article contains information on manipulating your database.

    It is CRITICAL that you have a full backup of your database prior to making any modifications to the database.

    Failure to backup your database may result in permanent loss of some, or even all data. Please consult with your DBA where applicable.




    This article will cover creating a trace database, capturing a basic SQL trace, ways saving the trace, and a brief overview of querying out useful items.


    Create Trace Database


    After we gather our SQL trace, we will want to save it as a table so we can use query it for relevant data.

    One DB can be created to save all future traces to. You do not need a new DB for every trace.

    • Open SQL Server Management Studio
    • Expand the SQL Servers name
    • Right click 'Databases'
    • Choose 'New Database...'

    • Provide a Database name to identify the new DB (I named my new DB "TraceTables")

    • Click OK to save the new DB


    Now you are ready to capture and save a trace file.



    SQL Trace Configuration


    You can find the SQL trace for your product line in the table below:


    ProductTrace Document
    Endpoint Manager and Endpoint Security (EPM) (Powered by LANDESK)How to: Set up a simple or advanced SQL trace for EPM database issues
    Service Desk & Asset Manager (Powered by LANDESK)How to set up a simple SQL trace for trouble shooting database related issues
    Environment Manager (Powered by AppSense)How to: Configure SQL Tracing for Database Troubleshooting
    IT Service Management (Powered by HEAT)How to: Collect and Filter SQL Profiler Trace of Ivanti Service Manager issues.


    You can follow the steps in this document to gather a "clean" trace: Best practices for gathering a clean and low activity SQL trace


    Save the Trace


    SQL Traces can be saved in a variety of formats. The 2 commonly used for our needs are a Trace Table, or a Trace File.

    • Trace Table - Use this option if looking at the trace locally in your environment.
    • Trace File - Use this option if sending a trace to be reviewed in a different environment.

    Trace Table

    • Click File | Save As | Trace Table...

    • When prompted to connect to a SQL Server, enter your authentication info and click Connect
    • In the Destination Table window, click the Database drop down and select the previously created TraceTable DB
    • Give the trace a unique name in the Table field.
    • Click Ok

    • Open Microsoft SQL Server Management Studio and login
    • Expand the SQL Server name | Databases | Trace Database you created | Tables
    • The trace just saved should be listed as a table now, and available for querying out data



    Trace File

    • Click File | Save As | Trace File...

    • In the Save As window, provide a File Name, and leave the Save as type as a SQL Server Profiler trace files (*.trc) file
    • Click Save

    The saved *.trc file can be opened within SQL Server and re-saved as a Trace Table  for querying data in a different environment.



    Query for Useful Data


    Note: The below information is offered as a means to help understand from a high level querying from the saved trace. The specifics of what query you will vary depending on your needs.


    When tracing SQL queries that are used against your database, they will show up within the tables TextData column of your table.

    This means we want information from the TextData column.


    Select TextData
    From MyTraceEvent


    However SQL traces grow rapidly, and the longer they run, the more data that will be in the trace.

    Example - After running trace during 1 task that lasted several seconds, our sample trace contained >68,000 rows.


    We can narrow down the results for what we are after by using a WHERE clause in our query


    Select TextData
    From MyTraceEvent
    Where TextData like '%Something we care about%'


    This will help reduce the overall results found with the query however it requires that you have something to query for such as a Task IDN, or a Device Name.


    Typically queries submitted by LDMS will look something like this:

    exec sp_executesql....


    We can use this to refine our query results for only those events that may be queries we care about captured during our trace.


    Select TextData
    From MyTraceEvent
    Where TextData like '%exec sp_executesql%'


    Example - In our sample trace of > 68,000 rows, the above query returned 530 rows. Getting better.


    If still uncertain what specifically might be of use, we can narrow it further.

    Looking at the remaining results of the above query, you may notice lots of duplicates.


    We can use the DINSTICT keyword to retrieve only unique records.

    If however you try simply using the DISTINCT operator in the query above, you'll get this error:


    Select Distinct(TextData)
    From MyTraceEvent
    Where TextData like '%exec sp_executesql%'
    Msg 421, Level 16, State 1, Line 2
    The ntext data type cannot be selected as DISTINCT because it is not comparable.


    The TextData column is stored as ntext, which can't be compared with a DISTINCT statement.

    To correct this, we can cast it into a type that is comparable.


    select Distinct CAST ([textdata] as NVARCHAR(MAX)) AS [textdata]
    from HistoryReview
    where textdata like '%exec sp_executesql N%'


    Example - In my trace of > 68,000 rows, the above query reduced it down to ~30 results. Much easier to sort through to identify relevant queries.