locked
Query para deleção estoura tamanho do disco RRS feed

  • Pergunta

  • Pessoal, boa tarde.

     

    Preciso apagar mais de 900000 linhas de um banco, porém tenho apenas 19gb livres do drive C. Quando executo a query, depois de alguns minutos, a partição C chega ao seu limite de tamanho o da pau. Estou usando DELETE FROM com a clausula de WHERE na coluna X e BETWEEN valor1 AND valor2.

     

    Alguém tem uma sugestão para isso não acontecer?

     

    Obrigado.

    sexta-feira, 2 de dezembro de 2011 17:48

Respostas

  • Boa Noite,

    Sugiro montar uma consulta do tipo SET Based Iteration. Ex:

    CREATE TABLE #TMP (ID INT IDENTITY(1,1))

    INSERT INTO #TMP DEFAULT VALUES
    GO 1000000

    DECLARE @TamanhoLote INT, @TotalLotes INT, @Iterador INT
    SET @TamanhoLote = 1000
    SET @TotalLotes = ((SELECT COUNT(*) FROM #TMP) / @TamanhoLote) + 1
    SET @Iterador = 1

    WHILE @Iterador <= @TotalLotes
    BEGIN
     DELETE TOP(@TamanhoLote) FROM #TMP
     PRINT 'O Lote ' + CAST(@Iterador As VARCHAR(5)) + ' foi executado'
     PRINT CAST(@TamanhoLote As VARCHAR(10)) + ' linhas foram deletadas'
     PRINT 'O range foi da linha ' + CAST(((@Iterador * @TamanhoLote) + 1) As VARCHAR(10)) + ' até ' + CAST((@Iterador * @TamanhoLote) As VARCHAR(10))
     -- BACKUP Log para não encher o log (Não faça Shrink só backup de log)
     -- Coloque uma espera opcional para não sobrescrever arquivos de log YYYYMMDD HHMMSS
     -- WAITFOR DELAY '00:00:01'
     SET @Iterador = @Iterador + 1
     
    END

    Ao invés de colocar #TMP. Coloque sua consulta. O tamanho ideal do lote só mesmo com a experiência

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos: http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível
    segunda-feira, 5 de dezembro de 2011 02:00
  • Bruno

     

    Complicado, pois se não houvesse filtro nem FK, seria possivel realizar um truncate table, não gerando assim mais do que algumas linhas de log, deletando os dados dessa maneira, para cada linha sera gerado uma entrada no log, causando mesmo um estouro, como voce esta dentro de uma transação, nada do log sera apagado até que sua transação seja concluida, pois na pior das hipoteses, voce tera o log de sua sessão barrando os demais, claro que outras transações paralelas a sua tambem fazem este log crescer, pois, por mais que elas terminem, como a mais antiga, que é a sua não terminou, ela não irão terminar tambem.

    Como estamos falando de um delete, ele precisa memso manter o log.

    Existem algumas coisas que voce pode fazer:

    DELETE TOP (UmNumero) FROM Suatabela

    Assim, cada execução que voce fizer, ele fara uma escrita no log, e ao termino, ja as limpara, se a base estiver em recovery simples, o problema disso é que voce não consegue nem colocar em um loop de repetição, pois o SQL Server entenderia como apenas uma transação, então voce teria que ficar executando a mesma query, até não aparecer mais nada.

     

    Outra solução é, que caso voce tenha um outro disco com espaço disponivel, uma opção viavel seria criar um novo arquivo de log, o SQL Server ira balancear a carga entre os 2 arquivos, e caso um deles venha a enxer, utilizara o outro.


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com
    sexta-feira, 2 de dezembro de 2011 18:53
    Moderador

Todas as Respostas

  • Bruno,

     

    Provavelmente o tamanho esta estourando devido ao arquivo de log certo? Voce esta deletando a tabela inteira ou existe um filtro para dados?


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com
    sexta-feira, 2 de dezembro de 2011 17:57
    Moderador
  • Fabrizzio, 

     

    Exatamente isso, tanto é que quando enche o meu Log, eu faço um shirink dele, assim liberando voltando o tamanho do disco.

     

    Abaixo exatamente a query que estou rodando. Esse me banco é muito grande, pois é de Log do meu ERP (Microsiga), então, tudo que é feito no sistema é registrado nessa tabale AUDIT_TRAIL. Esse meu between é o periodo entre essas duas datas.

    DELETE FROM AUDIT_TRAIL where AT_DATE between 20110515 and 20110517
    

     

    Att.

    sexta-feira, 2 de dezembro de 2011 18:38
  • Bruno

     

    Complicado, pois se não houvesse filtro nem FK, seria possivel realizar um truncate table, não gerando assim mais do que algumas linhas de log, deletando os dados dessa maneira, para cada linha sera gerado uma entrada no log, causando mesmo um estouro, como voce esta dentro de uma transação, nada do log sera apagado até que sua transação seja concluida, pois na pior das hipoteses, voce tera o log de sua sessão barrando os demais, claro que outras transações paralelas a sua tambem fazem este log crescer, pois, por mais que elas terminem, como a mais antiga, que é a sua não terminou, ela não irão terminar tambem.

    Como estamos falando de um delete, ele precisa memso manter o log.

    Existem algumas coisas que voce pode fazer:

    DELETE TOP (UmNumero) FROM Suatabela

    Assim, cada execução que voce fizer, ele fara uma escrita no log, e ao termino, ja as limpara, se a base estiver em recovery simples, o problema disso é que voce não consegue nem colocar em um loop de repetição, pois o SQL Server entenderia como apenas uma transação, então voce teria que ficar executando a mesma query, até não aparecer mais nada.

     

    Outra solução é, que caso voce tenha um outro disco com espaço disponivel, uma opção viavel seria criar um novo arquivo de log, o SQL Server ira balancear a carga entre os 2 arquivos, e caso um deles venha a enxer, utilizara o outro.


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com
    sexta-feira, 2 de dezembro de 2011 18:53
    Moderador
  • Boa Tarde, Bruno.
    Se no servidor que possui o banco de dados tiver uma outra unidade de disco com espaço de sobra você pode mover os arquivos.

    Este link pode te ajudar: http://msdn.microsoft.com/pt-br/library/ms345408.aspx


    Espero ter ajudado.


    Faça a sua parte, se você abriu tópico de pergunta acompanhe até obter a resolução e classifique as respostas que mais lhe ajudou, caso resolva o problema sozinho volte e compartilhe a resolução do problema.
    sexta-feira, 2 de dezembro de 2011 19:30
  • Boa Noite,

    Sugiro montar uma consulta do tipo SET Based Iteration. Ex:

    CREATE TABLE #TMP (ID INT IDENTITY(1,1))

    INSERT INTO #TMP DEFAULT VALUES
    GO 1000000

    DECLARE @TamanhoLote INT, @TotalLotes INT, @Iterador INT
    SET @TamanhoLote = 1000
    SET @TotalLotes = ((SELECT COUNT(*) FROM #TMP) / @TamanhoLote) + 1
    SET @Iterador = 1

    WHILE @Iterador <= @TotalLotes
    BEGIN
     DELETE TOP(@TamanhoLote) FROM #TMP
     PRINT 'O Lote ' + CAST(@Iterador As VARCHAR(5)) + ' foi executado'
     PRINT CAST(@TamanhoLote As VARCHAR(10)) + ' linhas foram deletadas'
     PRINT 'O range foi da linha ' + CAST(((@Iterador * @TamanhoLote) + 1) As VARCHAR(10)) + ' até ' + CAST((@Iterador * @TamanhoLote) As VARCHAR(10))
     -- BACKUP Log para não encher o log (Não faça Shrink só backup de log)
     -- Coloque uma espera opcional para não sobrescrever arquivos de log YYYYMMDD HHMMSS
     -- WAITFOR DELAY '00:00:01'
     SET @Iterador = @Iterador + 1
     
    END

    Ao invés de colocar #TMP. Coloque sua consulta. O tamanho ideal do lote só mesmo com a experiência

    [ ]s,

    Gustavo Maia Aguiar
    Blog: http://gustavomaiaaguiar.wordpress.com
    Vídeos: http://www.youtube.com/user/gmasql


    Classifique as respostas. O seu feedback é imprescindível
    segunda-feira, 5 de dezembro de 2011 02:00
  • Este post foi encerrado por ser considerado um post antigo.

    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 5 de junho de 2018 17:36
    Moderador