help with oversized table

I am running sql7 and I have 4 or so tables which are chewing up space which
I would like to recover.
Following is sp_spaceused results.
Is there anyway I can recover some of this space?
name rows reserved data
index_size unused
-- -- -- -- --
-- --
INF_FTRANS_LNE 31392337 83914424 KB 20873856 KB
5969360 KB 57071208 KB
thanksHi
Rebuild your clustered index on each table with a fillfactor of 90%
If you don't have one, create one on a highly selectable column.
Regards
Mike
"evan b" wrote:
> I am running sql7 and I have 4 or so tables which are chewing up space which
> I would like to recover.
> Following is sp_spaceused results.
> Is there anyway I can recover some of this space?
>
> name rows reserved data
> index_size unused
> -- -- -- -- --
> -- --
> INF_FTRANS_LNE 31392337 83914424 KB 20873856 KB
> 5969360 KB 57071208 KB
> thanks
>
>|||Thanks for the reply - could you explain the mechanics behind this?
ie how will it recover the space?
evan
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:EFF2B070-3DB3-45B4-AC54-5317C27439B7@.microsoft.com...
> Hi
> Rebuild your clustered index on each table with a fillfactor of 90%
> If you don't have one, create one on a highly selectable column.
> Regards
> Mike
> "evan b" wrote:
> > I am running sql7 and I have 4 or so tables which are chewing up space
which
> > I would like to recover.
> > Following is sp_spaceused results.
> >
> > Is there anyway I can recover some of this space?
> >
> >
> > name rows reserved data
> > index_size unused
> -- -- -- -- --
--
> > -- --
> > INF_FTRANS_LNE 31392337 83914424 KB 20873856 KB
> > 5969360 KB 57071208 KB
> >
> > thanks
> >
> >
> >|||Hi
A clustered index rebuild re-arranges all the data in the table as a
clustered index cuases data to be laid down in the table according to it's
sequence.. In effect, look at it like a "compact" that occurs in Access, but
for only one table. The 90% fill factor results in the data pages having 10%
free space to allow for inserts.
For a good description on clustered indexes, look in BOL and get Kalen
Delaney's book "Inside SQL Server 2000" (updated version of her "Inside SQL
Server 7.0" book)
Regards
Mike
"evan b" wrote:
> Thanks for the reply - could you explain the mechanics behind this?
> ie how will it recover the space?
> evan
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:EFF2B070-3DB3-45B4-AC54-5317C27439B7@.microsoft.com...
> > Hi
> >
> > Rebuild your clustered index on each table with a fillfactor of 90%
> > If you don't have one, create one on a highly selectable column.
> >
> > Regards
> > Mike
> >
> > "evan b" wrote:
> >
> > > I am running sql7 and I have 4 or so tables which are chewing up space
> which
> > > I would like to recover.
> > > Following is sp_spaceused results.
> > >
> > > Is there anyway I can recover some of this space?
> > >
> > >
> > > name rows reserved data
> > > index_size unused
> >
> > -- -- -- -- --
> --
> > > -- --
> > > INF_FTRANS_LNE 31392337 83914424 KB 20873856 KB
> > > 5969360 KB 57071208 KB
> > >
> > > thanks
> > >
> > >
> > >
>
>|||Ok I was aware of moving a clustered index to a different filegroup
resulting in a move of the datapages which effectively moves the table with
it.
So this is just a different spin on the same process.
Thanks
lucky its the weekend coming up and thankgod for vpn
evan
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ED51147D-8F04-4BE7-899B-8F5508EF5303@.microsoft.com...
> Hi
> A clustered index rebuild re-arranges all the data in the table as a
> clustered index cuases data to be laid down in the table according to it's
> sequence.. In effect, look at it like a "compact" that occurs in Access,
but
> for only one table. The 90% fill factor results in the data pages having
10%
> free space to allow for inserts.
> For a good description on clustered indexes, look in BOL and get Kalen
> Delaney's book "Inside SQL Server 2000" (updated version of her "Inside
SQL
> Server 7.0" book)
> Regards
> Mike
> "evan b" wrote:
> > Thanks for the reply - could you explain the mechanics behind this?
> > ie how will it recover the space?
> >
> > evan
> >
> > "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> > news:EFF2B070-3DB3-45B4-AC54-5317C27439B7@.microsoft.com...
> > > Hi
> > >
> > > Rebuild your clustered index on each table with a fillfactor of 90%
> > > If you don't have one, create one on a highly selectable column.
> > >
> > > Regards
> > > Mike
> > >
> > > "evan b" wrote:
> > >
> > > > I am running sql7 and I have 4 or so tables which are chewing up
space
> > which
> > > > I would like to recover.
> > > > Following is sp_spaceused results.
> > > >
> > > > Is there anyway I can recover some of this space?
> > > >
> > > >
> > > > name rows reserved data
> > > > index_size unused
> > >
> >
> -- -- -- -- --
> > --
> > > > -- --
> > > > INF_FTRANS_LNE 31392337 83914424 KB 20873856 KB
> > > > 5969360 KB 57071208 KB
> > > >
> > > > thanks
> > > >
> > > >
> > > >
> >
> >
> >