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

Hi @Pds ,

Welcome to Microsoft Q&A!

Msg 50000, Level 16, State 1, Server BIDSSql, Procedure CommandExecute, Line 167

Please see this MS link, which describes the possible scenarios for this error.

Msg 1222, Lock request time out period exceeded

In some cases, a query will fail with the lock request time out period error after waiting for more than 10ms.
So you can use sp_who2 to check for all session that are currently established in the database and check for any with blocking, high CPU usage, high I/O usage, or any with multiple entries for the same SPID. This is a troubleshooting. Maybe you can find the reason for your lock timeout.

If there is no abnormality, you can try the following solution:

1. Modify the Transaction use query hints (use RECOMPILE, MAXDOPhints)
2. Run big Transaction in smaller transactions.
3. Upgrade Hardware if possible.
4. To prevent this, make sure every BEGIN TRANSACTION has COMMIT
5. If you are running this in SQL Server Management Studio, you can close the query windows and it will automatically close the transaction.
   For more information, please see this article.

Here is a similar thread:https://social.msdn.microsoft.com/Forums/sqlserver/en-US/90c91ab6-2f16-4c17-a0d7-ae5802a100c2/index-job-failed-with-this-error-msg-1222-lock-request-time-out-period-exceeded-sqlstate-42000?forum=sqlgetstarted

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thanks Seeya for your detailed answer.
Information you mentioned which I have tried and while goggling found those ones so appreciate your information.
This update stats we are running nightly so hard check blocking session or high CPU or to kill that blocking session is.
I was trying to trace it down and see that it might be a session which is updating table and update stats is also failing for that table.
We are using Ola's script, Isi t we can prevent somehow blocking session and let it run Update stats job?

This is a part of jobs:
Index Optimize ==> which running fine
Update Stats ==> Failing at specific step

We are using following command in Sql Agent job:

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

Answer 3

I was looking more closely which Update Stats failing and from last few times stats were failing is from same table, only number of stats was different so I tried to check statistics on that table and almost 7 total statistics three stats start with _WA so system generated and 4 stats has it's own name.
While I was querying table, I couldn't find any rows ( not sure this table has been used or not) but it has Indexes (1 - Clustered and 3 - NC Indexes)
Table has BigInt columns.
Should I delete the statistics?

Answer 4

Hi @Pds ,

Update Stats ==> Failing at specific step

Do you get any error message or some error in errorlog?
Please run DBCC CHECKDB on the database or DBCC CHECKTABLE on the table shown on the error.

Should I delete the statistics?

Don't delete statistics easily. Doing so may affect the execution plan chosen by the query optimizer.
For more details, please see this document: https://learn.microsoft.com/en-us/sql/relational-databases/statistics/delete-statistics?view=sql-server-ver15

Furthermore, here is SQL Server Statistics Basics which is very detailed so that you can have a better understanding about statistics name.
https://www.red-gate.com/simple-talk/databases/sql-server/performance-sql-server/sql-server-statistics-basics/

Best regards,
Seeya

Answer 5

Thanks once again Seeya.
Not getting any error in Sql server Error log so do I need to run DBCC CHECKTABLE still?
Sql Agent log not showing useful message but we are capturing log into Log folder and from there I can see which Statistics are failing and which ones are succeeded.
From last few times, failed ones are almost same and from one table but somehow I am checking the table and no records.
Although Table having Indexes with Include columns.
If Table has already Index and those stats I can't delete or drop.