September 15, 2015 at 11:13 am
Dear All,
I'm trying to determine how much space I would need for my data drive and log file drive to do index rebuild. I have a database which is 100gb, it is in simple recovery mode. Would you be able to let me know what to have a look at to determine how much space I would need please?
Thank you in advance!
September 16, 2015 at 2:41 pm
index rebuilds require as much space as your largest table. During rebuild you essentially have two copies of the index until the rebuild is complete, at which time to original copy is removed, leaving free space in datafile.
here is a quick example
create database indexsize
USE indexsize;
--check size
exec sp_spaceused;
/********************************************
my results
database_namedatabase_sizeunallocated space
indexsize3.98 MB0.88 MB
********************************************/
DBCC SQLPERF(logspace);
/**********
my results
0.7890625MB
**********/
--create and populate table
CREATE TABLE [sometable]([id] [int] IDENTITY(1, 1)
PRIMARY KEY ,
[data] varchar(4000));
INSERT INTO [sometable]([data])
SELECT REPLICATE('a', 4000);
GO 1000
--check size
exec sp_spaceused;
/************************
my results
indexsize7.98 MB0.94 MB
************************/
DBCC SQLPERF(logspace);
/**********
my results
0.7890625MB
**********/
--rebuild index
ALTER INDEX ALL ON [sometable] REBUILD;
--checksize
exec sp_spaceused;
/*************************
my results
indexsize12.50 MB4.93 MB
*************************/
DBCC SQLPERF(logspace);
/**********
1.304688MB
**********/
DROP DATABASE [indexsize];
as you can see, creating and populating the table added approx 4MB to the database size(7.98MB). after the index rebuild the database grew to 12.5MB , which is the 4MB that the datafile grew along with the .5MB that the log file grew.
September 17, 2015 at 4:23 am
Thank you for your reply!
I have run the SQL server "Index physical stats" report which gives all the indexes which would need to be removed and also the "Disk usage by top tables report" which gives all the information regarding the data size. I have now listed all the indexes which would need to be rebuilt with their associated Data size.
If the data size is 114,800kb does it mean that my data drive would have to have nearly the double amount of the data size? and also the transactional log file will grow around half a gb?
September 17, 2015 at 5:40 am
I expect this is obvious, but you can choose to only rebuild indexes that are fragmented. You can also Reorganise, instead of Rebuild, which uses less disk space. I read conflicting things about whether Reindex is necessary (at some point) but we only ever Reorg our large indexes (so we can do it ONLINE as we don't [unless licensing has changed] have a SQL Version that allows online index Rebuild).
We do rebuild indexes during scheduled downtime, and then re-size the Log files if they grew excessively.
You should only need "extra space" equivalent to your largest index - although it might be that subsequent index rebuilds don't reuse the spare space available within the file?
I wonder: whether rebuilding largest-index-first makes best use of space and minimises extension of the file?
September 17, 2015 at 6:28 am
So, if my largest data in around 40gb then would I need only need 80gb extra free space?
Sorry to be a pain!
September 17, 2015 at 7:19 am
Note sure what your latent data consists of, but my expectation would be:
A database of size 40GB with largest index 10GB would need a maximum of 50GB for the index rebuild.
However, I'm not sure whether the next-largest-index will reuse the, now released, free space within the file, or whether it will also extend the database (to place the index pages contiguously within the file). There will come a point where a smaller index will fit into the space, now released.
My much bigger problem is that we use Full Recovery Model on all databases (OLTP) so I have to stop them growing massively during index rebuilds ...
September 17, 2015 at 7:55 am
A database of size 40GB with largest index 10GB would need a maximum of 50GB for the index rebuild.
However, I'm not sure whether the next-largest-index will reuse the, now released, free space within the file, or whether it will also extend the database (to place the index pages contiguously within the file). There will come a point where a smaller index will fit into the space, now released.
This is correct, you shouldn't need much more space than your largest index
My much bigger problem is that we use Full Recovery Model on all databases (OLTP) so I have to stop them growing massively during index rebuilds ...
If you are referring to your logs growing during index maintenance, that is probably due to reorganization and not rebuild. A rebuild create a new index and reorganization changed the order of leaf level pages. Depending on the size of table and the amount of fragmentation, there could be alot of log records created.
September 17, 2015 at 8:44 am
Robert klimes (9/17/2015)
If you are referring to your logs growing during index maintenance, that is probably due to reorganization and not rebuild. A rebuild create a new index and reorganization changed the order of leaf level pages. Depending on the size of table and the amount of fragmentation, there could be alot of log records created.
Interesting, thanks. I think I'll restore a fragmented database and try (separately) a comparison between Reorg and Rebuild.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply