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.
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:
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 theSettings/Sitecore/XConnect/Collection/Services/LoggingDbExecutorOptions/LogLevel
node toTrace
,Debug
, orInformation
.RequestResponseshell<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 theSettings/Sitecore/XConnect/Collection/Services/LoggingDbExecutorOptions/TraceIdLength
node.RequestResponseshell<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 theSettings/Sitecore/XConnect/Collection/Services/DbExecutorLogBuilder/Options/SkipParameters
node, adding a list of parameter names to skip separated by a comma:RequestResponseshell<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.
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 theSettings/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:RequestResponseshell<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 theSettings/Sitecore/XConnect/Collection/Services/IDbExecutor/Options/CommandExecutionTimeThreshold
node and specify the number of seconds.RequestResponseshell<CommandExecutionTimeThreshold>10</CommandExecutionTimeThreshold>