As tentativas de recuperar o espaço não utilizado fazem com que o espaço usado aumente significativamente no SQL Server

Eu executaria DBCC UPDATEUSAGE na tabela como primeira etapa, pois os sintomas mostram uso de espaço inconsistente.

DBCC UPDATEUSAGE corrige as linhas, páginas usadas, páginas reservadas, páginas de folha e contagens de páginas de dados para cada partição em uma tabela ou índice. Se não houver imprecisões nas tabelas do sistema, DBCC UPDATEUSAGE não retornará dados. Se forem encontradas imprecisões e corrigidas e WITH NO_INFOMSGS não for usado, DBCC UPDATEUSAGE retornará as linhas e colunas que estão sendo atualizadas nas tabelas do sistema.

Sintaxe é:

DBCC UPDATEUSAGE (N'<database_name>', N'<table_name>');

Depois de executar isso, eu correria EXEC sys.sp_spaceusedcontra a tabela:

EXEC sys.sp_spaceused @objname = N'dbo.MyTable'
    , @updateusage = 'false' --true or false
    , @mode = 'ALL' --ALL, LOCAL_ONLY, REMOTE_ONLY
    , @oneresultset = 1;

O comando acima tem a opção de atualizar o uso, mas como você executou DBCC UPDATEUSAGEmanualmente primeiro, deixe-o definido como false. A execução DBCC UPDATEUSAGEmanual permite que você veja se alguma coisa foi corrigida.

A consulta a seguir deve mostrar a porcentagem de bytes livres na tabela e a porcentagem de páginas livres na tabela. Como a consulta usa um recurso não documentado, não é aconselhável contar com os resultados, mas parece preciso quando comparado com a saída de sys.sp_spaceused, em alto nível.

Se a porcentagem de bytes livres for significativamente maior que a porcentagem de páginas livres, você terá muitas páginas parcialmente vazias.

Páginas parcialmente vazias podem ter várias causas, incluindo:

1. Divisões de página, em que a página deve ser dividida para acomodar novas inserções no índice clusterizado

2. Incapacidade de preencher a página com colunas devido ao tamanho da coluna.

A consulta usa a sys.dm_db_database_page_allocationsfunção de gerenciamento dinâmico não documentada:

;WITH dpa AS 
(
    SELECT dpa.*
        , page_free_space_percent_corrected = 
          CASE COALESCE(dpa.page_type_desc, N'')
            WHEN N'TEXT_MIX_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100)
            WHEN N'TEXT_TREE_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100)
            ELSE COALESCE(dpa.page_free_space_percent, 100)
          END
    FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.MyTable'), NULL, NULL, 'DETAILED') dpa
)
, src AS
(
SELECT TotalKB = COUNT_BIG(1) * 8192 / 1024
    , FreeKB = SUM((dpa.page_free_space_percent_corrected / 100) * CONVERT(bigint, 8192)) / 1024
    , TotalPages = COUNT_BIG(1)
    , TotalEmptyPages = SUM(CASE WHEN dpa.page_free_space_percent_corrected = 100 THEN 1 ELSE 0 END) --completely empty pages
FROM dpa
)
SELECT *
    , BytesFreePercent = (CONVERT(decimal(38,2), src.FreeKB) / src.TotalKB) * 100
    , UnusedPagesPercent = (CONVERT(decimal(38,2), src.TotalEmptyPages) / src.TotalPages) * 100
FROM src

A saída se parece com:

╔═════════╦════════╦════════════╦═════════════════ ╦══════════════════╦════════════════════╗
║ TotalKB ║ FreeKB ║ TotalPages ║ TotalEmptyPages ║ BytesFreePercent ║ UnusedPagesPercent ║
╠═════════╬════════╬════════════╬═════════════════ ╬══════════════════╬════════════════════╣
║ 208 ║ 96 ║ 26 ║ 12 ║ 46.153800 ║ 46.153800 ║
╚═════════╩════════╩════════════╩═════════════════ ╩══════════════════╩════════════════════╝

Eu escrevi um post no blog descrevendo a função aqui .

Em seu cenário, desde que você executou ALTER TABLE ... REBUILD, você deve ver um número muito baixo para TotalEmptyPages, mas acho que ainda terá cerca de 72% em BytesFreePercent.

Eu usei seu CREATE TABLEscript para tentar recriar seu cenário.

Este é o MCVE que estou usando:

DROP TABLE IF EXISTS dbo.MyTable;

CREATE TABLE [dbo].[MyTable](
    [Column1]  [int]            NOT NULL IDENTITY(1,1),
    [Column2]  [int]            NOT NULL,
    [Column3]  [int]            NOT NULL,
    [Column4]  [bit]            NOT NULL,
    [Column5]  [tinyint]        NOT NULL,
    [Column6]  [datetime]       NULL,
    [Column7]  [int]            NOT NULL,
    [Column8]  [varchar](100)   NULL,
    [Column9]  [varchar](256)   NULL,
    [Column10] [int]            NULL,
    [Column11] [image]          NULL,
    [Column12] [text]           NULL,
    [Column13] [varchar](100)   NULL,
    [Column14] [varchar](6)     NULL,
    [Column15] [int]            NOT NULL,
    [Column16] [bit]            NOT NULL,
    [Column17] [datetime]       NULL,
    [Column18] [varchar](50)    NULL,
    [Column19] [varchar](50)    NULL,
    [Column20] [varchar](60)    NULL,
    [Column21] [varchar](20)    NULL,
    [Column22] [varchar](120)   NULL,
    [Column23] [varchar](4)     NULL,
    [Column24] [varchar](75)    NULL,
    [Column25] [char](1)        NULL,
    [Column26] [varchar](50)    NULL,
    [Column27] [varchar](128)   NULL,
    [Column28] [varchar](50)    NULL,
    [Column29] [int]            NULL,
    [Column30] [text]           NULL,
 CONSTRAINT [PK] PRIMARY KEY CLUSTERED 
(
    [Column1] ASC,
    [Column2] ASC,
    [Column3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column4]  DEFAULT (0) FOR [Column4]

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column5]  DEFAULT (0) FOR [Column5]

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column15]  DEFAULT (0) FOR [Column15]

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column16]  DEFAULT (0) FOR [Column16]
GO

INSERT INTO dbo.MyTable (
      Column2
    , Column3
    , Column4
    , Column5
    , Column6
    , Column7
    , Column8
    , Column9
    , Column10
    , Column11
    , Column12
    , Column13
    , Column14
    , Column15
    , Column16
    , Column17
    , Column18
    , Column19
    , Column20
    , Column21
    , Column22
    , Column23
    , Column24
    , Column25
    , Column26
    , Column27
    , Column28
    , Column29
    , Column30
)
VALUES (
          0
        , 0
        , 0
        , 0
        , '2019-07-09 00:00:00'
        , 1
        , REPLICATE('A', 50)    
        , REPLICATE('B', 128)   
        , 0
        , REPLICATE(CONVERT(varchar(max), 'a'), 1)
        , REPLICATE(CONVERT(varchar(max), 'b'), 9000)
        , REPLICATE('C', 50)    
        , REPLICATE('D', 3)     
        , 0
        , 0
        , '2019-07-10 00:00:00'
        , REPLICATE('E', 25)    
        , REPLICATE('F', 25)    
        , REPLICATE('G', 30)    
        , REPLICATE('H', 10)    
        , REPLICATE('I', 120)   
        , REPLICATE('J', 4)     
        , REPLICATE('K', 75)    
        , 'L'       
        , REPLICATE('M', 50)    
        , REPLICATE('N', 128)   
        , REPLICATE('O', 50)    
        , 0
        , REPLICATE(CONVERT(varchar(max), 'c'), 90000)
);
--GO 100

;WITH dpa AS 
(
    SELECT dpa.*
        , page_free_space_percent_corrected = 
          CASE COALESCE(dpa.page_type_desc, N'')
            WHEN N'TEXT_MIX_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100)
            WHEN N'TEXT_TREE_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100)
            ELSE COALESCE(dpa.page_free_space_percent, 100)
          END
    FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.MyTable'), NULL, NULL, 'DETAILED') dpa
)
, src AS
(
SELECT TotalKB = COUNT_BIG(1) * 8192 / 1024
    , FreeKB = SUM((dpa.page_free_space_percent_corrected / 100) * CONVERT(bigint, 8192)) / 1024
    , TotalPages = COUNT_BIG(1)
    , TotalEmptyPages = SUM(CASE WHEN dpa.page_free_space_percent_corrected = 100 THEN 1 ELSE 0 END) --completely empty pages
FROM dpa
)
SELECT *
    , BytesFreePercent = (CONVERT(decimal(38,2), src.FreeKB) / src.TotalKB) * 100
    , UnusedPagesPercent = (CONVERT(decimal(38,2), src.TotalEmptyPages) / src.TotalPages) * 100
FROM src

A consulta a seguir mostra uma única linha para cada página alocada à tabela e usa esse mesmo DMV não documentado:

SELECT DatabaseName = d.name
    , ObjectName = o.name
    , IndexName = i.name
    , PartitionID = dpa.partition_id
    , dpa.allocation_unit_type_desc
    , dpa.allocated_page_file_id
    , dpa.allocated_page_page_id
    , dpa.is_allocated
    , dpa.page_free_space_percent --this seems unreliable
    , page_free_space_percent_corrected = 
        CASE COALESCE(dpa.page_type_desc, N'')
        WHEN N'TEXT_MIX_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100)
        WHEN N'TEXT_TREE_PAGE' THEN 100 - COALESCE(dpa.page_free_space_percent, 100)
        ELSE COALESCE(dpa.page_free_space_percent, 100)
        END
    , dpa.page_type_desc
    , dpa.is_page_compressed
    , dpa.has_ghost_records
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.MyTable'), NULL, NULL, 'DETAILED') dpa
    LEFT JOIN sys.databases d ON dpa.database_id = d.database_id
    LEFT JOIN sys.objects o ON dpa.object_id = o.object_id
    LEFT JOIN sys.indexes i ON dpa.object_id = i.object_id AND dpa.index_id = i.index_id
WHERE dpa.database_id = DB_ID() --sanity check for sys.objects and sys.indexes

A saída mostrará muitas linhas se você executá-la em sua tabela real em seu ambiente de teste, mas pode permitir que você veja onde está o problema.

Você pode executar o script a seguir e postar os resultados em sua pergunta? Só estou tentando ter certeza de que estamos na mesma página.

SELECT ObjectName = s.name + N'.' + o.name
    , ReservedPageCount = SUM(dps.reserved_page_count)
    , UsePageCount = SUM(dps.used_page_count)
FROM sys.schemas s
    INNER JOIN sys.objects o ON s.schema_id = o.schema_id
    INNER JOIN sys.partitions p ON o.object_id = p.object_id
    INNER JOIN sys.dm_db_partition_stats dps ON p.object_id = dps.object_id
WHERE s.name = N'dbo'
    AND o.name = N'MyTable'
GROUP BY s.name + N'.' + o.name;

Uma das colunas é um LOB do tipo imagem e está armazenando arquivos que variam em tamanho de alguns KB a várias centenas de MB

Você pode estar experimentando fragmentação interna.
Qual é a fragmentação de página para esta tabela?
E a fragmentação das páginas em linha é diferente das páginas fora da linha?

Você diz que tem arquivos com poucos KB.
O SQL Server armazena tudo em páginas de 8060 bytes. Ou seja, se você tiver uma linha (ou dados fora da linha) com 4040 bytes e a próxima for semelhante, ela não poderá caber ambas na mesma página e você desperdiçará metade do seu espaço. Tente alterar o tamanho da linha armazenando colunas de comprimento variável (começar com a imagem, por exemplo) em uma tabela diferente.

O banco de dados está no modo de recuperação total? Nesse caso, quando você realiza uma redução, ela registra todas as alterações e não a reduz da maneira esperada. Dependendo do horário de funcionamento, você pode fazer um backup, alternar para o modo de recuperação de envio em massa e, em seguida, executar a redução no arquivo de dados. Depois disso, você deseja executar scripts de índice para reparar/reconstruir e voltar para a recuperação completa. Isso é o que eu tentaria de qualquer maneira, mas, novamente, depende do seu horário de funcionamento para tudo isso.

A única vez que não consegui reduzir um banco de dados e recuperar espaço é porque você não pode reduzir um banco de dados além do tamanho inicial do banco de dados quando ele foi criado. Assim, por exemplo, se seu banco de dados é uma cópia do banco de dados de produção e você criou o banco de dados em 525 GB, o sql server não permitirá que você reduza o tamanho abaixo de 525 GB, não importa quantos dados você exclua do banco de dados. Mas se o banco de dados foi criado abaixo de 383 GB e depois cresceu para 525 GB, você não deve ter problemas para recuperar o espaço. Há muito que penso que esta é uma restrição estúpida e arbitrária da Microsoft.

Reduza o banco de dados apenas até seu tamanho inicial, que é definido após a criação do banco de dados

Eu encontrei esse problema antes em caixas de produção, o que você precisa fazer é reconstruir tabelas e índices para cada tabela (nessa ordem).

Aqui está a consulta que eu uso para manter as tabelas sob controle. Ele o ajudará a determinar quais tabelas precisam ser reconstruídas e a criar as consultas SQL que você precisa executar. Essa consulta é limitada àquelas com mais de 1 MB de espaço não utilizado e uma proporção de 5% não utilizada, para que você reconstrua apenas o que realmente precisa se concentrar:

SELECT  'alter table [' + t.NAME + '] rebuild;' AS SQL1, 'alter index all on [' + t.NAME + '] rebuild;' as SQL2, t.NAME AS TableName, p.rows AS RowCounts, SUM(a.total_pages) * 8/1024 AS TotalSpaceMB,  SUM(a.used_pages) * 8/1024 AS UsedSpaceMB,  (SUM(a.total_pages) - SUM(a.used_pages)) * 8/1024 AS UnusedSpaceMB, case when SUM(a.total_pages)=0 then 0 else (SUM(a.total_pages) - SUM(a.used_pages))*100/SUM(a.total_pages) end as Ratio  FROM     sys.tables t (nolock) INNER JOIN       sys.indexes i (nolock)  ON t.OBJECT_ID = i.object_id INNER JOIN  sys.partitions p (nolock) ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN  sys.allocation_units a (nolock) ON p.partition_id = a.container_id LEFT OUTER JOIN  sys.schemas s (nolock) ON t.schema_id = s.schema_id WHERE  t.is_ms_shipped = 0 AND i.OBJECT_ID > 255  GROUP BY  t.Name, s.Name, p.Rows  
having  (SUM(a.total_pages) - SUM(a.used_pages)) * 8/1024>1
and (SUM(a.total_pages) - SUM(a.used_pages))*100/SUM(a.total_pages)>5
ORDER BY    5 desc