1

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';

1 Answer 1

0

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.

2
  • 1
    Thanks for the testing you did. I did check the output file but I either missed it or just had the wrong one open. It's good to know that you did find it in there. I may submit this as a possible bug to Ola's Github so someone can address why the CommandLog table doesn't have the error.
    – Lee M
    Jul 25, 2020 at 16:02
  • I have opened an issue in the Github for Ola's scripts. github.com/olahallengren/sql-server-maintenance-solution/issues/…
    – Lee M
    Jul 27, 2020 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.