Configure SQL level logging

Abstract

How to configure xConnect SQL level logging to track SQL commands.

The Extended xConnect Logging feature lets you track SQL commands run in xConnect to improve troubleshooting. It is disabled by default.

The SQL log output contains log entries before each command for the:

  • Stored procedure name.

  • Connection parameters.

  • Values of the command parameters.

The SQL log output contains log entries after each command for the:

  • Stored procedure name.

  • Values of output command parameters.

  • Result returned by the command.

The log output contains a generated GUID, which is a unique trace ID. You can find logs of related commands using this trace ID.

Note

For bulk copy commands, there is only one log entry without a trace ID.

The log output contains:

  • The stored procedure name.

  • The connection parameters (RangeShardMapNameShardLocationReadPreference).

  • The command parameters (for example, contacts\interactions fields values or contacts identifiers).

  • The output parameters.

  • The result returned by the stored procedure.

Enable SQL level logging

To enable SQL level logging:

  • Enable the file \App_Data\Config\Sitecore\Collection\sc.Xdb.Collection.Data.Sql.Logging.xml.disabled by renaming the file to \App_Data\Config\Sitecore\Collection\sc.Xdb.Collection.Data.Sql.Logging.xml.

SQL level log output example

The SQL log output contains log entries before each command. For example:

2021-05-31 18:55:46.400 +03:00 [Verbose] xConnect SQL trace: ab716c04 
{ 
  "StoredProcedure": "[xdb_collection].[SaveContactIdentifiersIndexTvp]", 
  "ConnectionParameters": { 
    "RangeShardMapName": "ContactIdentifiersIndexShardMap", 
    "ShardLocation": "[DataSource=. Database=xcp_Xdb.Collection.Shard0]", 
    "ReadPreference": 0 
  }, 

  "CommandParameters": [ 
    { 
      "Name": "@ContactIdentifiersIndex", 
      "Value": [ 
        { 
          "[Identifier]": "NzNmM2U5ZWItMjNiZi00MDVhLWE3NjItMGQ3YzZlM2FiZTdh", 
          "[Source]": { 
            "IsNull": false, 
            "Value": "Alias", 
            "LCID": 1033, 
            "CultureInfo": "en-US", 
            "CompareInfo": { 
              "Name": "en-US", 
              "LCID": 1033, 
              "Version": { 
                "FullVersion": 393743, 
                "SortId": "00000001-57ee-1e5c-00b4-d0000bb1e11e" 
              } 
            }, 
            "SqlCompareOptions": 25 
          }, 
          "[ContactId]": "8257996d-b0bb-0000-0000-06257344ff88", 
          "[ChangeState]": 2, 
          "[ShardKey]": "TA==" 
        } 
      ] 
    }, 
    { 
      "Name": "@LockDateTime", 
      "Value": "5/31/2021 3:56:45 PM" 
    } 
  ] 
} 

2021-05-31 18:55:46.579 +03:00 [Verbose] xConnect SQL trace: ab716c04 
{ 

  "StoredProcedure": "[xdb_collection].[SaveContactIdentifiersIndexTvp]", 

  "OutputParameters": null, 

  "Result": [] 
} 

Change the log level

The level of extended xConnect SQL log entries is Trace by default.

To change the log level:

  • Open the config file \App_Data\Config\Sitecore\Collection\sc.Xdb.Collection.Data.Sql.Logging.xml and modify the value of the Settings/Sitecore/XConnect/Collection/Services/LoggingDbExecutorOptions/LogLevel node to Trace, Debug, or Information.

    <LogLevel>Trace</LogLevel>     

Change the length of the trace ID

The default length of a trace ID is eight characters. The maximum length is 32 characters.

To change the length of the trace ID:

  • Open the config file \App_Data\Config\Sitecore\Collection\sc.Xdb.Collection.Data.Sql.Logging.xml and modify the value of the

    Settings/Sitecore/XConnect/Collection/Services/LoggingDbExecutorOptions/TraceIdLength node.

    <TraceIdLength>8</TraceIdLength> 

Skip logging of parameters

You can configure a list of command parameters to skip in the logs, for example:

  • Sensitive data (such as username, password, or credit card number).

  • Big length data (such as table-valued parameters).

  • System data (such as sync tokens).

  • Any other irrelevant data that does not need collecting.

To specify parameters to skip in the log:

  • Open the config file \App_Data\Config\Sitecore\Collection\sc.Xdb.Collection.Data.Sql.Logging.xml and modify the Settings/Sitecore/XConnect/Collection/Services/DbExecutorLogBuilder/Options/SkipParameters node, adding a list of parameter names to skip separated by a comma:

    <SkipParameters>UserName,Password</SkipParameters>

Skip logging of results

You can skip the logging of command results. It could include big-length data (for example, collections of contacts, or interactions) or any other non-useful data.

Note

By default, there is no logging of results produced by data extraction and change tracking commands.

To specify command results to skip in the log:

  • Open the config file \App_Data\Config\Sitecore\Collection\sc.Xdb.Collection.Data.Sql.Logging.xml and modify the Settings/Sitecore/XConnect/Collection/Services/DbExecutorLogBuilder/Options/SkipResults node by adding a new child node (with a unique name) and specifying its value, which is the assembly qualified .NET type:

    <SkipResults> 
        <ContactDataExtraction> 
            System.Collections.Generic.IReadOnlyCollection`1[[Sitecore.Xdb.Collection.Model.ContactDataRecord, Sitecore.Xdb.Collection]], mscorlib  
        </ContactDataExtraction> 
    
        <InteractionDataExtraction> 
            System.Collections.Generic.IReadOnlyCollection`1[[Sitecore.Xdb.Collection.Model.InteractionDataRecord, Sitecore.Xdb.Collection]], mscorlib 
        </InteractionDataExtraction> 
    
        <DeviceProfileDataExtraction>
            System.Collections.Generic.IReadOnlyCollection`1[[Sitecore.Xdb.Collection.Model.DeviceProfileDataRecord, Sitecore.Xdb.Collection]], mscorlib 
        </DeviceProfileDataExtraction> 
    
        <ChangeTracking>
            Sitecore.Xdb.Collection.Data.SqlServer.Model.ChangeTracking.Changes, Sitecore.Xdb.Collection.Data.SqlServer 
        </ChangeTracking> 
    </SkipResults>

Configure long-running command logging

Long-running commands are tracked and logged with a warning level and length of time.

To specify the minimum threshold after which the command is considered long-running:

  • Open the config file App_Data\config\sitecore\Collection\sc.Xdb.Collection.Data.Sql.xml and modify the Settings/Sitecore/XConnect/Collection/Services/IDbExecutor/Options/CommandExecutionTimeThreshold node and specify the number of seconds.

    <CommandExecutionTimeThreshold>10</CommandExecutionTimeThreshold>