SQLTreeo Job Timeline · Troubleshooting

The diagnostics report

The plain-text diagnostics report: where it is, what is in it, what is never in it, replacing names by placeholders, and how to send it.

SQLTreeo Job Timeline can write a diagnostics report that explains what it shows. It is one plain-text file with what the application read from every server, what a live read-only probe of each server returned, and the recent events. Use it when you see something you did not expect, such as no jobs for a server or a job on the wrong replica.

The report is text on purpose: you can read it before you send it.

Make the report

  1. Open Settings with the gear button at the bottom of the server list and go to Diagnostics.
  2. To keep names inside your company, tick Replace server, availability group, database, job and login names by placeholders.
  3. Press Save diagnostics… and choose where to save the text file. The proposed name is JobTimeline-diagnostics-<date>-<time>.txt. Copy to clipboard gives the same text to paste into an e-mail.

Collecting the report connects to the servers in the list, read-only. That usually takes a few seconds. A query gets 30 seconds and a server two minutes in total; what was read until then is kept.

What is in it

  1. Application and settings. The version, the Windows and .NET version, the time zone of this computer, the computer name and Windows account, and the reading and analysis settings.
  2. Workspace. Every instance and every contained availability group whose jobs are shown, the server it is read from, and the other servers that also see it.
  3. Per server:
    • Connection. The name as typed, the authentication mode and login name, the encryption options, the state, and the last error with its SQL error numbers.
    • What the application read at its last refresh. Instance name, version, clock and time zone; the availability groups with the role of this replica; the msdb databases that were read, with row counts; the warnings; job counts by kind; the runs grouped by the server that executed them; and a table of the jobs with the availability group fields the analysis uses.
    • Live probe. Read-only queries that run on a fresh connection when the report is made: the login and its server-level permissions, how the connection landed, the availability groups with their replicas, roles, listeners and databases, and per msdb the SELECT permission on the Agent tables, their row counts, the last Agent sessions and which servers executed the runs. Each query runs on its own. A failure is recorded with its error number and the rest still run.
  4. Replica comparison. For every availability group with replicas among the included servers that were read: the replicas and how a failover to each works, the notes that limit the comparison, and the findings, as the Replicas view shows them. Names are replaced by placeholders when you ask for that.
  5. Recent events. The last 200 events of the application’s event log (full reads, failures, recoveries, failovers and refused logins) and the last 40 routine lines, such as lines that repeat the previous line about a server. Routine polls are not logged.

The probe is skipped for a server that refused the login at the last read, because it would be one more failed login for that account. It is also skipped when no password is known for the connection.

What is never in it

  • Passwords
  • Connection strings
  • Job step commands
  • Job history messages and step messages

The step commands never leave the server in the first place, see Permissions and what is read.

The report does contain names: servers, logins, availability groups, databases and jobs, and the name of your computer and Windows account. The placeholder option replaces them.

Names replaced by placeholders

With the option ticked, every server, listener, machine, instance, availability group, database, job, login and IP address the report knows becomes a placeholder: Host1, AG1, Db3, Job17, Login1. The same name is the same placeholder everywhere, so the report stays as useful: the replica that executed a run is recognizably one of the servers, and the msdb of a contained group stays AG1_msdb.

System database names, built-in principals such as NT SERVICE\SQLSERVERAGENT, local names such as localhost, default instance names such as MSSQLSERVER, role and state values, versions, group ids and counts are kept.

Free text, such as warnings, error messages and events, is searched for the known names of servers, groups, databases and logins as well. Job names are not searched for in free text. For other names there are safety nets: an IPv4 address becomes <address>, an unknown name inside the quotes of a SQL Server error message becomes <name>, and a known domain becomes <domain>. A name that none of these recognize stays as it is, such as a job name in a message, a name without quotes, or a fully qualified name in an unknown domain. Read the report before you send it outside your company.

More than twenty servers

A full section and a live probe for hundreds of servers would make a report nobody can read or send. With more than 20 servers in the list, 20 are reported in detail and the others get one line each under Servers in brief: name, state, instance, version, number of jobs and runs, last full read, number of warnings and the last error.

The 20 are chosen in this order: included servers first, among them the servers that could not be read or have a note, then servers with availability groups, then the order of the server list.

For the detail of one particular server, include only that server, or only its group, and make the report again.

Send it

Send the report and the steps you took through the support portal.

Event log file

The recent events are always kept in memory for the report. To keep them in a file as well, set this environment variable before starting the application:

Variable Effect
JOBTIMELINE_DEBUG=1 Every event line is also appended to %TEMP%\jobtimeline-debug.log. The lines name servers and carry error texts; never passwords, connection strings or job contents.