Eu tenho uma tabela em um banco de dados de produção que tem um tamanho de 525 GB, dos quais 383 GB não são utilizados:
Gostaria de recuperar um pouco desse espaço, mas, antes de mexer no banco de dados de produção, estou testando algumas estratégias em uma tabela idêntica em um banco de dados de teste com menos dados. Esta tabela tem um problema semelhante:
Algumas informações sobre a mesa:
- O fator de preenchimento é definido como 0
- Existem cerca de 30 colunas
- 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
- A tabela não possui nenhum índice hipotético associado a ela
O servidor está executando o SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64). O banco de dados está usando o SIMPLEmodelo de recuperação.
Algumas coisas que tentei:
- Reconstruindo os índices:
ALTER INDEX ALL ON dbo.MyTable REBUILD. Isso teve um impacto insignificante. - Reorganizando os índices:
ALTER INDEX ALL ON dbo.MyTable REORGANIZE WITH(LOB_COMPACTION = ON). Isso teve um impacto insignificante. - Copiou a coluna LOB para outra tabela, eliminou a coluna, recriou a coluna e copiou os dados de volta (conforme descrito neste post: Liberando Espaço Não Usado Tabela SQL Server ). Isso diminuiu o espaço não utilizado, mas parecia apenas convertê-lo em espaço usado:
- Usou o utilitário bcp para exportar a tabela, truncá-la e recarregá-la (conforme descrito neste post: Como liberar o espaço não utilizado para uma tabela ). Isso também reduziu o espaço não utilizado e aumentou o espaço usado de forma semelhante à imagem acima.
- Mesmo não sendo recomendado, tentei os comandos DBCC SHRINKFILE e DBCC SHRINKDATABASE, mas eles não tiveram nenhum impacto no espaço não utilizado.
- Correr
DBCC CLEANTABLE('myDB', 'dbo.myTable')não fez diferença - Eu tentei todos os itens acima, mantendo os tipos de dados de imagem e texto e depois de alterar os tipos de dados para varbinary(max) e varchar(max).
- Tentei importar os dados para uma nova tabela em um banco de dados novo, e isso também converteu apenas o espaço não utilizado em espaço usado. Eu descrevi os detalhes desta tentativa neste post .
Não quero fazer essas tentativas no banco de dados de produção se esses forem os resultados que posso esperar, então:
- Por que o espaço não utilizado está sendo convertido em espaço usado após algumas dessas tentativas? Eu sinto que não tenho uma boa compreensão do que está acontecendo sob o capô.
- Existe mais alguma coisa que eu possa fazer para diminuir o espaço não utilizado sem aumentar o espaço usado?
EDIT: Aqui está o relatório de uso do disco e o script para a tabela:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable](
[Column1] [int] NOT NULL,
[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]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column4] DEFAULT (0) FOR [Column4]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column5] DEFAULT (0) FOR [Column5]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column15] DEFAULT (0) FOR [Column15]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column16] DEFAULT (0) FOR [Column16]
GO
Aqui estão os resultados da execução dos comandos na resposta de Hannah Vernon:
╔════════════╦═══════════╦════════════╦═════════════════╦══════════════════════╦════════════════════╗
║ TotalBytes ║ FreeBytes ║ TotalPages ║ TotalEmptyPages ║ PageBytesFreePercent ║ UnusedPagesPercent ║
╠════════════╬═══════════╬════════════╬═════════════════╬══════════════════════╬════════════════════╣
║ 9014280192║ 8653594624║ 1100376║ 997178 ║ 95.998700 ║ 90.621500 ║
╚════════════╩═══════════╩════════════╩═════════════════╩══════════════════════╩════════════════════╝
╔═════════════╦═══════════════════╦════════════════════╗
║ ObjectName ║ ReservedPageCount ║ UsedPageCount ║
╠═════════════╬═══════════════════╬════════════════════╣
║ dbo.MyTable ║ 5109090 ║ 2850245 ║
╚═════════════╩═══════════════════╩════════════════════╝
ATUALIZAR:
Eu executei o seguinte, conforme sugerido por Hannah Vernon:
DBCC UPDATEUSAGE (N'<database_name>', N'<table_name>');
E aqui estava a saída:
DBCC UPDATEUSAGE: Usage counts updated for table 'MyTable' (index 'PK_MyTable', partition 1):
USED pages (LOB Data): changed from (568025) to (1019641) pages.
RSVD pages (LOB Data): changed from (1019761) to (1019763) pages.
Isso atualizou o uso do disco para a tabela:
E o uso geral do disco:
Portanto, parece que o problema foi que o uso do disco rastreado pelo SQL Server ficou totalmente fora de sincronia com o uso real do disco. Vou considerar esse problema resolvido, mas gostaria de saber por que isso aconteceu em primeiro lugar!