A maneira mais simples de reduzir os arquivos de log de transações em um banco de dados de produção espelhado

Question

Qual é a maneira mais simples de reduzir o arquivo de log de transações em um banco de dados de produção espelhado?

Eu tenho que, como o meu espaço em disco está se esgotando.

Eu farei um backup completo do banco de dados antes de fazer isso, então eu não preciso manter nada do log de transações (certo? Eu tenho backup diário completo, provavelmente nunca preciso de restauração point-in-time, embora eu manterá a opção aberta se eu puder - isso é tudo para que o arquivo .ldf seja realmente correto?).

Resolvido:
OK, depois de fazer 2 backups através do SSMS (não TSQL) de apenas o log, criando um novo conjunto de backup , o diálogo Shrink-Files-Log no SSMS finalmente funcionou , liberando algum espaço em disco.

Não sei por que 2 backups foram necessários, ou porque o TSQL não funcionou, e não houve diferença no "espaço disponível para ser recuperado" na caixa de diálogo de redução (foi 99% para todas as tentativas de encolhimento após o primeiro backup também, mas ainda não liberou espaço nenhum), mas o problema foi resolvido por enquanto. Obrigado a todos.

    

Answer 1

Faça um backup do log de transações, com qualquer método com o qual você se sinta mais confortável .

Isso fará com que os logs de transações que já foram confirmados no banco de dados sejam excluídos do disco. Idealmente, você deve criar uma tarefa de manutenção de banco de dados para fazer isso para você de forma recorrente exatamente por esse motivo - para eliminar os logs de transações antigos para não encher o disco.

Pela outra parte da sua pergunta ... não, na verdade não. Sim, eles executam essa função, mas não apenas essa função.

Bancos de dados não são armazenados em backup (ou gravados) de maneira tradicional que outros arquivos são, porque o próprio arquivo de banco de dados está constantemente em uso e em constante mudança. Portanto, um único backup de "point in time" exigiria que o banco de dados fosse colocado offline para "congelá-lo" em um estado consistente ou resultar em diferentes partes do backup contendo dados diferentes dos que estavam lá quando o backup foi iniciado.

Quais logs de transação são registros de cada "transação" realizada pelo banco de dados. Em vez de gravar no arquivo de banco de dados toda vez que um registro é alterado, atualizado, adicionado, removido etc., essas ações são gravadas em um arquivo separado, um log de transações e confirmadas no arquivo de banco de dados quando o servidor SQL determina sua segurança fazer isso sem interromper nenhuma atividade. Portanto, os logs de transação são, na verdade, onde as alterações no banco de dados ocorrem antes que elas realmente se tornem alterações no banco de dados [arquivo].

Portanto, se você precisar voltar a um determinado estado do banco de dados ou apontar no tempo, os logs de transação serão "reproduzidos". Essencialmente, não copiar os dados do arquivo, mas ir para o estado point-in-time mais recente encontrado para o banco de dados e, em seguida, fazer as mesmas coisas que levaram o banco de dados ao estado especificado [later]. Porém, é importante observar que, a qualquer momento, seus logs de transação conterão transações que ainda não foram confirmadas no banco de dados. Então, eles são mais do que apenas a capacidade de realizar uma restauração de ponto no tempo. Eles contêm [algumas] alterações que estão sendo feitas ou serão feitas em breve no banco de dados.

É por isso que você é forçado a fazer um backup antes de limpar os logs de transação - assim que o backup é feito, o sistema tem uma cópia point-in-time do banco de dados para fazer referência a qualquer restauração futura, e é capaz de determinar quais transações foram confirmadas no banco de dados e quais não foram. E com essa informação, o sistema sabe quais logs de transação obsoletos serão deletados para você e quais não.

Isso pode, no entanto, levar algum tempo, dependendo do tamanho de seus logs de transação. Se você nunca fez um antes, prepare-se, vai demorar um pouco.

    

Answer 2

Esta é uma pergunta mais antiga, mas há algumas coisas que não foram bem explicadas e esperamos lançar alguma luz. A maneira de encolher foi respondida, principalmente, mas isso explicará a parte "por que" do que você está realmente fazendo.

Backups, especificamente backups de log, fazem várias coisas. Os dados são gravados no disco como um conjunto de backup a ser usado ou descartado posteriormente, conforme necessário. Cada backup subsequente é adicionado a esse conjunto, em um banco de dados totalmente registrado. Truncar o log ou iniciar um novo conjunto de backup interrompe a cadeia e inibe, ou em muitos casos, nega sua capacidade de restaurar em um ponto no tempo antes da quebra da cadeia.

Os dados no log não são realmente excluídos, nem o arquivo de log é reduzido durante um backup. Os VLFs ativos são marcados como inativos, exceto aqueles que não podem ser totalmente confirmados - eles permanecem ativos. Os VLFs inativos podem ser reescritos, tornando seu registro circular, na verdade, como uma cobra comendo sua própria cauda. Um ponto de verificação é emitido, como parte do backup, que informa ao DB para começar a gravar no início do log, assim que o VLF atual for preenchido. Se você executar uma redução neste ponto, você ganhará de volta todo o espaço no final do log, até o ponto do VLF ativo.

A razão pela qual um segundo backup parece "fazer o truque" é porque o VLF ativo normalmente é preenchido durante esse tempo e o log está sendo escrito desde o início. Quando você faz o segundo backup, o VLF ativo é gravado no disco como parte do conjunto de backup (ou não) e o VLF é marcado como inativo. Como esse era o final do log devido à redução anterior, a execução de uma redução agora libera todo o espaço para o início do log, até o VLF ativo no momento.

Tudo isso pressupõe um par de coisas, 1.) você não tem VLFs enormes que levam horas ou dias para encher e 2.) seu banco de dados é bastante inativo e não há um monte de transações sendo escritas para o log. Se qualquer uma dessas condições for um problema para você, encolher seu registro também será um problema.

Tudo isso é verdadeiro para bancos de dados não espelhados e espelhados. A diferença é que você só precisa executar a manutenção no primário em um cenário espelhado, supondo que seu espelho seja construído de forma idêntica.

    

Answer 3

O recurso de espelhamento usa o log para rastrear as coisas até saber que o outro servidor possui essas alterações. Então, não, o ldf não é apenas para recuperação pontual. (Também é importante para alguns esquemas de replicação, mas você não está fazendo isso.) Mesmo TRUNCATE_ONLY não descartará as alterações registradas para coisas que o SQL possa precisar. O exemplo clássico é uma transação grande ou longa. Se você estiver na metade de uma transação de uma hora e um DBA for executado TRUNCATE_ONLY, seu material não será removido do LDF. O LDF pode continuar a crescer ou experimentar outros problemas. Se o DBA matar sua conexão, aguarda a conclusão da reversão e, em seguida, executa TRUNCATE_ONLY, o log deve ser liberado.

Você já tentou usar:

select log_reuse_wait_desc from sys.databases where name = 'mydb' 

para ver porque o log é tão grande? A Microsoft documenta essa tabela de sistema aqui .

Você também pode executar:

dbcc opentran() 

Isso é meio antigo, mas deve mostrar transações de longa duração nesse banco de dados. A Microsoft documenta esse comando aqui .

Eu gostaria de ter certeza de que há um backup de log acontecendo em um agendamento.

Eu me certificaria de dar ao comando TRUNCATE_ONLY um pouco de tempo para trabalhar, às vezes demora um pouco para o SQL começar a escrever para um VLF na frente do arquivo LDF. Se o último VLF no arquivo LDF for o que está sendo gravado, o SQL não poderá encurtar o arquivo. Caso contrário, eu faria um BACKUP DATABASE completo (com COPY_ONLY, ou esperaria até que o backup regular acontecesse, se isso não estivesse muito distante no futuro). Às vezes, isso parece dar o pontapé inicial às coisas, mas pode ser que um backup me distraia enquanto aguardo o VLF atual passar para a frente do arquivo LDF. Depois que o VLF atual se mover para a frente do arquivo LDF, você poderá usar o dbcc shrinkfile () e obter o resultado esperado. Eu recomendo tentar remover pequenos pedaços primeiro, ao invés de tentar fazer tudo de uma só vez.

Além disso, você deseja evitar fazer isso regularmente, pois entrar em um ciclo de autogrow shrink-autogrow-shrink repetitivo pode ser um matador de desempenho. (Isso pode levar a arquivos fragmentados e o processo de crescimento real pode levar uma quantidade surpreendente de tempo, durante o qual nenhuma transação seria capaz de se comprometer. Cresça seus arquivos grandes o suficiente para que eles não cresçam automaticamente. não invocado.