Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    76

    Unanswered: Row retrieval problem in small table

    Dear Participant,

    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.

    I dont understand what is the problem.

    Thanks

    R.Mall

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  3. #3
    Join Date
    Sep 2004
    Posts
    76
    I will see it, but I had look over the same matter on server itself, without a single user in LAN.

  4. #4
    Join Date
    Dec 2004
    Posts
    47
    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)

  5. #5
    Join Date
    Sep 2004
    Posts
    76
    Is there any easy going methods to resolve this problem.

    Thanks

    R.Mall

  6. #6
    Join Date
    Dec 2004
    Location
    CA, USA
    Posts
    63
    Use sql profiler to watch it.

  7. #7
    Join Date
    Jan 2005
    Posts
    10
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •