SQL Index space and unused table space

Question

I've got a table in a SQL database that has a large unused space that corresponds to the amount of space allocated to indexes

I was working on this by running a reindex on the specific table, but I'm running into space issues and I'm not sure if my approach is effective.

Answer 1

When you rebuilds an index, SQL Server creates a new version of the index elsewhere in the database, so you need the space to fit that new version of the index, even if you will end with more than free space than before.

The alternative would be to export the data with BCP, truncate the table and reload.

Answer 2

Am I correct that the unused space is most likely from the index?

I'm thinking to create a temporary table to load the data, truncate the existing table and then copy the data back. It seems like I will need about 40GB to accomplish that. Does that sound right?

Answer 3

Hi @Hollisorama ,

>I was working on this by running a reindex on the specific table, but I'm running into space issues and I'm not sure if my approach is effective.

Usually rebuilding the clustered index should be an effective approach. But rebuilding the index requires a certain amount of space.
Disk space is an important consideration when you create, rebuild, or drop indexes. Inadequate disk space can degrade performance or even cause the index operation to fail. More details please refer to following documents.
https://learn.microsoft.com/en-us/sql/relational-databases/indexes/disk-space-requirements-for-index-ddl-operations?view=sql-server-ver15
https://learn.microsoft.com/en-us/sql/relational-databases/indexes/index-disk-space-example?view=sql-server-ver15

If there are data types such as text or image in your table, index rebuilding will not affect them in some cases. You may need to consider rebuilding objects.
https://dba.stackexchange.com/questions/52317/freeing-unused-space-sql-server-table

Answer 4

"Unused space" often occurs on "heap tables" = a table without a clustered index; is it a heap table? In that case you have to rebuild the table as it to get rid of the unused space; which is similar to rebuild an index = it requires additional space.

See also Heaps (Tables without Clustered Indexes)

Answer 5

Here's the indexes on one of the tables

I'm thinking to copy the data to a new table, truncate the existing table, reduce the database file size and then reimport the data. This is a vendor's application so I may reengage them. I had already opened a ticket with them and they said the table usage was normal. But it doesn't seem normal to have a large index size and corresponding free space in the database. Can you confirm that is unusual?