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.
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:
|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.
- 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
- 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.
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:
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.