The issue I'm trying to solve is index rebuild that keeps consuming the entire disc space (and then fails the operation).
The command in use is (while iterating over all tables in the DB):
ALTER INDEX ALL ON MyTable REBUILD
- SQL Server 2014
- Total disk space is 465 GB
- The database size is ~320GB
During index rebuild the disc is filled entirely. And when the operation fails, the space is not deallocated. That means no free space is left on the disk - at all.
Any suggestions regarding what can be done differently?
Thank you.
CodePudding user response：
Any suggestions regarding what can be done differently?
Don't rebuild all your indexes. Why are you even doing that? Try just updating statistics instead.
Rebuild the indexes one-by one.
If a single index is too large to rebuild, consider disabling it first, which is like dropping the index but retaining the index definition. Warning, though, disabling the primary key index (or a unique index referenced by a foreign key) or a clustered index has side effects. Foreign keys on other tables will be disabled (NOCKECK), and if the clustered index is disabled all other indexes on the table will be disabled too.
And when the operation fails, the space is not deallocated.
When a database is set to auto-grow, it grows. There is no "auto shrink" setting for a database. To reclaim space allocated to a database after a on-time operation you can shrink it.