locked
table not fetching any records in sql server2000 RRS feed

  • Question

  • Hi,

    I am using sql server 2000 and there is a table which has about 1,00,000 records and If I run the below query It does not give any result.

    select top 10 * from testtable

    But if I use nolock it is returning the result

    Select top 10 * from testtable (nolock)

    I thought some transaction has locked the table and killed all the SPIDs related to this db but still it is not fetching any rows. Is there any way to fix this.

    Thanks,
    Rakesh.


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Wednesday, January 30, 2013 10:27 AM

Answers

  • What do you mean "does not give any result"? Does the query hang, or do you get back an empty result set?

    In the former case, the query is blocked. Use sp_who to see who is blocking the query. If there is a value in the Blk column, this spid is blocking the spid on that row.

    If you get back an empty result set, you seem to have corruption in the database. Run DBCC CHECKDB.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Sarat (SS) Wednesday, January 30, 2013 12:06 PM
    • Marked as answer by Kalman Toth Friday, February 8, 2013 7:42 AM
    Wednesday, January 30, 2013 11:23 AM

All replies

  • Reeboot the service....
    Wednesday, January 30, 2013 10:30 AM
  • When you execute is it getting stuck and hence not completing or is it simply completing but not returning any rows?

    I'd suggest that if its the first then your table is locked, I'd suggest you find the oid of the table in question and then see if the oid appears in sp_lock


    ---
    Shaun Turner

    My Blog | My LinkedIn

    If you're in the UK please join The SQL Developers / DBA's user group for the UK on LinkedIn

    • Proposed as answer by Sarat (SS) Wednesday, January 30, 2013 12:06 PM
    Wednesday, January 30, 2013 10:42 AM
  • thanks guys, I found the locking sps using the below query and killed those. Now it is working fine.

    SELECT req_spid AS 'spid',
    DB_NAME(rsc_dbid) AS 'Database',
    OBJECT_NAME(rsc_objid) AS 'Name',
    rsc_indid AS 'Index',
    rsc_text AS 'Description',
    ResourceType = CASE WHEN rsc_type = 1 THEN 'NULL Resource'
    WHEN rsc_type = 2 THEN 'Database'
    WHEN rsc_type = 3 THEN 'File'
    WHEN rsc_type = 4 THEN 'Index'
    WHEN rsc_type = 5 THEN 'Table'
    WHEN rsc_type = 6 THEN 'Page'
    WHEN rsc_type = 7 THEN 'Key'
    WHEN rsc_type = 8 THEN 'Extent'
    WHEN rsc_type = 9 THEN 'RID (Row ID)'
    WHEN rsc_type = 10 THEN 'Application'
    ELSE 'Unknown'
    END,
    Status = CASE WHEN req_status = 1 THEN 'Granted'
    WHEN req_status = 2 THEN 'Converting'
    WHEN req_status = 3 THEN 'Waiting'
    ELSE 'Unknown'
    END,
    OwnerType =
    CASE WHEN req_ownertype = 1 THEN 'Transaction'
    WHEN req_ownertype = 2 THEN 'Cursor'
    WHEN req_ownertype = 3 THEN 'Session'
    WHEN req_ownertype = 4 THEN 'ExSession'
    ELSE 'Unknown'
    END,
    LockRequestMode =
    CASE WHEN req_mode = 0 THEN 'No access '
    WHEN req_mode = 1 THEN 'Sch-S (Schema stability)'
    WHEN req_mode = 2 THEN 'Sch-M (Schema modification)'
    WHEN req_mode = 3 THEN 'S (Shared)'
    WHEN req_mode = 4 THEN 'U (Update)'
    WHEN req_mode = 5 THEN 'X (Exclusive)'
    WHEN req_mode = 6 THEN 'IS (Intent Shared)'
    WHEN req_mode = 7 THEN 'IU (Intent Update)'
    WHEN req_mode = 8 THEN 'IX (Intent Exclusive)'
    WHEN req_mode = 9 THEN 'SIU (Shared Intent Update)'
    WHEN req_mode = 10 THEN 'SIX (Shared Intent Exclusive)'
    WHEN req_mode = 11 THEN 'UIX (Update Intent Exclusive)'
    WHEN req_mode = 12 THEN 'BU. (Bulk operations)'
    WHEN req_mode = 13 THEN 'RangeS_S'
    WHEN req_mode = 14 THEN 'RangeS_U'
    WHEN req_mode = 15 THEN 'RangeI_N'
    WHEN req_mode = 16 THEN 'RangeI_S'
    WHEN req_mode = 17 THEN 'RangeI_U'
    WHEN req_mode = 18 THEN 'RangeI_X'
    WHEN req_mode = 19 THEN 'RangeX_S'
    WHEN req_mode = 20 THEN 'RangeX_U'
    WHEN req_mode = 21 THEN 'RangeX_X'
    ELSE 'Unknown'
    END
    FROM master.dbo.syslockinfo
    GO


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Wednesday, January 30, 2013 11:22 AM
  • What do you mean "does not give any result"? Does the query hang, or do you get back an empty result set?

    In the former case, the query is blocked. Use sp_who to see who is blocking the query. If there is a value in the Blk column, this spid is blocking the spid on that row.

    If you get back an empty result set, you seem to have corruption in the database. Run DBCC CHECKDB.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Sarat (SS) Wednesday, January 30, 2013 12:06 PM
    • Marked as answer by Kalman Toth Friday, February 8, 2013 7:42 AM
    Wednesday, January 30, 2013 11:23 AM