Help - Can't query a single table

• Mark Eckeard

  Hall of Fame

  Points: 3462

  More actions

  February 20, 2013 at 10:20 am

  #289884

  Hi All,

  I have a table in SQL Server 2000 that cannot be queried. Even a simple "select count(*) from dbo_tblAllocations" runs with no end. I've let this query run for 12 minutes with no results returned.

  Here's the table:

  CREATE TABLE [dbo].[dbo_tblAllocations] (

  [IngAllocationsID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,

  [Serial_Nbr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

  [IngAccountID] [int] NULL ,

  [ACCT] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [Invoice_Nbr] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [Invoice_Date] [smalldatetime] NULL ,

  [Process_Date] [smalldatetime] NULL ,

  [Records] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [IngSubAccountID] [int] NULL ,

  [SUB_ACCT] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [BTN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [IngVendorID] [int] NOT NULL ,

  [Vendor] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [IngLocationsID] [int] NULL ,

  [IngNumbersID] [int] NULL ,

  [DDN] [nvarchar] (55) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [TYPE] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [DESCRIPTION] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [UNIT] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

  [Per_Line_Chg] [float] NULL ,

  [Line_Chg] [float] NULL ,

  [CDR_Chg] [float] NULL ,

  [Acct_Per_Line_Chg] [float] NULL ,

  [Inv_Total_Chg] [float] NULL ,

  [MNTHLY] [float] NULL ,

  [OTC] [float] NULL ,

  [IBLD] [float] NULL ,

  [OBLD] [int] NULL ,

  [OUC] [float] NULL ,

  [TAX] [float] NULL ,

  [LATE] [float] NULL ,

  [ADJ] [float] NULL ,

  [CM] [int] NULL ,

  [UM] [int] NULL ,

  [FM] [int] NULL

  ) ON [PRIMARY]

  Here's the indexes on the table:

  index_name index_description index_keys

  ============================================================================

  IX_dbo_tblAllocationsnonclustered, unique, unique key located on PRIMARYSerial_Nbr

  PK_dbo_tblAllocationsclustered, unique, primary key located on PRIMARYIngAllocationsID

  Here's what I get when I run sp_spaceused on the table:

  name rows reserved data index_size unused

  ============================================================================

  dbo_tblAllocations129669 120112 KB54432 KB5624 KB 60056 KB

  Does anyone know what could be causing this and how to fix it? From what I can tell it's just this one table and the others look fine.

  Thanks,

  Mark

• jkelly95z@yahoo.com

  SSChasing Mays

  Points: 620

  More actions

  February 21, 2013 at 6:08 am

  #1589905

  whats the wait type when you are running the select?

  can you do select top 100

  are you doing this from the server, or from a client?

• bdloving 4446

  SSC Enthusiast

  Points: 198

  More actions

  February 21, 2013 at 12:42 pm

  #1590103

  drop the indexes

  try a simple select top 10 * from table

  rebuild the indexes

  or

  export, drop and rebuild the table

• Michael L John

  One Orange Chip

  Points: 27060

  More actions

  February 21, 2013 at 1:07 pm

  #1590113

  bdloving 4446 (2/21/2013)

  ---

  drop the indexes

  try a simple select top 10 * from table

  rebuild the indexes

  or

  export, drop and rebuild the table

  Don't do this! This is certainly not a first step, and probably not a last step.

  Find out what the cause may be.

  Can you add a WHERE clause, to filter a single row, and does it return then?

  Do you have any open transactions that may be blocking?

  Is there some sort of process running that you may not be aware of that can be causing deadlocks?

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/

• Mark Eckeard

  Hall of Fame

  Points: 3462

  More actions

  February 21, 2013 at 4:02 pm

  #1590161

  All,

  I couldn't query the table regardless of the actual SQL. I tried limiting the return data but that didn't work.

  Also tried using DTS to import the table to the same DB in hopes of playing around with the copied table w/o causing any harm to the original. Same results - it hangs.

  After I did an sp_who, I noted a user was still connected to the database and this user was set up to perform an import in to this table. That seemed a little coincidental and I later found out the credentials were shared so others were using it. I killed the 2 connections and the problem went away.

  Thanks to everyone for the suggestions.

  Mark