Option to ignore "Lock Request Time out" (error 1222) during IndexOptimize about sql-server-maintenance-solution HOT 8 CLOSED

I did some testing with this long time ago, but I never got to releasing it. I added a parameter to change the severity for error 1205 (deadlocks) and lock timeouts (1222) from error to informational. Would that work for you?

from sql-server-maintenance-solution.

If I understand it right, the problem may be the returncode of the procedure. If the procedure returns something different than 0, the job will show up as failed:

` ----------------------------------------------------------------------------------------------------
--// Log completing information //--

Logging:
SET @EndMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120)
SET @EndMessage = REPLACE(@EndMessage,'%','%%')
RAISERROR(@EndMessage,10,1) WITH NOWAIT

IF @returncode <> 0
BEGIN
RETURN @returncode ---<------ This might be the problem ---
END

`
Best regards,
Oliver

from sql-server-maintenance-solution.

In the test version I was working on, I was adding some logic to CommandExecute. Here is the catch block as it looks today. As you see I am raising an error with severity 16. I could so something that if the error number is 1205 or 1222, then I raise the error with severity 10 instead.

    BEGIN CATCH
      SET @Error = ERROR_NUMBER()
      SET @ReturnCode = @Error
      SET @ErrorMessageOriginal = ERROR_MESSAGE()
      SET @ErrorMessage = 'Msg ' + CAST(@Error AS nvarchar) + ', ' + ISNULL(@ErrorMessageOriginal,'')
      RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
    END CATCH

from sql-server-maintenance-solution.

Hi Ola,
I think this would only be one part of the solution.
In addition, the procedure IndexOptimize returns the latest return code from CommandExecute.
This would also be the returncode of the job.
Unfortunately, the job step would interpret every returncode <> o as an error. :-(
This would mean we would also have to change IndexOptimize to return 0 if the commandexecute returncode was 1205 or 1222. :-(
Best regards,
Oliver

from sql-server-maintenance-solution.

You are right. I would also need to do a RETURN 0 in CommandExecute, in this case.

Another thing is that there can also be lock timeouts in IndexOptimize (e.g. when checking sys.dm_db_index_physical_stats). (You will get the lock timeout in different places in the code, depending on what type of lock it is.)

So there are some work to get this working correctly.

If we think about the parameter. In the test version that I was working on, I had a parameter called @LockMessageSeverity, that default was set to 16, but that could be changed to 10. Do you have any thoughts on that?

from sql-server-maintenance-solution.

Yes. That would be great. If I use IndexOptimize from T-SQL context, it would be useful if I already know that the error is not critical from the supplied severity level.
Then I would have the choice to stop further activity when an error was raised with high severity, or to continue with the next steps when the severity level is "informational". I would definitely use it. :-)
Best regards,
Oliver

from sql-server-maintenance-solution.

I have added this feature now.
https://ola.hallengren.com/versions.html

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html#LockMessageSeverity

By default, the LockMessageSeverity is set to 16 (error), but you can set it to 10 (informational).

Here is how to use it:

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@LockTimeout = 60,
@LockMessageSeverity = 10

from sql-server-maintenance-solution.

Hi Ola,
this is great news.
I just tested the new version.
This is a perfect solution.
I still see the error in the CommandLog table, but the job completes successfully.
This is exactly how I need this. 👍
Thank you!!!! :-)
Best regards,
Oliver

from sql-server-maintenance-solution.