Data File Growth During Re-Index

Hi All
I have a 100GB SQL Server database and when I run a Database Re-Index the
data file (mdf) increases in size from 100GB to 190GB.
Is this normal and if not how do I go about resolving the issue as once the
re-index is finished there is ~90GB of free space in the database.
Thanks
- David T
- David TDavid
1) SQL Server 2005
ALTER INDEX..... there us ONLINE option too see BOL for details
2) SQL Server 2000
Don't run this command on all tables. Identify heavy fragmented tables and
then run DBCC
"David" <David@.discussions.microsoft.com> wrote in message
news:E00AC669-B2C3-4324-B082-4844771F9B93@.microsoft.com...
> Hi All
> I have a 100GB SQL Server database and when I run a Database Re-Index the
> data file (mdf) increases in size from 100GB to 190GB.
> Is this normal and if not how do I go about resolving the issue as once
> the
> re-index is finished there is ~90GB of free space in the database.
> Thanks
> - David T
> - David T|||Also if 2005 check the SORT_IN_TEMPDB option of Alter Index. Sort operations
are performed in tempdb, which does cause tempdb to grow so consider this
also.
--
Adam J Warne, MCDBA
"David" wrote:
> Hi All
> I have a 100GB SQL Server database and when I run a Database Re-Index the
> data file (mdf) increases in size from 100GB to 190GB.
> Is this normal and if not how do I go about resolving the issue as once the
> re-index is finished there is ~90GB of free space in the database.
> Thanks
> - David T
> - David T|||Having lots of free space is good for a db so that when you perform
operations such as these there is enough room to do it's job properly. When
you reindex it creates a new copy of all the indexes before dropping and
renaming the existing ones. So you need room for this to happen. Twice the
space is usually not required although it won't hurt anything. I suspect
your autogrow is set to a high percentage. Most dbs don't need all indexes
rebuilt each time so you may want to consider only rebuilding ones that are
fairly fragmented. See DBCC SHOWCONTIG in BooksOnLine for a sample script to
defrag based on %.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David" <David@.discussions.microsoft.com> wrote in message
news:E00AC669-B2C3-4324-B082-4844771F9B93@.microsoft.com...
> Hi All
> I have a 100GB SQL Server database and when I run a Database Re-Index the
> data file (mdf) increases in size from 100GB to 190GB.
> Is this normal and if not how do I go about resolving the issue as once
> the
> re-index is finished there is ~90GB of free space in the database.
> Thanks
> - David T
> - David T|||It is important to understand what these commands do, in order to understand the ramifications of
running the commands. You don't mention what version of SQL Server you have, so I'll assume 2005 in
the commands I mention below. The same principle holds for 2000, but the commands names are
different.
ALTER INDEX ... REBUILD:
This will create a new index internally, and *after that has been done* the old one is removed. So
if you have one large table in the database, consuming say close to 90 GB and that table has a
clustered index, then what you see is expected.
As suggested:
Don't reindex if you don't need to. Base you reindexing on fragmentation level and whether you
actually see enough performance improvements from the reindex.
Also, REORGANIZE instead of REBUILD does something different and does not require as much working
space in the database.
Check out http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx for more
information (as well as Books Online of course).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"David" <David@.discussions.microsoft.com> wrote in message
news:E00AC669-B2C3-4324-B082-4844771F9B93@.microsoft.com...
> Hi All
> I have a 100GB SQL Server database and when I run a Database Re-Index the
> data file (mdf) increases in size from 100GB to 190GB.
> Is this normal and if not how do I go about resolving the issue as once the
> re-index is finished there is ~90GB of free space in the database.
> Thanks
> - David T
> - David T