Sql Server : Does REBUILDing indexes increase data file size?
- Posted by Sqltimes
- On June 6, 2015
- 0 Comments
Quick one today:
Recently, we kept running into this question from several people. So, adding a dedicated post made sense.
In short, yes. Rebuilding indexes increases database file size. There are some nuances, but in general terms it is true. Both ONLINE or OFFLINE rebuild/reindexing operations increase file size. For more information about reindexing indexes and heaps, please refer to these previous posts.
Sql Server needs extra space to build a new index structure with data from old index. Once completed, my guess is that, it switches to the new structure in new storage location. This operation needs new space.
In ONLINE operation, the table is still available for queries. So, the guess here is that, Sql Server creates a bookmark in the LOG file before rebuilding the index. Then continues rebuilding index, while table is made available for queries. Once completed, it goes to LOG file to add/delete any new records since the bookmark. Once completed, it creates an exclusive lock on the table for a very short duration and redirects new queries to the new index. Then a background process will recollect the space from old index.
1
2
3
4
5
|
-- -- Rebuild all indexs on a table -- ALTER INDEX ALL ON dbo.StoredValue REBUILD GO |
Before Rebuilding the size of data file is:
After Rebuilding the size of data file is:
Couple of points:
MDF file size increases the first time. When I re-run the REBUILD again, the size does not increase. It uses the freed-up space from old index location to perform new rebuilds.
LDF file size increases every time. In some cases, we may not notice the increase during the first REBUILD, as there might be enough existing free space. But when I re-run it multiple times it increases dramatically. But if you take LOG backups between REBUILD’s the LDF size does not increase.
Hope this helps,
_Sqltimes
0 Comments