July 26, 2005 at 3:06 am
I try to reduce ma database size from 35 Go to 20 Go
I deleted 100 000 000 rows out of a total of 200 000 000 rows in my larger table.
The reserved space for this table was not reduced.
How can I reduce it ?
I tried a database maintenance plan on a test database but it is too long and keeps me from making any select statement on the table (during execution of the plan)!!!
My production database must be on 24h a day..
LM
July 26, 2005 at 9:52 am
right click on database properties
all tasks / shrink database / shink file
I compressed each physical data file and truncated free space
2 or 3 times and reduced the data files that involved deleted records.
other data files remained the same.
it did not disturbed database users even though it took some minutes
LM
July 26, 2005 at 9:54 am
right click on database properties
all tasks / shrink database / shink file
I compressed each physical data file and truncated free space
2 or 3 times and reduced the data files that involved deleted records.
other data files remained the same.
it did not disturbed database users even though it took some minutes
LM
July 26, 2005 at 9:56 am
reserved space for the large table remain the same !
only compression really made space available
in fact I did not solve the problem
LM
July 27, 2005 at 3:31 am
MS advises against compression. SQL 2005 beta2 won't install in a compressed partition. I heard/read that the algorithms for writing pages aren't designed to be safe for compressed volumes, for recovery.
SQL EM provides an option to move pages to the beginning before shrinking. It takes time, but to shrink a database file you may have to select that option.
July 28, 2005 at 6:24 am
You have to have a clustered index on your table to be able to reclaim space from deleted records. Put one and see that the reserved space for that table goes down.
DBCC UPDATEUSAGE is also important to run to have a correct representation about reserved pages for your table.
//Hans
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply