Post reply

Strange situation when retrieving data (only 6 records)

  • July 7, 2005 at 11:43 am

    #165251

    Hi,

    When I tried SELECT Col1, Col2, Col3 FROM dbo.TableName, the query takes more than 3 minutes !!! and still didn't finish the process.

    So I ran DBCC CHECKTABLE ('TableName') and got the following result,

    DBCC results for 'TableName'.

    There are 6 rows in 1 pages for object 'TableName'.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Application is still able to add records to this table. The data type for Col1, Col2, Col3 are INT, DATETIME, BIT.

    I also ran SP_WHO2 to see if there are any deadlocks, but there were none.

    Any help is greatly appreciated.

    Thanks.

    R

  • July 7, 2005 at 11:57 am

    #572004

    How many rows are in the table?

  • July 7, 2005 at 11:59 am

    #572008

    I am not sure how many as I am not able to retrieve data from it. SELECT COUNT(*) FROM dbo.TableName is also takeing forever to return the result.

  • July 7, 2005 at 12:03 pm

    #572010

    what does this return?

    Select max(rowcnt) as Total from dbo.SysIndexes where id = object_id('tablename') and indid < 2

  • July 7, 2005 at 12:18 pm

    #572022

    7

  • July 7, 2005 at 12:28 pm

    #572029

    Check if there are any open transaction. Any lock on any table (specially system tables).

  • July 7, 2005 at 12:32 pm

    #572031

    Hello ram4tech,

    Please, check Current Activity window under Management in the left pane of Enterprise Manager. Then under Current Activity click on Process Info and take a look at Blocked and Blocking fields on the very right. You have to scroll the horisontal scrollbar to see these fields. Also look at Wait Time and Wait Resource fields. Application field will be probably Query Analyzer for your process.

    Let us know if you see any locking. Refresh by Action ->Refresh. You will be able to refresh when you click on Current Activity entry. Process Info entry is not refreshing by itself.

    Yelena

    Regards,Yelena Varsha

  • July 7, 2005 at 12:37 pm

    #572033

    Hi Yelena,

    There are no process that are Blocked By or Blocking (they are all 0). There is nothing under Wait Resource and for Wait Type they are all "not waiting".

    I refreshed at Current Activity and looked under Process Info., nothing has changed.

  • July 7, 2005 at 12:41 pm

    #572036

    Hi Remi,

    I ran DBCC OPENTRAN and got the following result,

    Oldest active transaction:

    SPID (server process ID) : 68

    UID (user ID) : 8

    Name : implicit_transaction

    LSN : (8:107725:1)

    Start time : Jul 7 2005 12:11:44:327PM

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    I am not sure how to check to see if there are any locks on system tables.

    Thanks.

    R

  • July 7, 2005 at 12:41 pm

    #572035

    Start the query in one window of QA.

    Then run this in another window : exec sp_who2

    check the column blkby.

    Then for each row showing something different that "-"

    run dbcc inputbuffer (spid).

    That'll show you he statement that is blocking the other statement. Find the statement that you wish to stop, then run kill spid to stop that statement. That should solve the locking issue.

  • July 7, 2005 at 12:46 pm

    #572039

    There are no processes that are being blocked. So when I ran EXEC sp_who2 all the process have - under BlkBy.

  • July 7, 2005 at 12:48 pm

    #572041

    How long does it take to query another table?

  • July 7, 2005 at 12:53 pm

    #572044

    Less than a second to run four querries, returning more than 2000 records.

  • July 7, 2005 at 1:12 pm

    #572058

    Thank you Remi & Yelena:

    In the Process Info pane, I saw an open transaction (think its the same that I got when I ran DBCC OPENTRAN). I took a chance and killed that process. Then I was able to retrieve data from dbo.TableName.

    R

  • July 7, 2005 at 1:14 pm

    #572060

    Strange... there should have been something in the blkby column...

    Anyways glad you got this worked out.

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

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