XP_SQLMaint

[Dan]

I am running SQL Server 2000 SP3 on Win2k Server. I have just built a
new maintenance plan that first backs up the database, then analyzes
it for allocation and rebuilds indexes. Below is what I am using:

exec xp_sqlmaint '-S SCDEV -U "sa" -P <my_sa_password> -D ScProd
-PlanName Web_DB_Backup
-BkUpDB <My_Path> -BkUpMedia DISK'

exec xp_sqlmaint '-S SCDEV -U "sa" -P "my_sa_password" -D SCProd -CkDB
-CkAl
-CkCat -RebldIdx 99 -HtmlRpt <my_path>\SCWeb_DB_Backup_Log.html"
-DelHtmlRpt 5days'

It seems that after I run this, the database size grew to 5x it's
regular size. I thought this had to do with the index rebuild. When
I tested it, it ran for nearly 2.5 hours. I checked the datafile and
it is ~40GB when it used to be ~8GB. I will have to try and shrink
the database to get it back to size, but why did the data file grow so
astronomically from a maintenance plan?

Any help would be greatly appreciated.

[Louis]

do*****@amcad.com (Dan) wrote in message news:<bd**************************@posting.google. com>...

I am running SQL Server 2000 SP3 on Win2k Server. I have just built a
new maintenance plan that first backs up the database, then analyzes
it for allocation and rebuilds indexes. Below is what I am using:

exec xp_sqlmaint '-S SCDEV -U "sa" -P <my_sa_password> -D ScProd
-PlanName Web_DB_Backup
-BkUpDB <My_Path> -BkUpMedia DISK'

exec xp_sqlmaint '-S SCDEV -U "sa" -P "my_sa_password" -D SCProd -CkDB
-CkAl
-CkCat -RebldIdx 99 -HtmlRpt <my_path>\SCWeb_DB_Backup_Log.html"
-DelHtmlRpt 5days'

It seems that after I run this, the database size grew to 5x it's
regular size. I thought this had to do with the index rebuild. When
I tested it, it ran for nearly 2.5 hours. I checked the datafile and
it is ~40GB when it used to be ~8GB. I will have to try and shrink
the database to get it back to size, but why did the data file grow so
astronomically from a maintenance plan?

Any help would be greatly appreciated.

Probably because of "-RebldIdx 99", which specifies freespace of 99%
or fillfactor of 1%.

BOL:
-RebldIdx free_space
Specifies that indexes on tables in the target database should be
rebuilt by using the free_space percent value as the inverse of the
fill factor. For example, if free_space percentage is 30, then the
fill factor used is 70. If a free_space percentage value of 100 is
specified, then the indexes are rebuilt with the original fill factor
value.