April 20, 2008 at 7:50 pm
We have a sql server table which is occupying 33GB of space. This table has only 2 million records and not sure which column is occupying much usage.
sp_spaceused of the table
name rows reserved data index_size unused
====================================================================================
T12 2060865 33802968 KB 33714552 KB 83856 KB 4560 KB
* There are data corruption
* only one column is having 'text' datatype and it is having most of NULL values
* No data and index fragmentation
below is the structure of the table:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T12]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T12]
GO
CREATE TABLE [dbo].[T12] (
[C1] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[C2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C3] [int] NOT NULL ,
[C4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[C6] [int] NOT NULL ,
[C7] [int] NOT NULL ,
[C8] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[C536870912] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870913] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870914] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870915] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870916] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870917] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870918] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870919] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870920] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870921] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870922] [varchar] (130) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870923] [int] NULL ,
[C536870924] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870925] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870926] [int] NULL ,
[C536870927] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870928] [int] NULL ,
[C536870929] [int] NULL ,
[C536870930] [int] NULL ,
[C536870931] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870933] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870934] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870935] [int] NULL ,
[C536870936] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870937] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870938] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870940] [int] NULL ,
[C536870944] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870945] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870946] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870947] [int] NULL ,
[C900400000] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C901200000] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C901200001] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536871049] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C901200003] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C901200002] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870948] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870950] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870951] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870954] [int] NULL ,
[C536870955] [int] NULL ,
[C536870958] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870959] [int] NULL ,
[C536870960] [int] NULL ,
[C536870961] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870962] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870963] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870964] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870965] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870966] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870967] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870968] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870971] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870972] [varchar] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C536870973] [int] NULL ,
[C536870974] [int] NULL ,
[C536870975] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [IT12] ON [dbo].[T12]([C1]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [I12_536870912_1] ON [dbo].[T12]([C536870912]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
/****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_292_3] ON [dbo].[T12] ([C3]) ')
GO
/****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [hind_292_35] ON [dbo].[T12] ([C536870940]) ')
GO
=============================================================
I tried to find the number of characters in each column and based on its datatype tried to find the space used by each column. But not able to find the total number of characters on each column having NuLL values.
Any query can be used?
or
any other strategy to find the abnormality of this space usage?
BR,
Parthi
April 20, 2008 at 7:53 pm
there is a typo,
there are no data corruption in the table.
April 21, 2008 at 8:08 am
see
http://www.sqlservercentral.com/Forums/Topic487683-8-1.aspx
Parthi, please don't cross post.
---------------------------------------------------------------------
April 21, 2008 at 10:30 am
sorry for the confusion. Please delete the thread.
BR,
Parthi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply