question

Pds-2061 avatar image
0 Votes"
Pds-2061 asked ErlandSommarskog commented

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

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.


sql-server-general
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Pds-2061 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

0 Votes 0 ·

Seeya,
Not yet, still looking as solution not yet help.

Thanks once again!

0 Votes 0 ·

Hi @Pds-2061 ,

Did Erland's answer help you? If it helps, please accept the answer. If not, please feel free to post any problems encountered during the process.

Best regards,
Seeya

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @Pds-2061 ,


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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Pds-2061 avatar image
0 Votes"
Pds-2061 answered Pds-2061 edited

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Pds-2061 avatar image
0 Votes"
Pds-2061 answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @Pds-2061 ,


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://docs.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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Pds-2061 avatar image
0 Votes"
Pds-2061 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Pds-2061 avatar image
0 Votes"
Pds-2061 answered

Hello,
I also ran the Step for Update stats is failing and again it was taking much time so I started to run

DBCC OPENTRAN
Run DBCC InputBuffer <for that SPID> ==> Which I found updating one table that normally I see previously too
I also run EXEC sp_who2 <for that SPID> ==> Show blocking so I killed <for that SPID> and my job stopped and failed error out

I din't understand that Update statements have that SPID why my steps terminated?

I also check the Log for Update Stats and it showing same Update stats statement which one is failing last few times but it's from different table.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

By default, the lock timeout is set to -1, which means "wait forever" for a blocking lock to be released.

So something somewhere is setting the lock timeout to value of 0 or higher. It is possible that Ola sets this by default, but in such case I am sure that exposes a parameter to change this. Check the documentation.

But you may also want to check the process that blocks the UPDATE STATISTICS and possibly see if you can reschedule any of them so that they don't clash.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Pds-2061 avatar image
0 Votes"
Pds-2061 answered

Thanks Erland.
I agreed about default Lock Time but we are using same script on our lots of servers/Databases.
We don't have issue but it's just started last month.
This is a Vendor Application database and looking into table there are no relationship (PK/FK) set up, it might be somewhere in application set up that's why it's blocking as when I ran today, it was update Sql on another table which was blocking session but Update Statistics is on another table which stats is failing.
Unfortunately, Update Statistics failing on table which mostly empty, only once a while I see the records and table which getting updated (Blocking Table) that has only 11 records.
Is it any way I can prevent job step failing or restrict to that blocking session or ignore it?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Is it any way I can prevent job step failing or restrict to that blocking session or ignore it?

Your options are:
1) Change the lock timeout (as I said, check Ola's documentation)
2) Remove the blocking operation.

What you cannot do is to tell UPDATE STATISTICS to ignore the lock.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Pds-2061 avatar image
0 Votes"
Pds-2061 answered SeeyaXi-msft commented

Thanks Erland.
I can't prevent the blocking to remove as query running every few seconds.
Not sure but in the script it has @TimeLimit = NULL

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I checked Ola's documentation, and he has a parameter @LockTimeout. The docs says that by default there is no limit.

0 Votes 0 ·

Thank You!
I will change the script to exclude the table and see how it works as TimeLimit not much helpful

0 Votes 0 ·

Hi Pds-2061,

If you have any questions, please feel free to let us know.
Have a nice day!

Best regards,
Seeya

0 Votes 0 ·