-
Notifications
You must be signed in to change notification settings - Fork 48
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.
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.0150.0 and unzip it into a folder, such as C:\MSDATA. New SQLTrace.ini format. See below.
Prior version 1.0.0091.0.
SQLTrace is written for PowerShell 3-5 and 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
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 64-bit 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]
.\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.
- Stop terminates the traces. This may take a while as NETSH can take 2-5 minutes to stop and organize the traces. 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 requires several steps:
- 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.
- Run the ADMIN PowerShell command-line and CD to the folder containing SQLTrace.ps1.
CD C:\MSDATA - Initialize the BID Tracing registry, if collecting BID traces. Note: BID Tracing is enabled by default.
.\SQLTrace.ps1 -setup - 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.
- Start the trace collection.
.\SQLTrace.ps1 -start - Reproduce the issue or wait for the error to occur.
- Stop the trace.
.\SQLTrace.ps1 -stop - Zip the output folder and upload to Microsoft.
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.
- 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).
- 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.
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
- 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 3 versions of this line - two 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.
################## 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
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: collect TCPIP and Winsock AFD events
# FilterString = IPv4.Address=10.10.10.10 Ethernet.Type=IPv4 # NETSH filter string example; no port filtering; only a single = used in comparisons; For help: netsh trace show CaptureFilterHelp
# 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; no = signs; 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.
- Pktmon is not currently implemented.
- 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.
- 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 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 does 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.
################## 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; may take a while
EventViewer = Yes # Application, System, and Security event logs
SQLErrorLog = Yes
SQLXEventLog = No
DeleteOldFiles = No # Only keep the last so many minutes and number of trace files - this affects all chained collectors
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
- The FlushTickets setting controls whether Kerberos tickets get flushed. This can add up to a minute of start-up time and is not required for plain network errors.
- The EventViewer setting collects the last 24 hours of various event logs.
- 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 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.
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.