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

azure-sql-databasedatabase-sizesql server

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.

Best Answer

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.

Related Question