Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MuadDBA
628 Posts |
 Posted - 2003-05-21 : 16:13:24
|
| I am rebuilding indexes on my DB using a maintenance plan, and I set it to rebuild each index with 5% free space.It seems that whenever I do this, however, it causes my DB (not the tlog, the DB itself) to grow by as much as 50% - 100%. Right now I have a 19GB datbase that swelled to 30GB after reindexing stuff.Does the maintencnace plan rebuild indexes differently than DBCC DBREINDEX?? Some of my other servers were set up with DBCC DBREINDEX statements instead and I don't seem to encounter this problem.SQL 7.0 SP3Any advice (beyond scripting the index rebuilds, which I am in the process of doing) would be appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-21 : 16:17:02
|
| The maintenance plan uses the DBCC DBREINDEX statement as well (you can see what statement it ran by looking at the SQL Server Error Log). You probably used a different fill factor (would be 95 in your case) for the DBCC DBREINDEX than what you specified in the maintenance plan. So what fill factor did you use in your DBCC DBREINDEX statement? If it isn't 95, then use whatever it is in your maintenance plan. They should then be the same.Tara |
 |
|
|
MuadDBA
628 Posts |
Posted - 2003-05-21 : 16:22:13
|
| ?I don't see any messages about the reindexing of databases in my error log, whether I use DBCC or the maintenance plan.The servers which run the DBCC don't specify free space, they use the free space specified when the index was created, probably 0.Regardless of this, I still have no idea why reindexnig a table and telling it to leave 5% free space on the index would result in a 100% increase in the size of my DB. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-21 : 16:42:05
|
quote:
I don't see any messages about the reindexing of databases in my error log, whether I use DBCC or the maintenance plan.
Hmmm, don't why it doesn't show up for you.quote:
The servers which run the DBCC don't specify free space, they use the free space specified when the index was created, probably 0.
When you create your indexes, you set it so that the pages are complete full? Is your database primarily used for reading? If it is mostly used for writing (like mine are), then you should have a fillfactor of 80 or less.quote:
Regardless of this, I still have no idea why reindexnig a table and telling it to leave 5% free space on the index would result in a 100% increase in the size of my DB.
I don't have an explanation for this either, but I'm sure it all works out mathematically if it were calculated.Tara |
|
|
|
MuadDBA
628 Posts |
Posted - 2003-05-21 : 16:47:05
|
| Yes, I set the pages to completely full, even though there are lots of reads and writes going to this database, some of the tables are on the order of 10-12GB due to all the old data, and I know we don't ad an additional 20% of that on a weekly or even monthly basis, so 5% or less is fine with me.I am quite sure the math DOESN'T work out, there's really no explanation for increasing my DB size by 100% when I ask to have 5% of free space on my indexes. I can only imagine what 20% free space would do to me. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-05-21 : 17:17:05
|
| What is the row size for the large table that you mentioned?If, for example, the row size is in the range of 4K, then with no free space, then you could fit 2 rows per page. However, since you now want 5% free, you can't put 2 rows anymore. That would double the size of that table.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
MuadDBA
628 Posts |
Posted - 2003-05-23 : 18:09:59
|
| Not even close. If anything, my widest tables come out to row sizes of maybe a few hundred bytes if someone maxed out all the varchar columns, which rarely happens. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-05-24 : 04:48:06
|
| Ok, It just took a little research.Here is an explanation to the space usage when creating an index. When you issue a index creation statement or DBCC DBREINDEX statement, the activity involves laying out all the data pages as another copy and then re-sorting them. SQL Server will then delete the original set of data pages and free up the space. This means that we require at least the table size as free space plus the space required for sorting which means that the total space will be = table size + space required for sorts. This roughly amounts to a required free space of 1.2 times the (largest) table size. By design, we do not release the space that is free in a database to the Operating System. With rebuilding the clustered indexes, all the nonclustered indexes will also be rebuilt but they will use the space that was already created to accommodate the clustered index creation. So, what you are looking at is by design. You could schedule a shrink to occur immediatly after the index rebuild.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
MuadDBA
628 Posts |
Posted - 2003-05-27 : 10:58:19
|
| Chad,Thank you so much for that explanation....it certainly does explain what is happening.The only problem I have with running a shrinkDB immediately afterward is that it will actually fill up all my transaction log space. I have a 5GB mirror that I use for the logs, which would normally be plenty for a 16GB database, but not if it swells to 32GB and I have to shrink it. Besides it filling up the DB, it also takes hours upon hours to shrink that much space. I guess I'll just have to stop reindexing so frequently and start scheduling downtime for the server, if I can get my boss to let me :)Again, thanks chad and tduggan for your continued help! |
|
|
|
|
|
|
|
|
Rebuilding Index with Maint Plan makes DB Huge