How to run a SQL Server Database Trace using SQL Profiler
search cancel

How to run a SQL Server Database Trace using SQL Profiler

book

Article ID: 30111

calendar_today

Updated On:

Products

SUPPORT AUTOMATION- SERVER CA Service Desk Manager - Unified Self Service CA Service Desk Manager CA Service Management - Asset Portfolio Management CA Service Management - Service Desk Manager

Issue/Introduction

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

Environment

CA Service Desk Manager 17.3.X

Resolution

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:

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

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.

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.