Data File shrink

I have a data file in which there is a table containing 5,000,000 rows. I
deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
a clustered primary key index, and the table also has a text column. The
only other tables in the database are small lookup tables.
The datafile size before the delete was 75GB. After shrinking the datafile
(with target size 25000MB), the size decreased to 68GB.
The results of the shrinkfile were the following:
currentsize 8591936
minimumsize 128
usedpages 8206784
estimatedpages 8206784
Is there anything short of recreating a new table using select into, or
bcp, that might reclaim space?
--
Message posted via http://www.sqlmonster.comThe space used by the deleted text cannot be reclaimed in SQL Server 2000.
The (clumsy) solution is to bcp-out then in. In SQL Server 2005 we've fixed
this by adding LOB compaction to the shrink and defrag operations.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:127b65beb25d4ccab668ff9ff8df8322@.SQLMonster.com...
> I have a data file in which there is a table containing 5,000,000 rows. I
> deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
> a clustered primary key index, and the table also has a text column. The
> only other tables in the database are small lookup tables.
> The datafile size before the delete was 75GB. After shrinking the datafile
> (with target size 25000MB), the size decreased to 68GB.
> The results of the shrinkfile were the following:
> currentsize 8591936
> minimumsize 128
> usedpages 8206784
> estimatedpages 8206784
> Is there anything short of recreating a new table using select into, or
> bcp, that might reclaim space?
> --
> Message posted via http://www.sqlmonster.com|||It is most likely due to the text columns. Reindexing and shrinking does
little or nothing to help reduce wasted space in them. BCP out, truncate
and BCP back in is the cleanest method. SQL2005 has features to address
this but not 2000.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:127b65beb25d4ccab668ff9ff8df8322@.SQLMonster.com...
>I have a data file in which there is a table containing 5,000,000 rows. I
> deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
> a clustered primary key index, and the table also has a text column. The
> only other tables in the database are small lookup tables.
> The datafile size before the delete was 75GB. After shrinking the datafile
> (with target size 25000MB), the size decreased to 68GB.
> The results of the shrinkfile were the following:
> currentsize 8591936
> minimumsize 128
> usedpages 8206784
> estimatedpages 8206784
> Is there anything short of recreating a new table using select into, or
> bcp, that might reclaim space?
> --
> Message posted via http://www.sqlmonster.com|||When you say truncate, is that the same as:
delete [tablename]?
--
Message posted via http://www.sqlmonster.com|||Well not exactly. A truncate is much faster than a Delete since it simply
deallocates all the pages associated with the table and it's indexes. There
is minimal logging to the transaction log. A Delete with no where clause
will log each and every row to the log and will be much slower on a larger
system. However there are some caveats to truncate. One is that you can
not issue this on a table with RI unless you drop that first. Two it does
not update the statistics but the idea is you will BCP it right back in
anyway.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ff665e017d394681a275d526eb8acf92@.SQLMonster.com...
> When you say truncate, is that the same as:
> delete [tablename]?
> --
> Message posted via http://www.sqlmonster.com|||There is an option to reorganize data pages in the maintenance plan wizard.
Does this reclaim any space when I have already performed a shrinkfile
operation?
--
Message posted via http://www.sqlmonster.com|||No - it will take up more space. Under the covers it rebuilds the indexes
using DBCC DBREINDEX. Each index rebuild needs 1.2x the size of the index in
extra space. If you shrink then reorganize, your database will grow again.
If you reorganize then shrink, shrink will fragment the indexes again. Also,
reorganize doesn't touch text pages at all in SQL Server 2000.
There's no way in SQL Server 2000 to get the space back from the deleted
text rows without bcp out/in.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f3800333aebc4d99bcd34685895ed4cd@.SQLMonster.com...
> There is an option to reorganize data pages in the maintenance plan
wizard.
> Does this reclaim any space when I have already performed a shrinkfile
> operation?
> --
> Message posted via http://www.sqlmonster.com|||The Reorganize part of the MP is a DBREINDEX. A DBREINDEX does not reclaim
any space per say. It may free up a few pages based on the fill factor
depending on how full they were before the reindexing. There is a different
option in the wizard to free up space and will essentially do a DBCC
SHRINKFILE. It should be turned off as per the other conversations we had
regarding this subject.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f3800333aebc4d99bcd34685895ed4cd@.SQLMonster.com...
> There is an option to reorganize data pages in the maintenance plan
> wizard.
> Does this reclaim any space when I have already performed a shrinkfile
> operation?
> --
> Message posted via http://www.sqlmonster.com|||So if I started at 5 million records and 75GB, deleted 2.5 million and now
have 68GB, BCP export, truncate the table, BCP import back into the
truncated table, do I then run a shrinkfile again to further reduce the
68GB file? I do not believe that bcp will shrink the file, so do I perform
dbcc shrinkfile, or is there a better way once the import is completed?
--
Message posted via http://www.sqlmonster.com|||You would do it in this order:
BCP out the table
Truncate the table
DBCC SHRINKFILE
BCP back into the table
But you have to be careful that you don't shrink the file too much or it
will simply grow again when you BCP the data back in. Why do you want to
shrink it at all? If there is any chance at all the data will get that
large again it is best to leave it at that size. You also need room for
reindexing.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d29d78ea6b5a4cab9e2566bb93b3bf97@.SQLMonster.com...
> So if I started at 5 million records and 75GB, deleted 2.5 million and now
> have 68GB, BCP export, truncate the table, BCP import back into the
> truncated table, do I then run a shrinkfile again to further reduce the
> 68GB file? I do not believe that bcp will shrink the file, so do I perform
> dbcc shrinkfile, or is there a better way once the import is completed?
> --
> Message posted via http://www.sqlmonster.com|||This is an archive table, that has essentially served its purpose. We are
no longer archiving to it, but instead only deletes will be performed
against it in the future. No further growth anticipated, just the opposite,
a gradual reduction in size.
If I remove the indexes before performing the import, and given that I
allow enough space in the shrunken data file to recreate the indexes, does
this process of recreating the indexes after the import (having a clustered
primary key identity index) cause any fragmentation issues, versus leaving
the indexes in place during the import?
--
Message posted via http://www.sqlmonster.com|||You will need at least 1.2 times the size of the table (in the case of the
clustered index) and for each non-clustered index in free space for this.
If you only have a small amount above that free the chances are the indexes
will not be contiguous in the file but will still be built. The more free
space you have the better chance that the indexes can be placed in a
contiguous area in the file when rebuilt. A little bit of fragmentation
won't hurt.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:fb453ef7c78943ce9b5af4e75cfb224e@.SQLMonster.com...
> This is an archive table, that has essentially served its purpose. We are
> no longer archiving to it, but instead only deletes will be performed
> against it in the future. No further growth anticipated, just the
> opposite,
> a gradual reduction in size.
> If I remove the indexes before performing the import, and given that I
> allow enough space in the shrunken data file to recreate the indexes, does
> this process of recreating the indexes after the import (having a
> clustered
> primary key identity index) cause any fragmentation issues, versus leaving
> the indexes in place during the import?
> --
> Message posted via http://www.sqlmonster.comsql