Início / dba / Perguntas / 230491
Accepted
HoneyBadger
Asked: 2019-02-23 07:00:41 +0800 CST

Espaço em disco cheio durante a inserção, o que acontece?

  • 772

Hoje descobri que o disco rígido que armazena meus bancos de dados estava cheio. Isso já aconteceu antes, geralmente a causa é bastante evidente. Normalmente, há uma consulta incorreta, que causa grandes vazamentos no tempdb, que crescem até o disco ficar cheio. Desta vez foi um pouco menos evidente o que aconteceu, já que tempdb não foi a causa da unidade cheia, foi o próprio banco de dados.

Os fatos:

  • O tamanho normal do banco de dados é de cerca de 55 GB, cresceu para 605 GB.
  • O arquivo de log tem tamanho normal, o arquivo de dados é enorme.
  • Datafile tem 85% de espaço disponível (eu interpreto isso como 'ar': espaço que foi usado, mas foi liberado. O SQL Server reserva todo o espaço uma vez alocado).
  • O tamanho do Tempdb é normal.

Encontrei a causa provável; há uma consulta que seleciona muitas linhas (junção ruim causa a seleção de 11 bilhões de linhas onde são esperadas algumas centenas de milhares). Esta é uma SELECT INTOconsulta, o que me fez pensar se o seguinte cenário poderia ter acontecido:

  • SELECT INTO é executado
  • A tabela de destino é criada
  • Os dados são inseridos conforme são selecionados
  • O disco enche, fazendo com que a inserção falhe
  • SELECT INTO é abortado e revertido
  • A reversão libera espaço (os dados já inseridos são removidos), mas o SQL Server não libera o espaço liberado.

Nesta situação, no entanto, eu não esperava que a tabela criada pelo SELECT INTOainda existisse, ela deveria ser descartada pelo rollback. Eu testei isso:

BEGIN TRANSACTION 
SELECT  T.x
INTO    TMP.test
FROM    (VALUES(1))T(x)

ROLLBACK

SELECT  * 
FROM    TMP.test

Isto resulta em:

(1 row affected)
Msg 208, Level 16, State 1, Line 8
Invalid object name 'TMP.test'.

No entanto, a tabela de destino existe. A consulta real não foi executada em uma transação explícita, isso pode explicar a existência da tabela de destino?

As suposições que esbocei aqui estão corretas? Este é um cenário provável de ter acontecido?

sql-server sql-server-2016

2 respostas

  1. Best Answer

    A consulta real não foi executada em uma transação explícita, isso pode explicar a existência da tabela de destino?

    Sim, exatamente assim.

    Se você fizer um simples select intofora de um explicit transaction, existem dois transactionsno modo autocommit: o primeiro cria o tablee o segundo o preenche.

    Você pode provar isso para si mesmo desta forma:

    Em um servidor de teste dedicado databaseem simple recovery model, primeiro faça um checkpointe certifique-se de que o log contenha apenas algumas linhas (3 no caso de 2016) relacionadas ao checkpoint. Em seguida, execute uma select intolinha e verifique lognovamente, procurando por um begin tranassociado a select into:

    checkpoint;

select *
from sys.fn_dblog(null, null);

select 'a' as col
into dbo.t3;  

select *
from sys.fn_dblog(null, null)
where Operation = 'LOP_BEGIN_XACT'
      and [Transaction Name] = 'SELECT INTO';

    Você obterá 2 linhas, mostrando que você tinha 2 transactions.

    As suposições que esbocei aqui estão corretas? Este é um cenário provável de ter acontecido?

    Sim, eles estão corretos.

    A insertparte de select intowas rolled back, mas não libera nenhum espaço de dados. Você pode verificar isso executando sp_spaceused; você verá muitos unallocated space.

    Se você quiser que o banco de dados libere esse espaço não alocado, você deve usar shrinkseu(s) arquivo(s) de dados.

    • 17

  2. Você está correto, o SELECT...INTOcomando não é atômico. Isso não foi documentado no momento da postagem original, mas agora é chamado especificamente na página SELECT - INTO Clause (Transact-SQL) no MS Docs (yay open source!):

    A SELECT...INTOinstrução opera em duas partes - a nova tabela é criada e as linhas são inseridas. Isso significa que, se as inserções falharem, todas serão revertidas, mas a nova tabela (vazia) permanecerá. Se você precisar que toda a operação seja bem-sucedida ou falhe como um todo, use uma transação explícita .

    Vou criar um banco de dados que usa o modelo de recuperação completo. Vou fornecer um arquivo de log bastante pequeno e, em seguida, informar que o arquivo de log não pode crescer automaticamente:

    CREATE DATABASE [SelectIntoTestDB]
ON PRIMARY 
( 
    NAME = N'SelectIntoTestDB', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\SelectIntoTestDB.mdf', 
    SIZE = 8192KB, 
    FILEGROWTH = 65536KB
)
LOG ON 
( 
    NAME = N'SelectIntoTestDB_log', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\SelectIntoTestDB_log.ldf', 
    SIZE = 8192KB, 
    FILEGROWTH = 0
)

    E então tentarei inserir todas as postagens da minha cópia do banco de dados StackOverflow2010. Isso deve gravar um monte de coisas no arquivo de log.

    USE [SelectIntoTestDB];
GO

SELECT *
INTO dbo.Posts
FROM StackOverflow2010.dbo.Posts;

    Isso resultou no seguinte erro após a execução por 4 segundos:

    Msg 9002, Level 17, State 4, Line 1
    O log de transações do banco de dados 'SelectIntoTestDB' está cheio devido a 'ACTIVE_TRANSACTION'.

    Mas há uma tabela Posts vazia no meu novo banco de dados:

    captura de tela de zero resultados da tabela recém-criada

    Então, como você suspeitava, deu CREATE TABLEcerto, mas a INSERTparte foi toda revertida. Uma solução alternativa seria usar uma transação explícita (que você já observou em sua pergunta).

    • 16

relate perguntas