How much space does MS SQL 7.0 need for reorganizing data

We are running MS SQL 7.0 and we use a database maintenance plan that
does, among other things, the "Reorganize data and index pages" (under
the "Optimizations" tab). We have had this optimization fail for some
of the databases due to file space shortages. I have a couple of
questions that may help to resolve this issue and aid my understanding
of how MS SQL Server 7.0 allocates space.
Assumptions: We have a single database TEST which has a single
filegroup (PRIMARY) and that this filegroup has only two files (one
for the data and one for the transaction log). Furthemore, there is an
infinite amount of free disk space available to the system.
1. If the data file has size N (in megabytes) how much of this file
must be free space to guarantee that the above optimization will not
fail due to lack of space in the PRIMARY filegroup? (For example, if
the data file has size 1000 megabytes of which 700 is currently used,
is 300 megabytes enough free space?)
2. The same question for the database's transaction log file.
3. If the data and transaction files are full and the "Reorganize data
and index pages" is run (so, there is not enough space), will checking
the "Automatically grow file" property be enough to ensure that the
system will grow the two files sufficiently to allow the re-indexing
to complete? Or do the files have to have enough space _before_ the
re-indexing?
Thanks, A. LewenbergIt is not really the size of the data file, but the size of the largest
table you wish to re-index... You need 1.25 * table size to drop/recreate
the clustered index...If you intend to do 2 tables at the same time, you
have to have extra space for both... This is in addition to the current size
used by the tables. But the 1.25 * space is used only temporarily...
The log file shouldn't be much ( but i am not sure).
The reorging can use lots of log space however...same as the size of the
index (I think)...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"A. Lewenberg" <adam@.macrotex.net> wrote in message
news:6bd7331b.0403011328.7618c1de@.posting.google.com...
> We are running MS SQL 7.0 and we use a database maintenance plan that
> does, among other things, the "Reorganize data and index pages" (under
> the "Optimizations" tab). We have had this optimization fail for some
> of the databases due to file space shortages. I have a couple of
> questions that may help to resolve this issue and aid my understanding
> of how MS SQL Server 7.0 allocates space.
> Assumptions: We have a single database TEST which has a single
> filegroup (PRIMARY) and that this filegroup has only two files (one
> for the data and one for the transaction log). Furthemore, there is an
> infinite amount of free disk space available to the system.
> 1. If the data file has size N (in megabytes) how much of this file
> must be free space to guarantee that the above optimization will not
> fail due to lack of space in the PRIMARY filegroup? (For example, if
> the data file has size 1000 megabytes of which 700 is currently used,
> is 300 megabytes enough free space?)
> 2. The same question for the database's transaction log file.
> 3. If the data and transaction files are full and the "Reorganize data
> and index pages" is run (so, there is not enough space), will checking
> the "Automatically grow file" property be enough to ensure that the
> system will grow the two files sufficiently to allow the re-indexing
> to complete? Or do the files have to have enough space _before_ the
> re-indexing?
> Thanks, A. Lewenberg