Table has 14 GB in unused space - How to shrink table size

Question

I use the following script to gather data from my database wich is SQL Server on Azure.

-- Script to run against database to gather metrics

CREATE TABLE #SpaceUsed (name sysname,rows bigint,reserved sysname,data sysname,index_size sysname,unused sysname)

DECLARE @Counter int 
DECLARE @Max int 
DECLARE @Table sysname

SELECT  name, IDENTITY(int,1,1) ROWID 
INTO       #TableCollection 
FROM    sysobjects 
WHERE xtype = 'U' 
ORDER BY lower(name)

SET @Counter = 1 
SET @Max = (SELECT Max(ROWID) FROM #TableCollection)

WHILE (@Counter <= @Max) 
    BEGIN 
        SET @Table = (SELECT name FROM #TableCollection WHERE ROWID = @Counter) 
        INSERT INTO #SpaceUsed 
        EXECUTE sp_spaceused @Table 
        SET @Counter = @Counter + 1 
    END

SELECT * FROM #SpaceUsed

DROP TABLE #TableCollection 
DROP TABLE #SpaceUsed

One of my tables is 53 GB in size, with 38 GB in data, 14 GB in unused. The indexes are 11 MB in size. How do I go about reclaiming that 14 GB that is unused? As this is in Azure the extra space is costing money.

After reading various articles I have tried the following to reduce the size of the table.

DBCC DBREINDEX ('myTableName', ' ')
DBCC SHRINKDATABASE (myDatabaseName, 10);

However, after these complete, the size of the table remains unchanged. How can I go about reducing the size of the table and database in order to reclaim unused space?

UPDATE: There are 24 columns in the table with 9 of the fields being varchar of large length (> 4000) or varchar(MAX). Another 8 columns are unique-identifier types.

Answer 1

Whether a heap or a clustered index, you should find that you will be able to reclaim space in a table by rebuilding:

ALTER INDEX ALL ON dbo.myTableName REBUILD;

Note that reclaiming space in a table, and shrinking a database, are two completely separate things. Shrinking a database should be an exceptional thing - don't shrink a database just to free up disk space, since in most cases, the database will just have to grow and use that space again anyway.

As for determining how the table is distributed, sp_spaceused is pretty useless, since it just gives you a single line item with all data. First, check to see if your table is partitioned. You can see this quickly using:

SELECT COUNT(*)
  FROM sys.partitions
  WHERE partition_number > 1
  AND object_id = OBJECT_ID(N'dbo.myTableName');

Next, see how the data is laid out across your seven indexes. If one of these is a lot bigger than it should be, you could try dropping/re-creating (including potentially removing, say, your XML column from the INCLUDE list), as there may be cases where LOB space for rows you've deleted or set to NULL doesn't get fully recovered, even after a rebuild.

 ;WITH x AS
  ( 
    SELECT i.name, type = pa.allocation_unit_type_desc, kb = COUNT(*) * 8
    FROM sys.indexes AS i
    CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), i.object_id, i.index_id, 
      NULL, 'LIMITED') AS pa
    WHERE i.[object_id] = OBJECT_ID(N'dbo.myTableName')
    GROUP BY GROUPING SETS((), (i.name, pa.allocation_unit_type_desc))
  )
  SELECT name, type, kb, 
    [%] = CONVERT(decimal(5,2), kb*100.0/(SELECT kb FROM x WHERE name IS NULL))
    FROM x
    WHERE name IS NOT NULL
    ORDER BY name, type;

If you want to see individual row sizes in the base table, you could do:

SELECT TOP (100) key, rowsize = DATALENGTH(col1) + DATALENGTH(col2) + ...
  FROM dbo.myTableName
  ORDER BY rowsize DESC;

If you have some that are quite large, you might want to make sure you need that data (otherwise set it to NULL, as it may be what's contributing to your size.

Answer 2

You should check the fill factor on your tables and indexes, to make sure that you aren't explicitly reserving a lot of space on your pages. Run the following command:

SELECT t.name as TableName
      ,COALESCE(i.name,'<no index>') as IndexName
      ,i.type_desc
      ,i.Fill_Factor
  FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id = i.object_id
;

Fill factor tells the SQL engine how much free space to leave on each page it creates as it populates a table or index. After the pages are populated, SQL will add information to the page until it gets too full, then will create a new page, and move an appropriate amount (usually about half) of the data to the new page.

If one of your indexes has a low fill factor (under 75, but over 0 (0 actually means fill to 100%, basically)), it's possible that might explain a large amount of unused space in your database.

If you want to change the fill factor on your indexes to (for example) 90%, you could use a slight variation on the ALTER INDEX command from Aaron Bertrand's answer:

ALTER INDEX ALL ON dbo.myTableName REBUILD WITH FILLFACTOR = 90;