log to table vs log to output file

Question

I am using ola-hallengren solutions for database maintenance. A question about the log, I see in the documentation website:

LogToTable: Log commands to the table dbo.CommandLog.

Value   Description
Y       Log commands to the table.
N       Do not log commands to the table. This is the default.

Does N which is default means to log to output file? I also see the recommendation is to log to output file, then what is the CommandLog table for?

Which stored procedure has code to log into output file and can set location of output file?

Thanks

Answer 1

Does N which is default means to log to output file?

No, it just wouldn't output to the CommandLog table. It would still output to file.

I also see the recommendation is to log to output file, then what is the CommandLog table for?

The CommandLog table makes it easy to read the output log instead of having to read a flat file from your drive. Many people will add follow on jobs to query this table and email / report out errors, etc.

Which stored procedure has code to log into output file and can set location of output file?

The @OutputFileDirectory parameter in the MaintenanceSolution.sql script is where you should set this. If no ouput is specified, the SQL Server error log directory is used. This is referenced in the Advanced page on the job step. Depending on your SQL Version, you will see SQLLOGDIR or the actual output path. SQLLOGDIR is an agent token added in 2014.

(SQLLOGDIR) Replacement token for the SQL Server error log folder path - for example, $(ESCAPE_SQUOTE(SQLLOGDIR)). This token is only available on SQL Server 2014 and above.

Another way, if you are using Windows Scheduler or wanted to edit the Agent Job directly is by specifying your log location for sqlcmd via the output option.

The output option (-o) specifies the file in which sqlcmd is to put its output.

Thus, something like -o C:\Log\YourLog.txt

In that FAQ, Ola provides an example for a DB Backup:

sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt