How To: Read a SQL Trace When Troubleshooting LANDESK

Version 23

    Verified Product Versions

    Endpoint Manager 2016.x

    What is a SQL trace?:

     

    A SQL trace, as it's a name implies, is achieved by "tracing" the activities of the SQL Server Database Engine. This is a very useful tool to further narrow issues that may not be evident in the GUI or logs. The trace results can be stored in either a file or a SQL Server table. Any user requiring to run a SQL Trace using SQL Server Profiler must have the ALTER TRACE permissions on the SQL Server instance. The following document describes how to setup a SQL trace for issues related to LANDESK:

     

    https://community.landesk.com/support/docs/DOC-23558

     

    How to analyze a SQL Trace:

     

    When you open a trace by using SQL Server Profiler, the trace file does not need to have the .trc file extension if the file was created by either SQL Server Profiler or SQL Trace system stored procedures. If the SQL trace happens to be very large (1gb+) , it can also be save into a database table. You should then be able to open this file up in the SQL Server profiler. Here is a few sample results from two traces:

     

     

     

    Example 1:

     

    (Please click on screenshot for detailed view)

    SQL trace errors.PNG

     

     

    If you look closely you will see the error sticks out like a sore thumb.

     

    sore_thumb-808.jpg

    The particular example cited in the screenshot is related to a user not being able to delete certain machines from the console. You will notice the error in this instance is "The DELETE statement conflicted with the REFERENCE constraint "R_DataProtClient". The conflict occurred in database "LDMS", table "dbo.DataProtClient", column 'Computer_Idn'." which references a database conflict.

     

    Example 2:

     

    (Please click on screenshot for detailed view)

    SQL trace errors 2.PNG

     

    As a general rule of thumb, normal operations are displayed in Black. Errors, conflicts, and exceptions are generally listed in Red. In this particular instance the issue seems to be related SQL attempting to perform an operation but the request timing out.

     

     

     

     

    Data Columns:

     

    The following table describes the Microsoft SQL Server Profiler data columns. These are the columns that are available by default. Each one describes events that are returned when the trace runs.

     

     


    Data column
    Column Number
    Description
    Application Name110Name of the client application that created the connection to an instance of SQL Server. This column is populated with the values passed by the application rather than the displayed name of the program.
    Binary Data2Binary value dependent on the event class captured in the trace.
    ClientProcessID19ID assigned by the host computer to the process where the client application is running. This data column is populated if the client process ID is provided by the client.
    Column Permissions44Indicates whether a column permission was set. Parse the statement text to determine which permissions were applied to which columns.
    CPU 18Amount of CPU time (in milliseconds) used by the event.
    Database ID13ID of the database specified by the USE database statement or the default database if no USE database statement has been issued for a given instance. SQL Profiler displays the name of the database if the Server Name data column is captured in the trace and the server is available. Determine the value for a database by using the DB_ID function.
    DatabaseName35Name of the database in which the user statement is running.
    DBUserName140SQL Server user name of the client.
    Duration 13Amount of time (in milliseconds) taken by the event.
    End Time 15Time at which the event ended. This column is not populated for event classes that refer to an event starting, such as SQL:BatchStarting or SP:Starting.
    Error31Error number of a given event. Often this is the error number stored in sysmessages.
    EventClass127Type of event class captured.
    EventSubClass121Type of event subclass, providing further information about each event class. For example, event subclass values for the Execution Warning event class represent the type of execution warning:

    1 = Query wait. The query must wait for resources (for example, memory) before it can execute.
    2 = Query time-out. The query timed out while waiting for required resources to execute. This data column is not populated for all event classes.

    FileName36The logical name of the file being modified.
    Handle33Integer used by ODBC, OLE DB, or DB-Library to coordinate server execution.
    Host Name18Name of the computer on which the client is running. This data column is populated if the host name is provided by the client. To determine the host name, use the HOST_NAME function.
    Index ID24ID for the index on the object affected by the event. To determine the index ID for an object, use the indid column of the sysindexes system table.
    Integer Data25Integer value dependent on the event class captured in the trace.
    LoginName11Name of the login of the user (either SQL Server security login or the Microsoft Windows® login credentials in the form of DOMAIN\Username).
    LoginSid141Security identification number (SID) of the logged-in user. You can find this information in the sysxlogins table of the master database. Each SID is unique for each login in the server.
    Mode32Integer used by various events to describe a state the event has received or is requesting.
    NestLevel29Integer representing the data returned by @@NESTLEVEL.
    NT Domain Name17Microsoft Windows NT® 4.0 or Windows 2000 domain to which the user belongs.
    NT User Name16Windows NT 4.0 or Windows 2000 user name.
    Object ID22System-assigned ID of the object.
    ObjectName34Name of the object being referenced.
    ObjectType28Value representing the type of the object involved in the event. This value corresponds to the type column in sysobjects.
    Owner Name37Database user name of the object owner.
    Permissions19Integer value representing the type of permissions checked. Values are:

    1 = SELECT ALL
    2 = UPDATE ALL
    4 = REFERENCES ALL
    8 = INSERT
    16 = DELETE
    32 = EXECUTE (procedures only)
    4096 = SELECT ANY (at least one column)
    8192 = UPDATE ANY
    16384 = REFERENCES ANY

    Reads16Number of logical disk reads performed by the server on behalf of the event.
    RoleName38Name of an application role being enabled.
    Server Name126Name of the instance of SQL Server being traced.
    Severity20Severity level of an exception.
    SPID112Server Process ID assigned by SQL Server to the process associated with the client.
    Start Time114Time at which the event started, when available.
    State30Equivalent to an error state code.
    Success23Represents whether the event was successful. Values include:

    1 = Success.
    0 = Failure

    For example, a 1 means success of a permissions check and a 0 means a failure of that check.

    TargetLoginName42For actions which target a login (for example, adding a new login), the name of the targeted login.
    TargetLoginSid43For actions which target a login (for example, adding a new login), the SID of the targeted login.
    TargetUserName39For actions which target a database user (for example, granting permission to a user), the name of that user.
    TextData1Text value dependent on the event class captured in the trace. However, if you are tracing a parameterized query, the variables will not be displayed with data values in the TextData column.
    Transaction ID4System-assigned ID of the transaction.
    Writes17Number of physical disk writes performed by the server on behalf of the event.

     

     

    Tips and Tricks:

     

    As a trace can sometimes be enormous, you can CTRL+F when looking for a specific user,group,error, or anything that could be relevant to the issue: 

     

    (Please click on screenshot for detailed view)

    sql trace errros 3.PNG

     

    As mentioned earlier in the article, this can also be saved as a trace table:

     

    (Please click on screenshot for detailed view)
    sql trace errors 5.jpg

    A separate Database can be created to store trace tables. Once that is done, you can configure those tables on the newly created Database for this purpose:

     

    (Please click on screenshot for detailed view)

     

    It can then be viewed in SQL and queries can be run like any other standard database:

     

    (Please click on screenshot for detailed view)

    sql trace errors 6.jpg

     

     

     

    Conclusion:

     

     

    The error contained within the trace can be very helpful when diagnosing an issue, some of the errors can be found here in our community and on Microsoft's knowledgebase as well. They can also be a very helpful when working with a LANDesk engineer on an issue