Database Size problems

I have a database that has grown much larger than I expect. There is a
specific table that seems to be unusally large. If I copy the data from that
table out into a temp table then copy it back in, it frees up over 90% of th
e
space that it was taking up.
I cannot alter the database structure at this point so is there a better way
to free this space up ? I have tried things like shrinkdb and shrinkfile and
whilst they create a little space its not anywhere near the level of moving
the data.
Does anyone have any ideas ?
SiSimon
How large is your database? Do you have limited space on the disk?
1) You can put the large table on different physical disk array
2) You use partition to "divide" the table
BTW , what is the version are you using?
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:A58E5518-5D24-4FBC-AE57-C50D822F75BB@.microsoft.com...
>I have a database that has grown much larger than I expect. There is a
> specific table that seems to be unusally large. If I copy the data from
> that
> table out into a temp table then copy it back in, it frees up over 90% of
> the
> space that it was taking up.
> I cannot alter the database structure at this point so is there a better
> way
> to free this space up ? I have tried things like shrinkdb and shrinkfile
> and
> whilst they create a little space its not anywhere near the level of
> moving
> the data.
> Does anyone have any ideas ?
> Si|||How many indexes do you have on that table? How often do you rebuild
the indexes?
It looks like your table had a lot of fragmentation, which as a result
made t use a lot more space than necessary.
Markus|||The database is around 40Gig
But one table is 35G of that. When I copy the data out and then back in it
becomes around 3G.
I`m using SQL2000.
I know I can split the data onto seperate disks but I was hoping to write a
job that will shrink the data down automatically.
"Uri Dimant" wrote:

> Simon
> How large is your database? Do you have limited space on the disk?
> 1) You can put the large table on different physical disk array
> 2) You use partition to "divide" the table
> BTW , what is the version are you using?
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:A58E5518-5D24-4FBC-AE57-C50D822F75BB@.microsoft.com...
>
>|||Simon wrote:
> The database is around 40Gig
> But one table is 35G of that. When I copy the data out and then back in it
> becomes around 3G.
> I`m using SQL2000.
> I know I can split the data onto seperate disks but I was hoping to write
a
> job that will shrink the data down automatically.
>
As Markus said, this sounds like a fragmentation problem. Use DBCC
SHOWCONTIG to check the fragmentation of the indexes on that table, and
have a look at this script of mine:
http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com