SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help - Can't query a single table


Help - Can't query a single table

Author
Message
Mark Eckeard
Mark Eckeard
SSChasing Mays
SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)

Group: General Forum Members
Points: 617 Visits: 505
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_tblAllocations nonclustered, unique, unique key located on PRIMARY Serial_Nbr
PK_dbo_tblAllocations clustered, unique, primary key located on PRIMARY IngAllocationsID

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

name rows reserved data index_size unused
============================================================================
dbo_tblAllocations 129669 120112 KB 54432 KB 5624 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



Jeff Kelly-310227
Jeff Kelly-310227
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 438
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
bdloving 4446
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 18
drop the indexes

try a simple select top 10 * from table

rebuild the indexes

or

export, drop and rebuild the table
Michael L John
Michael L John
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5783 Visits: 8227
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
Mark Eckeard
SSChasing Mays
SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)

Group: General Forum Members
Points: 617 Visits: 505
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search