Re: sp_spaceused reporting anomolies?

From: Jacco Schalkwijk (NOSPAMjaccos_at_eurostop.co.uk)
Date: 03/17/04 

Date: Wed, 17 Mar 2004 21:34:21 -0000

What you see is normal behaviour if you have a table without a clustered
index and you delete all the rows from it. All the datapages will still be
assigned to the table, even if they are empty. You can reclaim the pages
taken up by the table by either:
- using TRUNCATE TABLE TableX if the table is not referenced by foreign keys
- creating a clustered index on the table and dropping it again, although,
as it in general is a good idea to have a clustered index on a table, it
might be better to leave it there. 

-- 
Jacco Schalkwijk
SQL Server MVP
"mikron2" <anonymous@discussions.microsoft.com> wrote in message
news:F25A2C90-F5BC-4D3A-BAC5-BD6E80D1D8EE@microsoft.com...
> Here's the DDL script for the table in question:
>
> CREATE TABLE [dbo].[TableX] (
> [ColA] [int] IDENTITY (1, 1) NOT NULL ,
> [ColB] [varchar] (40) NULL ,
> [ColC] [varchar] (30) NOT NULL ,
> [ColD] [int] NULL ,
> [ColE] [datetime] NULL ,
> [ColF] [smallint] NULL ,
> [ColG] [varchar] (30) NULL ,
> [ColH] [varchar] (30) NULL ,
> [ColI] [varchar] (30) NULL ,
> [ColJ] [varchar] (30) NULL ,
> [ColK] [float] NULL ,
> [ColL] [float] NULL ,
> [ColM] [float] NULL ,
> [ColN] [smallint] NULL ,
> [ColO] [smallint] NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[TableX] ADD
> CONSTRAINT [PK_TableX] PRIMARY KEY  NONCLUSTERED
> (
> [ColA],
> [ColC]
> )  ON [PRIMARY]
> GO
>
> When I run sp_spaceused on the table, here's what I get:
>
> name     rows   reserved      data          index_size    unused
> -------- ------ ------------- ------------- ------------- ----------------
-- 
> TableX   0      121440 KB     117216 KB     56 KB         4168 KB
>
> Now, if the table has zero rows (I did a 'select * from TableX' and got
the same result), why is there ANY space used?  I've also run about every
DBCC command I can think of that would pertain to this problem - no change
in the sp_spaceused output.  Also, when I ran the 'select * from TableX'
query, it took ~ 5 seconds for the query to execute.  I don't know if the
table was truncated or 'delete from TableX' was executed.
>
> There are a handful of tables in the database with this same problem (not
nearly as drastic!), so I'm wondering if there is some database corruption
at work here that isn't being reported by DBCC CHECKDB, etc.
>
> TIA for any help.
>
> Mike Stuart
> mstuart_nospam@gates.com
>

Relevant Pages

  • Re: sp_spaceused reporting anomolies?
    ... took up space at some point), delete, truncate, drop/re-create? ... query, it took ~ 5 seconds for the query to execute. ... so I'm wondering if there is some database corruption ...
    (microsoft.public.sqlserver.server)
  • Re: Qry not pulling bound column problem
    ... What are TableX and TableY? ... The UNION query will show ALL records in TableX and ALL records in TableY, ...
    (microsoft.public.access.queries)
  • Re: Upgrading from 9.21 to 10 planning
    ... SELECT tableX.* FROM tableX WHERE tableX.att1=? ... If you run the query a few times IDS crashes with following trace from af ... Statement caching is having a problem with what you are doing." ...
    (comp.databases.informix)
  • Re: Starting Date of Week(Week Number Issue)
    ... from tablex s INNER JOIN tabley b on group by DAtenameorder ... by avg desc ... this is the query i had....which i formed with great difficulty, ...
    (microsoft.public.sqlserver.programming)
  • Re: query enhancement
    ... The query, as it now stands, tests for the presence of 'null' in one ... select countfrom tableX where columnX IS NULL; ... Presuming you have an index this may produce an index fast full scan ...
    (comp.databases.oracle.server)