onboarding: how best to come to grips with default error messages?

[sacrophyte]

Good day!

We are ramping up our exposure and use of MS SQL Server (coming from a predominantly Oracle shop), and have been using the Ola hallengren scripts for backups across our enterprise. Some questions and clarifications to help us come to grips with "how things work" in these scripts.

At this point, my first and biggest problem is diagnosing and troubleshooting errors and job failures. I see three issues with our understanding of output generated from jobs:

1. Most messages are typically very (VERY) generic and do not indicate what the actual problem is. For instance, we will see something like "Step 1 failed", but no actual details.

2. The logfile path is obfuscated by the job system tokens (eg, $(ESCAPE_SQUOTE(SQLLOGDIR))$(ESCAPE_SQUOTE(JOBNAME))$(ESCAPE_SQUOTE(STEPID))$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).txt). It is not clear at all what SQLLOGDIR is. While we can figure out the rest of it, it would be very helpful to have the actual path printed out somewhere that is easy to find. Do most people just leave SQLLOGDIR as default? Why not have a common, well-known directory close to root? (or at least, a shortcut/link)

3. The standard output from select statements do not seem to appear in the Job Step output file - where does it go?

I realize these are somewhat "noob" questions and reveal our lack of experience. Help?

[griffitmatt]

Hi for point 2 The SQLLOGDIR is the Replacement token for the SQL Server error log folder path (2014+)
https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps?view=sql-server-ver15

You can get the folder path by running the below SQL

SELECT SERVERPROPERTY('ErrorLogFileName')

This is common well known directory for SQL server installations.