SQLTRACE - microsoft/CSS_SQL_Networking_Tools GitHub Wiki

SQL Trace (SQLTRACE)

This is a command-line PowerShell script that will collect Network traces, BID traces, and Auth traces on the current computer to capure low-level logs while a problem is occurring to provide insight into underlying causes. It is a complementary tool to SQLCheck, which collects static settings. While SQLCHECK can be run at any time, SQLTRACE needs to be run prior to issues occurring and left running until the issue occurs to collect log files. Progress entries are written to the PowerShell window and also to SQLTrace.log in the output folder, into which all the other logs are also written.

Installation

This is a PowerShell script that drives built-in Windows data collectors (LOGMAN and NETSH) and must be run on Windows.
Download SQL_TRACE.ZIP and extract SQLTrace.ps1 and SQLTrace.ini to a folder of your choice, e.g. C:\MSDATA.
Note: It requires an Administrative PowerShell command-prompt in order to run. It will not run in CMD.EXE.

Download version 1.0.0234.0 and unzip it into a folder, such as C:\MSDATA.

Download combined SQLTrace 1.0.0234.0 and SQLCheck 1.0.1482.0

Prior version 1.0.0215.0.

Running SQLTrace on PowerShell 7

SQLTrace is written for PowerShell 3.0 - 5.0 that comes with Windows. It uses the Windows-specific Write-EventLog command that is not available in PowerShell 7. To run from a PowerShell 7 Administrator command-prompt, first launch the PowerShell 5.0 command prompt and check the version:

powershell -version 5.0
$PSVersionTable.PSVersion

Usage

SQLTrace is driven by settings in the SQLTrace.ini file. The downloaded INI file is configured to capture a network trace using NETSH, Auth traces, and driver traces for Windows Full Framework .NET drivers for SQL Server (System.Data.SqlClient). See the INI Configuration section below for how to customize the trace.

   .\SQLTrace.ps1 -Help
   .\SQLTrace.ps1 -Setup [-INIFile SQLTrace.ini]
   .\SQLTrace.ps1 -Start [-INIFile SQLTrace.ini] [-LogFolder folderpath] [-StopAfter minutes]
   .\SQLTrace.ps1 -Stop [-INIFile SQLTrace.ini]
   .\SQLTrace.ps1 -Cleanup [-INIFile SQLTrace.ini]
  • Help lists the above usage.
  • Setup is required once before taking BID traces. An application you want to trace must be restarted if it is a web site or a service app or other type of long-running applications. Applications, such as SSIS, which launch a new process for every job, do not need to be restarted.
  • Start begins tracing what is controlled by the INI file. You can optionally specify an alternate INI file. A folder will be created in the current directory with a time-stamp-generated name unless you specify otherwise. You can also specify the trace stop automatically after a certain number of minutes in version 1.0.0171.0 and above.
  • Stop terminates the traces. This may take a while to download event logs. DO NOT terminate the command Window as traces will continue running in the background.
  • Cleanup removes the BID Trace registry keys. This step is optional and will not affect application performance if they are left in place.
  • If you use a custom INI file, make sure to use it with each command.

Taking a Trace

Taking a trace requires several steps:

  1. Customize the SQLTrace.ini file if you want some tracing options other than the default, especially if you want to trace ODBC Drivers or OLE DB Providers. See the INI Configuration section below.
  2. Run the ADMIN PowerShell command-line and CD to the folder containing SQLTrace.ps1.
    CD C:\MSDATA
  3. Initialize the BID Tracing registry, if collecting BID traces. Note: BID Tracing is enabled by default.
    .\SQLTrace.ps1 -setup
  4. Restart the service or application you are tracing. For some applications, such as SSIS packages, a new instance of DTEXEC or ISServerExec are launched when the package is run, so a restart does not make sense.
  5. Start the trace collection.
    .\SQLTrace.ps1 -start
  6. Reproduce the issue or wait for the error to occur.
  7. Stop the trace.
    .\SQLTrace.ps1 -stop
  8. Zip the output folder and upload to Microsoft.

What is Collected

SQLTrace is driven by settings in the SQLTrace.ini file. It collects the following items by default. Changing the INI file can enable or disable specific items for capture.

  • If SQLCheck is installed, run it and collect the output.
  • NETSTAT at the start and end of the trace.
  • TASKLIST at the start and end of the trace.
  • NETSH / LOGMAN network capture.
  • Driver traces for 64-bit and 32-bit .NET apps using System.Data.SqlClient, such as SQL Server Management Studio (SSMS) up through version 18.
  • Auth traces, including Kerberos, LSA, SSL, NTLM.
  • Windows Event logs for the last 24 hours, including Application, Security, System, CAPI2, and Kerberos.
  • SQL Server ERRORLOG files.

INI Configuration

The default SQLTrace.ini file is given below. It is divided into 3 parts: BID Trace, Network Trace, and Auth Trace.

################## BID TRACE ##############
##
## Only traces on Windows
## Does not trace JDBC Drivers
## Does not trace .NET Core Microsoft.Data.SqlClient
## For more information on BID Tracing, including Linux, see: https://github.com/microsoft/CSS_SQL_Networking_Tools/wiki/Collect-a-SQL-Driver-BID-Trace
##

BIDTrace          = Yes                                   # Controls the overall category of collection
BIDWOW            = Both                                  # No | Only | Both     (whether 32-bit apps are traced)
BIDProviderList   = System.Data System.Data.SNI

# BIDProviderList   = OLEDB SQLOLEDB DBNETLIB SQLNCLI11 MSOLEDBSQL MSOLEDBSQL19               # These are the currently supported OLE DB Providers
# BIDProviderList   = ODBC SQLSRV32 DBNETLIB MSODBCSQL11 MSODBCSQL13 MSODBCSQL17 MSODBCSQL18  # These are the currently supported ODBC Drivers

# Combined drivers
# BIDProviderList   = System.Data System.Data.SNI System.Data.OracleClient OLEDB MSDASQL SQLOLEDB DBNETLIB SQLNCLI SQLNCLI10 SQLNCLI11 MSOLEDBSQL MSOLEDBSQL19 ODBC SQLSRV32 MSODBCSQL11 MSODBCSQL13 MSODBCSQL17 MSODBCSQL18

# All BID trace points - not SQL Server BID Tracing - use XEvent tracing
# BIDProviderList   = System.Data System.Data.SNI System.Data.OracleClient OLEDB SQLOLEDB DBNETLIB SQLNCLI SQLNCLI10 SQLNCLI11 MSOLEDBSQL MSOLEDBSQL19 ODBC SQLSRV32 MSODBCSQL11 MSODBCSQL13 MSODBCSQL17 MSODBCSQL18 MSDADIAG ADODB ADOMD BCP BCP10 MSADCE MSADCF MSADCO MSADDS MSADOX MSDAORA MSDAPRST MSDAREM MSDART MSDATL3 ODBCBCP RowsetHelper SQLBROWSER System.Data.Entity SQLJDBC_XA
  • This section is turned on or off by the BIDTrace=Yes|No line.
  • 32-bit apps can be traced via the BIDWOW line. No=64-bit, Only=32-bit, Both=32-bit and 64-bit.
  • What specific drivers are traced is controlled by the BIDProviderList. This is space delimited. There are 5 versions of this line - four are commented out, and only one can be active at a time. You can customize or combine the lines to include only the providers you want, mixing and matching from multiple lists, or create an additional line with your own provider list. If you are unsure of the driver being used, use the "Combined Drivers" list.
  • BID Traces also capture Name Resolution/DNS events as these are often a cause for a slow connection or timeout or of a network connection that "appears" to terminate early.
################## NETWORK TRACE ##########
##
## Only traces on Windows
## For more information on network tracing, see: https://github.com/microsoft/CSS_SQL_Networking_Tools/wiki/Collect-a-Network-Trace
##

NETTrace          = Yes      # Controls the overall category of collection
NETSH             = Yes      # Default for Windows; built-in                                                               Supports TruncatePackets, FilterString, TCPEvents
PSNETCAPTURE      = No       # PowerShell New-NetEvent NDIS capture; built-in                                              Supports TruncatePackets, FilterString, TCPEvents
NETMON            = No       # Must be installed separately                                                                Supports TruncatePackets, FilterString
WIRESHARK         = No       # WireShark can see VPN traffic, must be installed separately                                 Supports TruncatePackets, FilterString
Pktmon            = No       # Windows 2019 and later, Windows 11 and later, Windows 10 builds equivalent to Windows 2019. Supports TruncatePackets, FilterString, TCPEvents
TruncatePackets   = No       # Whether to capture full packet payload or just the beginning portion
TCPEvents         = NO       # For NETSH, PSNETCAPTURE, PktMon: collect TCPIP, Winsock AFD, Firewall, and DNS events

# FilterString      = IPv4.Address=10.10.10.10 Ethernet.Type=IPv4                    # NETSH        filter string example; no port filtering; single = used in comparisons; For help: netsh trace show CaptureFilterHelp
# FilterString      = -EtherType 0x0800 -IPAddresses 10.10.10.10 -IpProtocols 6,17   # PSNETCAPTURE filter string example; 0x800 = IPV4, 0x86DD = IPV6, 6 = TCP, 17 = UDP - omit to not filter on a particular item
# FilterString      = ipv4.address==10.10.10.10 and tcp.port==1433                   # NETMON       filter string example; double == used in comparisons
# FilterString      = -f "host 10.10.10.10 and port 1433"                            # WIRSHARK     filter string example; not expression-based; for help run Wireshark | Capture | Capture Filters ... menu
# FilterString      = -t tcp -i 10.10.10.10 -p 1433                                  # PKTMON       filter string example; not expression-based; spaces separate multiple values for IP addresses and ports
  • NETTrace controls the overall section.
  • Specific network trace providers can be enabled on the following lines. We recommend NETSH except for special circumstances.
  • PSNETCAPTURE captures NDIS traffic using the NetEventSession PowerShell module.
  • Pktmon is only available on Window 2019 and later and equivalent versions of Windows 10 and 11.
  • NETMON and/or WireShark must be installed separately in order to be used.
  • Selecting more than one network trace provider is allowed, but not recommended because of performance and storage issues or other unexpected issues.
  • Do not save trace data to a network share as that adds to the data collected and may overload the capture.
  • The TruncatePackets setting causes the network trace to only capture the beginning ~ 200 bytes of each packet to conserve disk space during long captures. This applies to all network providers.
  • TCPEvents controls whether additional TCP events are captured with NETSH, PSNETCAPTURE, or PKTMON go give insight into internal stack and firewall issues.
  • FilterString allows you to filter while capturing. Each provider has a different format for the string and other limitations, e.g. NETSH and PSNETEVENT do not allow filtering on port numbers. Copy from the examples (minus the # symbol) and change the values that you want to enable filtering during the packet capture. Omit parameters that you are not interested in.
################## AUTH TRACE #############
##
## Equivalent to parts of the Directory Services Auth trace script, but more focused
##

AUTHTrace      = Yes    # Controls the overall category of collection
ssl            = Yes    # Certificate logs
credssp_ntlm   = Yes    # NTLM logs
KERBEROS       = Yes    # Kerberos logs
LSA            = Yes    # Local security authority logs
  • AuthTrace controls the overall section.
  • Various security providers can be individually enabled or disabled, but this is not recommended.
################## MISC #############
##
## Other Collection Settings
##

FlushTickets      = No     # Are we going to flush Kerberos tickets; not needed for pure network issues
EventViewer       = Yes    # Application, System, and Security event logs
SQLErrorLog       = Yes    # All ERRORLOG.* files for all SQL instances on the machine.
SQLXEventLog      = No     # All *.XEL XEvent logs for all SQL instances on the machine. This can be very large.
DeleteOldFiles    = No     # Only keep the last so many minutes and number of trace files - this affects collectors and is a minimum amount.
MinFiles          = 20     # Only delete files beyond this limit that are more than MinMinutes old
MinMinutes        = 60     # Only delete files older than this number of minutes (LastWriteTime) has elapsed
SQLCheck          = Yes    # Run SQLCheck if it's in the SQLTrace.ps1 folder and redirect the output to the log folder
SQLCheckPath      = .\     # SQLCheck.exe location - defaults to the current folder
  • The FlushTickets setting controls whether Kerberos tickets get flushed. This can add up to a minute or two of start-up time and is not required for plain network errors.
  • The EventViewer setting collects the last 24 hours of various event logs. This can add a minute or two of shut-down time.
  • The SQLErorLog setting collects the SQL Server ERRORLOG files.
  • The SQLXEventLog setting collects the SQL Server XEvent files. Off by default.
  • The DeleteOldfiles setting deletes old chained files based on the settings below. This is to conserve disk space if the computer is low on free space. You need to terminate the capture in a timely manner to prevent losing data. You may want to adjust the cut-off numbers depending on how fast your traces increase and how much free disk space you have. The evaluation is done every 5 minutes, files may grow between evaluations.
  • The MinFiles setting prevents deleting old files until we reach this threshold, i.e. once we have 21 or more files.
  • The MinMinutes setting prevents deleting files that have been updated less than 60 minutes ago.
  • The SQLCheck setting is whether to attempt to run SQLCheck.exe if it is present in the folder designated by SQLCheckPath. This defaults to the current path but could be set to a specific drive and folder.

Disclaimers

Note: This tool does not trace .NET Core drivers, JDBC Drivers, or anything on Linux or MAC operating systems.
Note: This tool captures various logs. It does not change any system settings.
Note: This tool does save the log files to the current folder or a specified folder.
Note: This tool does not communicate with any external systems.
Note: This tool does not make a connection to SQL Server, IIS, or other application services.

For best results, run this tool on the SQL Server and on the client machine.