lock request time out period exceeded, error 1222 - microsoft.public.sqlserver.server

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 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 "George" <George@discussions.microsoft.com> wrote in message news:A2985347-18DD-4802-B29B-667DE216755F@microsoft.com... > 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?

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. "Uri Dimant" wrote: > 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 > > > > > > "George" <George@discussions.microsoft.com> wrote in message > news:A2985347-18DD-4802-B29B-667DE216755F@microsoft.com... > > 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? > > > . >

http://www.sql-server-performance.com/tips/blocking_p1.aspx "George" <George@discussions.microsoft.com> wrote in message news:8665B7B3-A2D1-4E3E-A97A-9DEE7233B05F@microsoft.com... > 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. > > > > "Uri Dimant" wrote: > >> 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 >> >> >> >> >> >> "George" <George@discussions.microsoft.com> wrote in message >> news:A2985347-18DD-4802-B29B-667DE216755F@microsoft.com... >> > 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? >> >> >> . >>

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 "George" wrote: > 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 The OP mentioned that he has already tried -1 and it has the same result "Manpreet Singh" <ManpreetSingh@discussions.microsoft.com> wrote in message news:D640F0E1-990C-4AF7-8B84-771B3AA49F58@microsoft.com... > 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 > > > "George" wrote: > >> 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?

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?

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 "George" <George@discussions.microsoft.com> wrote in message news:BC608D84-AF8E-4825-A318-F0A1226D57D9@microsoft.com... > 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?

George (George@discussions.microsoft.com) writes: > 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. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Similiar Articles:

lock request time out period exceeded, error 1222 - microsoft ...
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 ...

CRM Request timed out - microsoft.public.crm
lock request time out period exceeded, error 1222 - microsoft ... Try this again: Crystal Reports timeouts - microsoft.public.crm ... lock request time out period exceeded ...

Request.Form error in asp.net - microsoft.public.dotnet.framework ...
lock request time out period exceeded, error 1222 - microsoft ... Hi All, "lock request time out period exceeded, error 1222 ... com/sqlserver/default.asp ... locked table ...

Performance :Lock Wait Time - microsoft.public.sqlserver.server ...
lock request time out period exceeded, error 1222 - microsoft ... Hi All, "lock request time out period ... http://www.sql-server-performance.com/tips ... want to find out ...

Management Reporter - User locked out - microsoft.public ...
lock request time out period exceeded, error 1222 - microsoft ... Management Reporter - User locked out - microsoft.public ... lock request time out period exceeded, error ...

HttpException "Request timed out" - how to handle ...
lock request time out period exceeded, error 1222 - microsoft ... HttpException "Request timed out" - how to handle ... lock request time out period exceeded, error 1222 ...

Time server error - microsoft.public.windows.server.sbs ...
lock request time out period exceeded, error 1222 - microsoft ... George You want to find out who held the locks on the remote table? --By applying the filter in the where ...

Try this again: Crystal Reports timeouts - microsoft.public.crm ...
lock request time out period exceeded, error 1222 - microsoft ... Try this again: Crystal Reports timeouts - microsoft.public.crm ... lock request time out period exceeded ...

sys.dm_exec_sql_text questions - microsoft.public.sqlserver ...
lock request time out period exceeded, error 1222 - microsoft ... sys.dm_exec_sql_text questions - microsoft.public.sqlserver ... lock request time out period exceeded ...

Session timeouts with forms authentication - microsoft.public.crm ...
lock request time out period exceeded, error 1222 - microsoft ... Crystal 'session timeout' error - microsoft.public.crm ... lock request time out period ... locked table ...

SQL SERVER – Alternate Fix : ERROR 1222 : Lock request time out ...
ERROR 1222 : Lock request time out period exceeded. MSDN Suggests solution here. It says find offending transaction and terminate it and run the query again.

Error 1222 Lock Request Time Out Period Exceeded When Set up ...
One of the SQL Server that I manage has a couple non-updatable transactional publications defined in a database. I needed to push those publications to a new ...

Lock request time out period exceeded
The first error is Lock request time out period exceeded This is then immediately followed by An error occurred in dialog transmission: Error: 1222, State: 51.

Answer : lock request time out period exceeded, error 1222
George You want to find out who held the locks on the remote table? questions like: - What SQL Statement is causing the lock? Which user has executed the SQL ...

Error 1222: Lock request time out period exceeded
The Lock request time out is set from the client application in your case EM. Opening Enterprise Manager, Management, Current Activity, Process Info actually executes ...

7/22/2012 6:35:28 AM