---
title: "Configure SQL level logging"
slug: "configure-sql-level-logging"
abstract: "How to configure xConnect SQL level logging to track SQL commands."
---
# Configure SQL level logging
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 (`RangeShardMapName`, `ShardLocation`, `ReadPreference`).
- 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:
```text
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`.
```xml
Trace
```
## 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.
```xml
8
```
## 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:
```xml
UserName,Password
```
## 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:
```xml
System.Collections.Generic.IReadOnlyCollection`1[[Sitecore.Xdb.Collection.Model.ContactDataRecord, Sitecore.Xdb.Collection]], mscorlib
System.Collections.Generic.IReadOnlyCollection`1[[Sitecore.Xdb.Collection.Model.InteractionDataRecord, Sitecore.Xdb.Collection]], mscorlib
System.Collections.Generic.IReadOnlyCollection`1[[Sitecore.Xdb.Collection.Model.DeviceProfileDataRecord, Sitecore.Xdb.Collection]], mscorlib
Sitecore.Xdb.Collection.Data.SqlServer.Model.ChangeTracking.Changes, Sitecore.Xdb.Collection.Data.SqlServer
```
## 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.
```xml
10
```