Pages

Dec 2, 2016

Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)



Issue: I see this issue when I try to expand the databases and any other things in object explorer of secondary replica of AlwaysOn SQL instance. I have seen few blogs which suggests to see sp_who2 results and kill blocking. Unfortunately this is  read only database and no user connections found.

However I have noticed many system sessions running from many days by taking much CPU and IO but I cannot kill them. There is reason for this, this database is out of sync with primary and DPM failing to take log backups from many days.

Error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.4232&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476

------------------------------
BUTTONS:

OK

Fix: As I cannot kill the sessions I had to restart the SQL services as there is no impact with this option. Restart taken quite while approx 5mins. which is very long with the restarts I made before.

Comments: SQL Server version is

Microsoft SQL Server 2014 (SP1-GDR) (KB3194720) - 12.0.4232.0 (X64)
                Sep 23 2016 18:45:14
                Copyright (c) Microsoft Corporation

                Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

No comments: