After Index Optimize the database size increased from 114 to 140Gb, is this normal?
Server RAM usage do not reduced after the process finishing, and stay at 62Gb/98%
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Yes that is normal.
When a new index structure is created, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. The old structure is not deallocated until the index creation transaction commits.
The following index DDL operations create new index structures and require additional disk space:
- ALTER INDEX REBUILD
So if one of your indexes was rebuilt by the script, the size needed for that operation was the size of the old index structure + the size of the new index structure for the time the operation was running.
Once the new structure is in place the old structure is removed and free space remains inside the data file, but it is not returned to the operating system so your data file ends up larger but should include some free space that will be reused in the future.
Also take a look at Index Disk Space Example
Total disk space required to support both the source and target structures for the duration of the index operation is 816 MB (363 + 453). The space currently allocated to the source structures will be deallocated after the index operation is committed.