Lock request time out period exceeded.

  • for the first time i experience the following message when trying to expand the tree (ie Stored Procs etc) in Management Studio.

    Lock request time out period exceeded.

    I am more of a SQL programmer than DBA so please forgive my lack of knowledge in this department.

    Where can i look (or what system views do i review) to find out (or to give clues as to) what code was being run by clients to cause database to crawl?

    When i opened up windows task manager on the server, SQLSevr.exe was utilizing 1.1GB of memory, where it usually sits on 180 - 200MB.

    any help would be truly appreciated.

    regards,

  • I've never seen that error occur when expanding nodes within Object Explorer.

    You can use Activity Monitor (under Management node in explorer) to quickly view resource usage among each user connections. That will show you the memory & cpu usage, and whether any blocking/locking is occuring.

    When you know performance is suffering and see (within Activity Monitor) that a session is blocking query the sys.dm_exec_requests to get the sql_handle for the user causing the blocking. Once you have that, you can query sys.dm_exec_sql_text() to get the actual statement(s) the user was executing (see example below).

    As for memory usage by SQL Server, that is dependent upon many variables; size of database, number of databases within SQL Server instance, database utilization and query performance among others. SQL Server uses over 10GB of memory on our production server at work, but that server has 32GB installed.

    Example:

    SELECT

    d1.session_id,

    d3.[text],

    d1.login_time,

    d1.login_name,

    d2.wait_time,

    d2.blocking_session_id,

    d2.cpu_time,

    d1.memory_usage,

    d2.total_elapsed_time,

    d2.reads,d2.writes,

    d2.logical_reads,

    d2.sql_handle

    FROM sys.dm_exec_sessions d1

    JOIN sys.dm_exec_requests d2 ON d1.session_id=d2.session_id

    CROSS APPLY sys.dm_exec_sql_text(d2.sql_handle) d3

  • I had the same thing happen to me you just cannot get into activity to see anything you get the lock timeout in the studio.

    I still not found out why..........is there a way to run the activity script using sqlcmd and see the details...

    I.e if no gui then how to see.

  • have u changed any thing in the sql server configuration.

    is it 2000 or 2005.

    ..>>..

    MobashA

  • No -- for me this is on our dev box and you just get a message waiting for internal operation to finish and then studio never comes up..so you can't see whats going on the sql......

  • is this problem is going on with every body or just u, if so it might be some thing wrong with the installaion files on ur PC.

    ..>>..

    MobashA

  • run sp_who2 and see the activities

    http://rajanjohn.blogspot.com

  • I have also had similar problems.

  • In SQL 2000 when running reindex or some other heavy transactions I see this all the time. Very frustrating because that is exactly when you want to see the activity.... In 2005 I still see it but fewer times. I think this is a known issue by MSFT.

  • You cannot even get to go to the query to run sp_who2 when this occurs.

    Is there a way to run sp_who2 in sqlcmd without bringing up the gui and get the output.

  • Same here, my Sql manager freezes and I get that error.

    In specific, its happening cause on of one job is running now

    sqlwho2 ---->

    SPIDStatusLoginHostNameBlkByDBNameCommandCPUTimeDiskIOLastBatchProgramNameSPIDREQUESTID

    1 BACKGROUND sa . . NULLRESOURCE MONITOR0007/28 18:13:23 1 0

    2 BACKGROUND sa . . NULLLAZY WRITER 31007/28 18:13:23 2 0

    3 SUSPENDED sa . . NULLLOG WRITER 93007/28 18:13:23 3 0

    4 BACKGROUND sa . . NULLLOCK MONITOR 0007/28 18:13:23 4 0

    5 BACKGROUND sa . . masterSIGNAL HANDLER 0007/28 18:13:23 5 0

    6 sleeping sa . . masterTASK MANAGER 0007/28 18:13:23 6 0

    7 BACKGROUND sa . . masterTRACE QUEUE TASK15007/28 18:13:23 7 0

    8 sleeping sa . . NULLUNKNOWN TOKEN 0007/28 18:13:23 8 0

    9 BACKGROUND sa . . masterBRKR TASK 0007/28 18:13:23 9 0

    10 BACKGROUND sa . . masterTASK MANAGER 0007/28 18:13:23 10 0

    11 BACKGROUND sa . . masterCHECKPOINT 210924807/28 18:13:23 11 0

    12 BACKGROUND sa . . masterBRKR EVENT HNDLR152607/28 18:13:23 12 0

    13 BACKGROUND sa . . masterBRKR TASK 0007/28 18:13:23 13 0

    14 sleeping sa . . masterTASK MANAGER 0007/28 18:13:23 14 0

    15 sleeping sa . . masterTASK MANAGER 01307/28 18:13:23 15 0

    16 sleeping sa . . masterTASK MANAGER 0307/28 18:13:23 16 0

    17 sleeping sa . . masterTASK MANAGER 01007/28 18:13:23 17 0

    18 sleeping sa . . masterTASK MANAGER 0007/28 18:13:23 18 0

    19 sleeping sa . . masterTASK MANAGER 0007/28 18:13:23 19 0

    20 sleeping sa . . masterTASK MANAGER 0107/28 18:13:23 20 0

    21 sleeping sa . . masterTASK MANAGER 0707/28 18:13:23 21 0

    51 sleeping NT AUTHORITY\NETWORK SERVICEserver123. ReportServerAWAITING COMMAND0007/29 15:27:38Report Server 51 0

    52 RUNNABLE \P00621FRPPSP00621HP . ATLASSELECT INTO 125407/29 15:28:19Microsoft SQL Server Management Studio - Query 52 0

    53 sleeping NT AUTHORITY\NETWORK SERVICEserver123. msdbAWAITING COMMAND4223507/29 14:14:30SQLAgent - Generic Refresher 53 0

    54 sleeping \P00621FRP00621HP . masterAWAITING COMMAND126207/29 15:24:32Microsoft SQL Server Management Studio 54 0

    55 sleeping NT AUTHORITY\NETWORK SERVICEserver123. msdbAWAITING COMMAND32807/29 14:15:22SQLAgent - Job invocation engine 55 0

    56 sleeping NT AUTHORITY\SYSTEMserver123. ReportServerAWAITING COMMAND0007/29 15:25:40Report Server 56 0

    57 sleeping NT AUTHORITY\NETWORK SERVICEserver123. msdbAWAITING COMMAND17250407/29 15:28:07SQLAgent - Alert Engine 57 0

    58 sleeping NT AUTHORITY\SYSTEMserver123. ReportServerAWAITING COMMAND0007/29 15:28:12Report Server 58 0

    59 RUNNABLE NT AUTHORITY\NETWORK SERVICEserver123. ATLASSELECT INTO 101428215086107/29 14:15:22SQLAgent - TSQL JobStep (Job 0xCD23B4970F13494582D1C75B5F43EA52 : Step 1)59 0

  • In SQL Server 2005 another user had uncommited transactions on the database - causing me to get this error.

  • +1 for somebody having an open transaction. That foxed me too!

  • I did this:

    1) start to rebuild index

    2) SP_WHO2

    the SP_WHO2 will show a row with column Command reading like "Rebuild Index"

    He will be getting blocked by a SPID.

    Kill that SPID.

    Kill him good.

    Then try to rebuild your index again.

  • This happened to me after running "SET ANSI_DEFAULTS ON" from a query window in SSMS and then creating a stored procedure in another query window. If I didn't run "SET ANSI_DEFAULTS ON" I had no problems (after bringing the database offline then online again to kill all transactions and connections).

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply