How to run a SQL Server Database Trace using SQL Profiler

Document ID : KB000030111
Last Modified Date : 14/02/2018
Show Technical Document Details

This document describes how to use SQLProfiler to trace SQL DB connections and accesses.

When trying to diagnose problems with DB functionality, one thing that could be tried to troubleshoot the concern is a SQL trace.  The following describes how to configure SQL Server to run such a trace.

  • Identify where there seems to be a problem. 

    It is important to be specific with the problem.  ie:  you cannot create Issues or save Contacts.  That helps to get an idea of what tables to look at, such as the Issue, ca_contact, and usp_contact tables.  Keep in mind there may also be some backend tables that may factor in, such as the event_log or the issalg (issue activity log) tables.
  • Prepare to reproduce the problem.

    For example, to analyse a problem with the creation of an issue, go into SDM, choose to create a new issue, but do not actually save the Issue yet.  The idea is to start the trace right before issue ocurrence.  Depending on where the problem is, you may also consider starting the trace earlier, such as before choosing to create the issue or even logging into SDM.
  • Access SQL Server through SQL Server Management Studio.  Login as the "sa" user or an equivalent, and choose Tools -> SQL Server Profiler
  • Login to SQL Server Profiler. 

    You will see this screen:
    TEC1885971-trace-prop.png
  • Please choose the following:
    • Save to file.  Choose a local file and set the max size to 50 MB (in practice, the file size will be substantially less)
  • Choose the Events Selection tab
    TEC1885971-trace-prop-2.png

    Depending on the nature of the problem, you will need to select the appropriate events and columns.  For instance, if you are suspecting a table INSERT is not committing, you can select the “RowsAffected” column.
  • Once you have selected your events and rows, click on the “Run” button. Transactions similar to below screen start showing up:
    TEC1885971-SQL-pro.png
  • Reproduce the issue as before.
  • Once the issue reproduces, click on the red STOP button (red square).
  • Depending on the nature of the issue, examine the logging to determine activity on the SQL Server. 
    • For instance, if you do not think an update on an existing contact record is taking place, look for an UPDATE entry on either ca_contact or usp_contact.

Note:  While Support may be able to review the logging, your DBA may also be well suited to review the logs and help diagnose any concerns being experienced.