Skip to content

SQLTRACE

Jump to bottom
Malcolm Stewart edited this page Aug 16, 2022 · 51 revisions

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 TBD

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.

  • 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 .NET apps using System.Data.SqlClient.
  • Auth traces, including Kerberos, LSA, SSL, NTLM.
  • Windows Event logs for the last 24 hours, including Application, Security, System, CAPI2, and Kerberos.

INI Configuration

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 64-bit driver traces for Windows Full Framework .NET drivers for SQL Server (System.Data.SqlClient).

     TBD

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.

Clone this wiki locally