lock request time out period exceeded, error 1222

[George]

Hi All,

"lock request time out period exceeded, error 1222" will appear when I try
to expand the tables list in a remote database in SQL server management
studio express. This is ok because some of the tables are locked. But I want
to onfigure the system to wait forever for this kind of locked table. Any
idea how I can achieve it? I have searched on the Internet that wait forever
is the default setting but it seems it is not.

I have tried to configure the "set lock timeout" configuration to -1
(default) in SQL management studio express options. But the problem persists.
Also, when I use SQL profiler, I noticed a "set lock timeout 10000" always
appear before the query begins. 10000 means system will only wait for ten
seconds. How can I change this number?

[Uri Dimant]

George
You want to find out who held the locks on the remote table?
--By applying the filter in the where clause you get the answers to
questions like:

---- What SQL Statement is causing the lock?

--- Which user has executed the SQL statement that's holding the locks?

--- What objects/tables are being locked?

--- What kinds of locks are being held and on which pages, keys, RID's?





SELECT L.request_session_id AS SPID,

DB_NAME(L.resource_database_id) AS DatabaseName,

O.Name AS LockedObjectName,

P.object_id AS LockedObjectId,

L.resource_type AS LockedResource,

L.request_mode AS LockType,

ST.text AS SqlStatementText,

ES.login_name AS LoginName,

ES.host_name AS HostName,

TST.is_user_transaction as IsUserTransaction,

AT.name as TransactionName,

CN.auth_scheme as AuthenticationMethod

FROM sys.dm_tran_locks L

JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id

JOIN sys.objects O ON O.object_id = P.object_id

JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id

JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id

JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id =
AT.transaction_id

JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id

CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST

WHERE resource_database_id = db_id()

ORDER BY L.request_session_id

Post by George
Hi All,
"lock request time out period exceeded, error 1222" will appear when I try
to expand the tables list in a remote database in SQL server management
studio express. This is ok because some of the tables are locked. But I want
to onfigure the system to wait forever for this kind of locked table. Any
idea how I can achieve it? I have searched on the Internet that wait forever
is the default setting but it seems it is not.
I have tried to configure the "set lock timeout" configuration to -1
(default) in SQL management studio express options. But the problem persists.
Also, when I use SQL profiler, I noticed a "set lock timeout 10000" always
appear before the query begins. 10000 means system will only wait for ten
seconds. How can I change this number?

[George]

Thanks.

Actually, I don't want to find out who held the lock. I just want to
configure the wait time to a bigger number. Currently, after about 10
seconds, I will receive the 1222 error.

Any thoughts?

George.

Post by Uri Dimant
George
You want to find out who held the locks on the remote table?
--By applying the filter in the where clause you get the answers to
---- What SQL Statement is causing the lock?
--- Which user has executed the SQL statement that's holding the locks?
--- What objects/tables are being locked?
--- What kinds of locks are being held and on which pages, keys, RID's?
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id =
AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id

Post by George
Hi All,
"lock request time out period exceeded, error 1222" will appear when I try
to expand the tables list in a remote database in SQL server management
studio express. This is ok because some of the tables are locked. But I want
to onfigure the system to wait forever for this kind of locked table. Any
idea how I can achieve it? I have searched on the Internet that wait forever
is the default setting but it seems it is not.
I have tried to configure the "set lock timeout" configuration to -1
(default) in SQL management studio express options. But the problem persists.
Also, when I use SQL profiler, I noticed a "set lock timeout 10000" always
appear before the query begins. 10000 means system will only wait for ten
seconds. How can I change this number?

.

[Uri Dimant]

http://www.sql-server-performance.com/tips/blocking_p1.aspx

Post by George
Thanks.
Actually, I don't want to find out who held the lock. I just want to
configure the wait time to a bigger number. Currently, after about 10
seconds, I will receive the 1222 error.
Any thoughts?
George.

Post by Uri Dimant
George
You want to find out who held the locks on the remote table?
--By applying the filter in the where clause you get the answers to
---- What SQL Statement is causing the lock?
--- Which user has executed the SQL statement that's holding the locks?
--- What objects/tables are being locked?
--- What kinds of locks are being held and on which pages, keys, RID's?
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id =
TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id =
AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id

Post by George
Hi All,
"lock request time out period exceeded, error 1222" will appear when I try
to expand the tables list in a remote database in SQL server management
studio express. This is ok because some of the tables are locked. But I want
to onfigure the system to wait forever for this kind of locked table. Any
idea how I can achieve it? I have searched on the Internet that wait forever
is the default setting but it seems it is not.
I have tried to configure the "set lock timeout" configuration to -1
(default) in SQL management studio express options. But the problem persists.
Also, when I use SQL profiler, I noticed a "set lock timeout 10000" always
appear before the query begins. 10000 means system will only wait for ten
seconds. How can I change this number?

.

[Manpreet Singh]

Hi,

Go to tools -> options , then query execution -> sql server -> general in
right hand side tree and then select query time out option to zero (means
unlimited wait, no time out) and also in query execution -> sql server ->
advanced select SET LOCK TIMEOUT to -1 (means unlimited wait, no time out)


Manpreet Singh
http://crazysql.wordpress.com/
MCITP (DBA, Developer) SQL Server 2005
MCTS (MOSS 2007), ITILv3F

Post by George
Hi All,
"lock request time out period exceeded, error 1222" will appear when I try
to expand the tables list in a remote database in SQL server management
studio express. This is ok because some of the tables are locked. But I want
to onfigure the system to wait forever for this kind of locked table. Any
idea how I can achieve it? I have searched on the Internet that wait forever
is the default setting but it seems it is not.
I have tried to configure the "set lock timeout" configuration to -1
(default) in SQL management studio express options. But the problem persists.
Also, when I use SQL profiler, I noticed a "set lock timeout 10000" always
appear before the query begins. 10000 means system will only wait for ten
seconds. How can I change this number?

[Uri Dimant]

Manpreet Singh

The OP mentioned that he has already tried -1 and it has the same result

Post by Manpreet Singh
Hi,
Go to tools -> options , then query execution -> sql server -> general in
right hand side tree and then select query time out option to zero (means
unlimited wait, no time out) and also in query execution -> sql server ->
advanced select SET LOCK TIMEOUT to -1 (means unlimited wait, no time out)
Manpreet Singh
http://crazysql.wordpress.com/
MCITP (DBA, Developer) SQL Server 2005
MCTS (MOSS 2007), ITILv3F

Post by George
Hi All,
"lock request time out period exceeded, error 1222" will appear when I try
to expand the tables list in a remote database in SQL server management
studio express. This is ok because some of the tables are locked. But I want
to onfigure the system to wait forever for this kind of locked table. Any
idea how I can achieve it? I have searched on the Internet that wait forever
is the default setting but it seems it is not.
I have tried to configure the "set lock timeout" configuration to -1
(default) in SQL management studio express options. But the problem persists.
Also, when I use SQL profiler, I noticed a "set lock timeout 10000" always
appear before the query begins. 10000 means system will only wait for ten
seconds. How can I change this number?

[George]

Thanks for the help. I have already tried setting SET LOCK TIMEOUT to -1. It
did not work. In SQL Profiler, I noticed a set lock timeout 10000 always
appear before opening table list. Where can I configure that?

Uri, if I manually set lock timeout to a bigger number, for example:
99999999, it will work. But after a while, set lock timeout 10000 will appear
again as I monitor in SQL profiler...

Any thoughts?

[Tibor Karaszi]

Unfortunately, I think this value is hard-coded in the app. I suggest you
post a wish at connect to address this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by George
Thanks for the help. I have already tried setting SET LOCK TIMEOUT to -1. It
did not work. In SQL Profiler, I noticed a set lock timeout 10000 always
appear before opening table list. Where can I configure that?
99999999, it will work. But after a while, set lock timeout 10000 will appear
again as I monitor in SQL profiler...
Any thoughts?

[Erland Sommarskog]

Post by George
Thanks for the help. I have already tried setting SET LOCK TIMEOUT to
-1. It did not work. In SQL Profiler, I noticed a set lock timeout 10000
always appear before opening table list. Where can I configure that?

I would guess it is hard-coded. I will have to admit that I don't see the
point with SSMS locking up infinitely if there is uncomitted DDL in the
database.