The purpose of this article is to explain the process of accessing/configuring and running a SQL trace to provide to Support to aid the investigation into a suspected issue. You may also want to use the attached trace templates (see the section "Using Trace Templates").
Why running a SQL trace is helpful
- An SQL trace can be helpful when it comes to troubleshooting why an outcome is occurring in the Snow Solution.
- The process captures the back end SQL statements that are running during a set of steps being carried out.
- A simple example:
- Investigating why a report is not working as expected: the SQL statement would help you to understand how it's being ran at the database level.
How to set up the trace
Define the criteria in the profiler for the specific application we're seeing the issue with (for example Snow Inventory or Snow License Manager) before we carry out any steps part of the system we see fault.
-- Please be aware your SQL user account will need to have permissions set to be able to access the SQL Server Profiler tool.
1. Load up the Microsoft SQL Server Management Studio .
2. Select Tools and then select SQL Server Profiler:
3. Once the tool has loaded, name the trace:
4. Navigate to the Event Selection tab:
5. From this screen we need to start outlining our criteria. De-select the following items:
6. Once the above has been de-selected, we need to add some additional event types. First select the Show All events tick box on the right hand side of the window:
7. Once selected, navigate to the following sub sections and add in these additional events:
- Errors and Warnings:
- Stored Procedures:
8. Untick the Show All events tick box. You will be left with a trace configured like this:
9. Apply some additional filtering to minimize the number of irrelevant rows being collected in the trace. To ensure we can filter on columns such as the database name, ensure you tick Show all columns and then select Column Filters button on the right hand side:
10. You will be presented with the following window:
Here we can filter as an example on the database name. If we were only interested in what was occurring in the Snow License Manager database we could navigate to DatabaseName and state like SnowLicenseManager and not like SnowInventory as shown below:
A further example would be filtering on TextData which is not necessary relevant to the troubleshooting we're conducting:
The relevant Not Like criteria are the following:
%tblRebusMessages%
%sp_reset_connection%
%EventStorePassword%
%WebApplicationConfigGet%
%ReportExportScheduleDueList%
11. Once configured press OK and then trigger the trace by selecting Run:
12. You will be presented with the following window:
13. If any actions occur on the database, they are recorded within the trace. This will also record the Stored Procedure used. For example:
14. Once you have carried out the reproduction steps to capture, stop the trace and save the output for further analysis by Support:
Using Trace Templates
- Please also see attached to this article some SQL Trace templates that have been pre-prepared for use.
- The only difference between these is the LIKE term on the target database you want to trace depending on the application you are troubleshooting.
- If you need guidance on how to import the trace template into your environment, please refer to the following Microsoft page:
