Locked Table 1

[EscapeUK]

One of my tables has become locked. When I choose return all rows i am able to scroll down so far then Enterprise manager locks. After a while it times out. Also if i try to very the table using query analyzer again it displays some results but the query continues to run for ages. Also tried an ODBC connection from other apps to try and bring back data, but again the apps just hangs.

Any ideas into how i can find out whats wrong. or any ideas what might be wrong

 

[redlam]

do you have a ton of data in this table? sounds like the indexes may be inefficient. try bringing back a smaller subset of data, maybe just one or two records.

 

[EscapeUK]

No it is not a huge amount of data. I tried a small query but still had the same prob

 

[dbmsguy]

a few things to try...

1. check for blocking.
2. try unloading the data using bcp. see if bcp hangs.
3. have you run integrity checks recently? if not, try runnnig some dbcc's
4. try doing a select count(some_non_indexed_col) and see if it is to do with access or data being returned.


Paul

http://www.dbmsguy.com

 

[EscapeUK]

Lots of points there, sorry to be pain but could you tell me how to do them

 

[dbmsguy]

1. once it seems to hang, open a EM session and use sp_who. look for blocked processes.
2. from cmd - mssql7/binn/bcp db..table out filename -T -n
this command will create an output file. assumes that you are doing this from the local machine from admin acct.
3. either look up 'dbcc' in the manual or go to 'maintenance' tab and create an integrity check job.
4. enter command 'select count(xxxx) from tabname. xxx should be an unindexed column. this will cause a scan of the entire table and bring back one row containing the rowcount.

5. you should also check the errorlog. you can find this in one of the tabs, or from EM enter sp_readerrorlog or go to mssql7/log/errorlog and look at it....

hope this helps.
Paul

http://www.dbmsguy.com