Does DBCC DBREINDEX cause data files to grow?

Question

Post reply

Does DBCC DBREINDEX cause data files to grow?

Adam Bean

One Orange Chip

Points: 26631
More actions
March 24, 2008 at 8:04 am

#70896

I've seen DBCC DBREINDEX cause log files to grow quite rapidly, but never data files ... I have a database that shot up over 50gb yesterday when the only thing that should have been running would have been my weekly maintenance. I'm curious to know If it was infact my reindex that caused this sudden growth.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

It could cause it. What was the size of the largest table that you reindexed?

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

I think my biggest table in this database is 56gb.

The crazy thing is that sp_spaceused is reporting:

index_sizeunused

-2230440 KB-36888 KB

Negatives!? How is that possible?

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 3

Negatives because things are being calculated and added to other things and probably overflowed.

Because a reindex is transactional, the system has to keep copies of the old and new pages until it can swap things, so it can cause your largest table to grow the database if it's a significant portion of the database size.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

I thought that all was done in the log ... wow, so even after the operation has completed, it won't release the space unless I shrink it?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

Adam Bean (3/24/2008)
I think my biggest table in this database is 56gb.
The crazy thing is that sp_spaceused is reporting:
index_sizeunused
-2230440 KB-36888 KB
Negatives!? How is that possible?

You have to update usage to make sure you get the correct size from sp_spacesued:

-- For whole database

exec sp_spaceused @updateusage = 'true'

-- For one table

exec sp_spaceused @objname = 'TableName', @updateusage = 'true'

This script will give you a better analysis of tables sizes:

Script to analyze table space usage:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

Adam Bean One Orange Chip Points: 26631 More actions · Answer 6

Ah yeah, good call ... so that got me out of the negatives. These numbers still don't add up to the 50gb growth, but I'd assume that rebuilding the entire db's indexes would have been the root cause if in fact DBCC DBREINDEX does in fact cause data size growth.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 7

So when comparing my current database to a backup taken before my maintenance ... the index/table sizes did not increase at all ... it appears that the unused space is all that increased ... meaning I have a bunch of white space in the data file.

Why would DBCC DBREINDEX cause such a thing? Can I expect to see this regularly? This really was the first time this server saw any type of real maintenance. It's been up and running for over 4 years and only has 5 indexes rebuilt on a weekly basis (not my doing, new here), and now I'm doing all indexes on all databases. I'm wondering if this is a one time growth or I need to get more disk space ...

Last time I worked with 2000 using DBCC DBREINDEX, the only growth I saw was on the log files.

Thanks