ApsaraDB RDS:Problemas de espaço insuficiente no RDS for SQL Server

O banco de dados tempdb é um banco de dados de sistema do SQL Server que armazena dados temporários. O espaço de arquivo de dados no tempdb é frequentemente utilizado em diversos cenários, tais como:

• Objetos de usuário: Tabelas temporárias criadas por usuários.

• Objetos internos: Tabelas temporárias geradas internamente pelo SQL Server.

• Armazenamento de versões: Quando o isolamento de snapshot ou o snapshot de leitura confirmada está habilitado para um banco de dados, as informações de versionamento são armazenadas no tempdb.

Se determinadas operações, como transações longas, criação de muitas tabelas temporárias ou isolamento de snapshot, utilizarem uma grande quantidade de espaço, o arquivo de dados pode ficar inchado. Isso significa que o tamanho do arquivo cresce significativamente além de seu intervalo normal. Para mais informações, consulte o tutorial oficial da Microsoft sobre o banco de dados tempdb.

Arquivar ou excluir dados

Exclua ou migre dados acessados com pouca frequência — por exemplo, registros históricos antigos — para outra instância ou formato de arquivamento. Isso reduz diretamente o espaço de dados utilizado e é a solução de longo prazo mais eficaz, embora exija alterações na lógica da aplicação e no design do banco de dados.

Comprimir dados

Instâncias executando SQL Server 2016 ou posterior, e instâncias Enterprise Edition executando versões anteriores, suportam compactação de linha e página no nível de tabela, índice ou partição. Para detalhes, consulte Compactação de dados.

A economia varia amplamente (10–90%) dependendo do esquema, tipos de colunas e distribuição de dados. Use sp_estimate_data_compression_savings para estimar a economia antes de ativar a compactação.

Alterar configurações de compactação é uma operação de Linguagem de Definição de Dados (DDL). Em tabelas grandes, isso pode causar bloqueios de tabela prolongados. Execute fora do horário de pico.

Em instâncias Enterprise Edition, defina ONLINE = ON para minimizar o impacto nos negócios.

A compactação aumenta a sobrecarga da CPU. Ative-a apenas em tabelas grandes onde a economia de espaço justifique o custo.

Desfragmentar índices

A alta fragmentação de índices torna as consultas lentas e desperdiça armazenamento. Para visualizar as taxas de fragmentação, acesse Autonomy Services > Performance Optimization e clique em Index Usage. O console mostra a taxa de fragmentação por tabela e sugere se deve reconstruir ou reorganizar cada índice.

A taxa de fragmentação de índice mede a porcentagem de páginas de índice logicamente adjacentes que não são fisicamente adjacentes. Isso difere da porcentagem de espaço livre dentro das páginas. Para medir o espaço livre médio por página, consulte sys.dm_db_index_physical_stats no modo SAMPLED ou DETAILED e verifique a coluna avg_page_space_used_in_percent . Para detalhes, consulte sys.dm_db_index_physical_stats (Transact-SQL) . Essa consulta lê muitas páginas de índice — execute-a fora do horário de pico.

Reconstrução de índice (REBUILD): Recria completamente o índice. Mais eficaz do que reorganizar para índices com alta fragmentação. Por padrão, bloqueia a tabela durante a operação. Em instâncias Enterprise Edition, defina ONLINE = ON para evitar bloqueios longos de tabela.

ALTER INDEX <IX_YourIndexName> ON <YourTableName> REBUILD WITH (ONLINE = ON);

Após a reconstrução, o console atualiza as estatísticas do índice de forma assíncrona. Clique em Recollect para obter os dados mais recentes e, em seguida, clique em Export Script para baixar e verificar a taxa de fragmentação.

Reorganização de índice (REORGANIZE): Reestrutura as páginas de nível folha sem bloquear a tabela. Menos eficaz que a reconstrução, mas apropriada para índices com baixa fragmentação.

-- This script applies only to SQL Server 2012 and later versions. Specify the following parameters before running.
DECLARE @dbName NVARCHAR(128) = 'YourDBName'  -- Database name
DECLARE @fileName NVARCHAR(128)               -- Data file name
DECLARE @targetSize INT = 2000                -- Target size (MB)
DECLARE @shrinkSize INT = 5120                -- Size to shrink per iteration (MB). 5 GB recommended.
DECLARE @currentSize INT                      -- Current file size
DECLARE @freeSize INT                         -- Unallocated space
DECLARE @usedSize INT                         -- Used space

DECLARE @sql NVARCHAR(500)
DECLARE @waitTime INT = 10                    -- Wait between iterations (seconds)

-- Get the data file name
SELECT @fileName = name
FROM sys.master_files
WHERE database_id = DB_ID(@dbName)
AND type_desc = 'ROWS'

-- Shrink in a loop
WHILE 1 = 1
BEGIN
    -- Get current size and space breakdown
    DECLARE @sql0 NVARCHAR(MAX) = N'
    USE [' + @dbName + '];
    SELECT
      @currentSize = (SUM(total_page_count) * 1.0 / 128),
      @freesize = (SUM(unallocated_extent_page_count) * 1.0 / 128)
    FROM sys.dm_db_file_space_usage
    WHERE database_id = DB_ID();'

    EXEC sp_executesql
      @sql0,
      N'@currentSize INT OUTPUT, @freesize INT OUTPUT',
      @currentSize OUTPUT,
      @freesize OUTPUT

    PRINT 'Current size:' + CAST(@currentSize AS VARCHAR(10)) + 'MB'
    PRINT 'Free size:' + CAST(@freeSize AS VARCHAR(10)) + 'MB'

    SET @usedSize = @currentSize - @freeSize
    PRINT 'Used size:' + CAST(@usedSize AS VARCHAR(10)) + 'MB'

    -- Validate target size
    IF @targetSize <= @usedSize
    BEGIN
        PRINT 'The target size is too small. Specify a new size. The target size cannot be smaller than: ' + CAST(@usedSize AS VARCHAR(20)) + 'MB'
        BREAK
    END

    -- Exit when target is reached
    IF @currentSize <= @targetSize
    BEGIN
        PRINT 'Shrink completed. Current size: ' + CAST(@currentSize AS VARCHAR(20)) + 'MB'
        BREAK
    END

    -- Calculate the new size for this iteration
    DECLARE @newSize INT = @currentSize - @shrinkSize
    IF @newSize < @targetSize
        SET @newSize = @targetSize

    -- Run the shrink
    SET @sql = 'USE [' + @dbName + '];DBCC SHRINKFILE (N''' + @fileName + ''', ' + CAST(@newSize AS VARCHAR(20)) + ')'
    PRINT 'Executing shrink: ' + @sql
    EXEC (@sql)

    -- Wait before the next iteration
    PRINT 'Waiting ' + CAST(@waitTime AS VARCHAR(10)) + ' seconds to continue...'
    WAITFOR DELAY '00:00:10'
END

DBCC SHRINKFILE(<File ID>, <Target size in MB>)

Solucionar problemas comuns de espaço de dados

SELECT name, log_reuse_wait, log_reuse_wait_desc FROM sys.databases;

Cenário 1: tempdb está grande atualmente (instância ativa)

Passo 1 — Verificar espaço por tipo de objeto

Execute a seguinte consulta para ver quanto espaço cada categoria (objetos de usuário, objetos internos, armazenamento de versões) está consumindo:

SELECT
    SUM(version_store_reserved_page_count) AS [version store pages used],
    (SUM(version_store_reserved_page_count) * 1.0 / 128) AS [version store space (MB)],
    SUM(user_object_reserved_page_count) AS [user object pages used],
    (SUM(user_object_reserved_page_count) * 1.0 / 128) AS [user object space (MB)],
    SUM(internal_object_reserved_page_count) AS [internal object pages used],
    (SUM(internal_object_reserved_page_count) * 1.0 / 128) AS [internal object space (MB)]
FROM
    sys.dm_db_file_space_usage;

Para referência, consulte sys.dm_db_file_space_usage.

Passo 2a — Se objetos de usuário ou objetos internos estiverem grandes

Identifique quais sessões estão usando mais espaço do tempdb:

SELECT
    session_id,
    SUM(user_objects_alloc_page_count) AS [user object pages used],
    (SUM(user_objects_alloc_page_count) * 1.0 / 128) AS [user object space (MB)],
    SUM(internal_objects_alloc_page_count) AS [internal object pages used],
    (SUM(internal_objects_alloc_page_count) * 1.0 / 128) AS [internal object space (MB)]
FROM
    sys.dm_db_session_space_usage
GROUP BY
    session_id;

Para referência, consulte sys.dm_db_session_space_usage.

Em seguida, use o session_id para encontrar a instrução SQL que a sessão está executando:

SELECT
    r.session_id AS [SPID],
    r.start_time AS [Start Time],
    r.status AS [Status],
    r.command AS [Command Type],
    r.wait_type AS [Wait Type],
    r.wait_time AS [Wait Time (ms)],
    r.last_wait_type AS [Last Wait Type],
    t.text AS [Executing Statement]
FROM
    sys.dm_exec_requests r
CROSS APPLY
    sys.dm_exec_sql_text(r.sql_handle) t
WHERE
    r.session_id = <session_id>; -- Replace with the session_id from the previous query

Passo 2b — Se o armazenamento de versões estiver grande

O banco de dados provavelmente tem o isolamento de snapshot habilitado com transações longas impedindo a limpeza.

Verifique quais bancos de dados têm o isolamento de snapshot habilitado:

SELECT
    name,
    is_read_committed_snapshot_on,
    snapshot_isolation_state
FROM sys.databases;

Se is_read_committed_snapshot_on ou snapshot_isolation_state for 1, o isolamento de snapshot está ativo.

Encontre sessões com transações longas não confirmadas que estão bloqueando a limpeza do armazenamento de versões:

SELECT * FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC;

Use o session_id para verificar o que a sessão está fazendo:

SELECT * FROM sys.sysprocesses WHERE spid = <session_id>;

Se o status da sessão for sleeping, verifique a última instrução executada:

DBCC INPUTBUFFER(<session_id>);

Cenário 2: Investigar crescimento passado do tempdb após uma reinicialização

Se a instância já foi reiniciada e os dados em tempo real não estão disponíveis, use Sessões Ativas Médias (AAS), logs de consultas lentas e logs de auditoria para reconstruir o que aconteceu.

Passo 1 — Encontrar a janela de crescimento

Na página de detalhes da instância, acesse Monitoring and Alerts > Standard Monitoring. Na seção Instance Storage, anote quando a métrica tmp_size começou e parou de subir. Lembre-se de que o tempdb pode reutilizar o espaço livre existente primeiro — a expansão real do arquivo acontece apenas depois que o espaço existente se esgota, então o crescimento no gráfico pode ocorrer após o início da operação SQL.

Passo 2 — Analisar usando AAS

Acesse Autonomy Services > Performance Optimization > Performance Insight. Selecione a janela de tempo, estendendo o horário de início para capturar todas as operações relacionadas. Procure por instruções SQL que usam intensivamente tabelas temporárias baseadas em disco (por exemplo, criação frequente de tabelas #).

Passo 3 — Filtrar logs de consultas lentas por palavra-chave

Acesse Autonomy Services > Slow Query Logs. Pesquise por palavra-chave (por exemplo, nome de uma tabela temporária). Verifique os horários de início e duração das consultas para ver se o horário de término da consulta coincide com o momento em que o tempdb parou de crescer.

USE tempdb;
DBCC SHRINKFILE(templog, 1);
GO