I face following problem by last few day, please help me for the same
My Mssql server 2000 with service pack 3 use for my lan bas users, normally they can work fine without any problem, but some time user not able to retrieve information from server. I had debugged this problem and found one small table with 50/60 records not retrieving for so long at clients machine. I open enterprise manage and trough that try to open table, but server in try mode only not show a single row after long time and give message client time out.
I open query analyzer and try to select * from table_name, it is also not retrieve a single row after long time and nothing got as a message.
Shutdown the server and restart , I am able to retrieve that table from EM, Query analyzer and from application also.
Sounds like a blocking problem to me. The next time this happens, take a look in Enterprise Manager Management->Current Activity->Process Info, and see if people are getting blocked in general. After that, you can try to narrow down the table, but I think you already have that table.
Is there any jobs running in parallel.... Jobs by default is a transaction.... u can see once a transaction is running and if u try to get data thru enterprise manage and trough that try to open table it will not display any.... but select should run in that case......
I got a doubt what does this blocking means??? is that 'lock' u guys are mentioning.... If its lock u can run the below query to check if there is any lock still running,
SELECT spid, cmd, status, loginame, open_tran,
datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)
i suggest you use SP_WHO to check the instances running and status of each instance. you would be able to see also if there are blocking...
use the Profiler if you want to know the different SQL commands being processed by the server. However, use this with caution since it might cause your system to slow down thus giving you the false impression that your script is slow.