How can I capture error thrown by SET_LOCKTIMEOUT in ola Hallengren

Question

A stats only job for Ola Hallengren was blocked for the 180 seconds as specified below. The job threw an error and emailed me and the other DBA. However, upon review of the CommandLog table and the output file, there was no record of what the error was. I eventually figured out what had happened by looking at our monitoring tool, noticing the blocking for exactly the amount of time as the locketimout value and then looking at Ola's site to find that it throws a severity 16 error when the lock timeout is reached.

Is there a way to have the Ola scripts log the error so it's easier to figure out what went wrong?

EXECUTE [dbo].[IndexOptimize]
    @Databases = 'USER_DATABASES' ,
    @FragmentationLow = NULL ,
    @FragmentationMedium = NULL ,
    @FragmentationHigh = NULL ,
    @UpdateStatistics = 'ALL' ,
    @OnlyModifiedStatistics = N'Y' ,
    @LockTimeout = 180,
    @LogToTable = N'Y';

Answer 1

As you're already doing, @LogToTable = 'Y', should log errors. However, I tested this and I'm not getting an error logged to the commandlog table, either, but it does get logged to an output file. I'm using the latest version of Ola's script and table on SQL 2019. If you add @LockMessageSeverity = 10 (the default is 16), the job will at least run and you won't get alerted. You may want to check your stats, though, because the update stats statement being blocked will be skipped. You could submit this as a possible bug through Ola's github.