Update Stats Error - Msg 1222, Lock request time out period exceeded

Question

Hello,
I am running Weekly Index Optimizing job to Modify the stats but it's failing from last few times at this step - Modify Stats.
Actually we are running this off hours on Weekend using Ola's script.
Any one having issue or any suggestion?
Is it Ola's script having any work around?

We are getting following error message for some of the failed stats:

Msg 1222, Lock request time out period exceeded

Command: UPDATE STATISTICS [BIDS].[dbo].[WorkQ] [_WA_Sys_00000006_151B244E]
Msg 50000, Level 16, State 1, Server BIDSSql, Procedure CommandExecute, Line 167
Msg 1222, Lock request time out period exceeded.
Outcome: Failed
Duration: 01:00:00
DateTime: 2021-09-26 11:14:05

This is a Sql Server 2012 and Database is around 150 GB size.

Answer 1

Any idea if we can exclude the table from update stats?
I am using Ola's script?

Is it correct way to use?

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBAMaint -Q "EXECUTE [dbo].[IndexOptimize] @Databases = 'ALL_DATABASES', -TextDB.dbo.WQueuu, @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @TimeLimit = 3600, @LogToTable = 'Y'" -b

Answer 2

Thanks Erland.
I think I should Exempt this table from update stats which will be better idea.

Answer 3

Yes, sure, you can add a step to the reindex job that kills the spid, but as you described it, that process will soon be hammering the database again. So you would have to find away to stop this guy at the gate. You will need to discuss this with the vendor.

Then again, what is most important? That the application process is doings it job, or that statistics are updated? Maybe the best is to exempt this table from the stats job, and rely solely on autostats? If this table is frequently updated, autostats should kick in often.

Again, this is something you should discuss with the vendor

Answer 4

Thanks once again Erland.
I understood the @TimeLimit parameter, the reason I set it 3600 because job should not run mire then an hour.
For @LockMessageSeverity to 10, not sure as job constantly running to update the table, looks like this table kind of keeping last entry - kind of log.
Is it anyway I can add something which can Kill the blocking session when this jobs run?

Thank You!

Answer 5

As I understand the @TimeLimit parameter, it controls how long time the job will run. If you set it to 3600, the job will stop after an hour. Or more precisely, it will not submit any new commands, but the command that is running when the hour expires will complete. Again, that is my interpretation.

But you could try setting the parameter @LockMessageSeverity to 10. With this setting, the lock timeout will only produce an informational message but not stop the job.