May 14, 2008 at 5:44 pm
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,
May 14, 2008 at 8:46 pm
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
May 17, 2008 at 10:19 am
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.
May 18, 2008 at 12:24 pm
have u changed any thing in the sql server configuration.
is it 2000 or 2005.
..>>..
MobashA
May 19, 2008 at 5:50 am
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......
May 19, 2008 at 6:48 am
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
May 19, 2008 at 6:56 am
run sp_who2 and see the activities
May 19, 2008 at 9:06 am
I have also had similar problems.
May 20, 2008 at 6:49 am
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.
May 23, 2008 at 9:42 am
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.
July 29, 2009 at 1:38 pm
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
December 13, 2010 at 5:06 pm
In SQL Server 2005 another user had uncommited transactions on the database - causing me to get this error.
March 29, 2011 at 9:11 am
+1 for somebody having an open transaction. That foxed me too!
August 4, 2011 at 12:50 pm
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.
January 23, 2012 at 10:16 am
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