Uma resposta mais curta:

Você provavelmente tem uma transação de longa execução em execução (manutenção de índice? Exclusão ou atualização de grande lote?) ou você está no modo de recuperação "padrão" (mais abaixo sobre o que se entende por padrão) Fulle não fez um backup de log (ou não os está tomando com freqüência suficiente).

Se for um problema de modelo de recuperação, a resposta simples pode ser Mudar para Simpleo modo de recuperação se você não precisar de recuperação pontual e backups de log regulares. Muitas pessoas, no entanto, fazem disso sua resposta sem entender os modelos de recuperação. Continue lendo para entender por que isso é importante e, em seguida, decida o que você faz. Você também pode começar a fazer backups de log e permanecer em Fullrecuperação.

Pode haver outros motivos, mas esses são os mais comuns. Esta resposta começa a mergulhar nos dois motivos mais comuns e fornece algumas informações básicas sobre o porquê e como por trás dos motivos, além de explorar alguns outros motivos.

Uma resposta mais longa: Quais cenários podem fazer com que o log continue crescendo? Há muitos motivos, mas geralmente esses motivos são os dois padrões a seguir: Há um mal-entendido sobre os modelos de recuperação ou há transações de longa execução. Leia para obter detalhes.

Principal motivo 1/2: não entender os modelos de recuperação

( Estar no modo de recuperação total e não fazer backups de log - esse é o motivo mais comum - a grande maioria das pessoas com esse problema é. )

Embora esta resposta não seja um aprofundamento nos modelos de recuperação do SQL Server, o tópico dos modelos de recuperação é fundamental para esse problema.

No SQL Server, existem três modelos de recuperação :

• Full,
• Bulk-Loggede
• Simple.

Ignoraremos Bulk-Loggedpor enquanto diremos que é um modelo híbrido e a maioria das pessoas que estão nesse modelo estão lá por um motivo e entendem os modelos de recuperação.

Os dois com os quais nos preocupamos e sua confusão são a causa da maioria dos casos de pessoas com esse problema Simplee Full.

Intervalo: Recuperação em geral

Antes de falarmos sobre Modelos de Recuperação: vamos falar sobre recuperação em geral. Se você quiser se aprofundar ainda mais nesse tópico, basta ler o blog de Paul Randal e quantos posts quiser sobre ele. Mas para esta pergunta:

1. Recuperação de falha/reinicialização
   Uma finalidade do arquivo de log de transações é a recuperação de falha/reinicialização . Para o avanço e retrocesso do trabalho que foi feito (avançar/refazer) antes de uma falha ou reinicialização e o trabalho que foi iniciado, mas não concluído após uma falha ou reinicialização (reversão/desfazer). É o trabalho do log de transações para ver se uma transação foi iniciada, mas nunca foi concluída (revertida ou falha/reinicialização ocorreu antes da transação ser confirmada). Nessa situação, o trabalho do log é dizer "Ei, isso nunca realmente terminou, vamos reverter" durante a recuperação. Também é o trabalho do log ver se você terminou algo e que seu aplicativo cliente foi informado de que foi concluído (mesmo que ainda não tenha endurecido para seu arquivo de dados) e dizer"Ei... isso realmente aconteceu, vamos avançar, vamos fazer como os aplicativos pensam que era" após uma reinicialização. Agora há mais, mas esse é o objetivo principal.

2. Recuperação pontual
   A outra finalidade de um arquivo de log de transações é nos dar a capacidade de recuperar um ponto no tempo devido a um "oops" em um banco de dados ou garantir um ponto de recuperação no caso de uma falha de hardware envolvendo os dados e/ou arquivos de log de um banco de dados. Se esse log de transações contiver os registros de transações que foram iniciadas e concluídas para recuperação, o SQL Server poderá usar e usará essas informações para obter um banco de dados para onde estava antes de ocorrer um problema. Mas isso nem sempre é uma opção disponível para nós. Para que isso funcione, temos que ter nosso banco de dados no modelo de recuperação correto e fazer backups de log .

Modelos de recuperação

Para os modelos de recuperação:

• Modelo de Recuperação Simples
  Assim, com a introdução acima, é mais fácil falar Simple Recoveryprimeiro sobre o modelo. Neste modelo, você está dizendo ao SQL Server: "Estou bem com você usando seu arquivo de log de transações para recuperação de falha e reinicialização..." (Você realmente não tem escolha. Procure as propriedades ACID e isso deve fazer sentido rapidamente.) "...mas quando você não precisar mais dele para esse propósito de recuperação de falha/reinicialização, vá em frente e reutilize o arquivo de log."

  O SQL Server escuta essa solicitação no Simple Recovery e mantém apenas as informações necessárias para fazer a recuperação de falha/reinicialização. Quando o SQL Server tiver certeza de que pode se recuperar porque os dados estão protegidos no arquivo de dados (mais ou menos), os dados que foram protegidos não são mais necessários no log e são marcados para truncamento - o que significa que são reutilizados.

• Modelo de recuperação completa
  Com Full Recovery, você está informando ao SQL Server que deseja recuperar para um ponto específico no tempo, desde que seu arquivo de log esteja disponível ou para um ponto específico no tempo coberto por um backup de log. Nesse caso, quando o SQL Server atingir o ponto em que seria seguro truncar o arquivo de log no Modelo de Recuperação Simples, ele não fará isso. Em vez disso , ele permite que o arquivo de log continue crescendo e continue crescendo, até que você faça um backup de log (ou fique sem espaço em sua unidade de arquivo de log) em circunstâncias normais.

Mudar de Simples para Completo tem uma pegadinha.

Há regras e exceções aqui. Falaremos detalhadamente sobre transações de longa duração abaixo.

Mas uma ressalva a ser lembrada para o modo de recuperação total é esta: se você apenas alternar para o Full Recoverymodo, mas nunca fizer um backup completo inicial, o SQL Server não atenderá sua solicitação para estar no Full Recoverymodelo. Seu log de transações continuará funcionando como estava Simpleaté que você mude para o Modelo de Recuperação Completa E faça seu primeiro arquivo Full Backup.

O modelo de recuperação completa sem backups de log é ruim.

Então, qual é o motivo mais comum para o crescimento descontrolado de logs? Resposta: Estar no modo Full Recovery sem ter nenhum backup de log.

Isso acontece o tempo todo com as pessoas.

Por que esse é um erro tão comum?

Por que isso acontece o tempo todo? Porque cada novo banco de dados obtém sua configuração inicial do modelo de recuperação observando o banco de dados modelo.

A configuração inicial do modelo de recuperação do modelo é sempre Full Recovery Model- até e a menos que alguém altere isso. Então você poderia dizer que o "modelo de recuperação padrão" é Full. Muitas pessoas não estão cientes disso e têm seus bancos de dados em execução Full Recovery Modelsem backups de log e, portanto, um arquivo de log de transações muito maior do que o necessário. É por isso que é importante alterar os padrões quando eles não funcionam para sua organização e suas necessidades)

O modelo de recuperação completa com poucos backups de log é ruim.

Você também pode ter problemas aqui por não fazer backups de log com frequência suficiente.
Fazer um backup de log por dia pode parecer bom, faz uma restauração exigir menos comandos de restauração, mas tendo em mente a discussão acima, esse arquivo de log continuará crescendo e crescendo até que você faça backups de log.

Como descubro qual a frequência de backup de log que preciso?

Você precisa considerar sua frequência de backup de log com duas coisas em mente:

1. Necessidades de Recuperação - Espero que isso seja o primeiro. Caso a unidade que hospeda seu log de transações fique ruim ou você tenha uma corrupção séria que afete seu backup de log, quantos dados podem ser perdidos? Se esse número não for maior que 10 a 15 minutos, você precisará fazer o backup de log a cada 10 a 15 minutos, fim da discussão.
2. Crescimento de log - Se sua organização está bem em perder mais dados devido à capacidade de recriar facilmente esse dia, pode ser bom ter um backup de log com muito menos frequência do que 15 minutos. Talvez sua organização esteja bem com cada 4 horas. Mas você tem que ver quantas transações você gera em 4 horas. Permitir que o log continue crescendo nessas quatro horas tornará um arquivo de log muito grande? Isso significa que seus backups de log demoram muito?

Principal motivo 2/2: transações de longa duração

( "Meu modelo de recuperação está bem! O log ainda está crescendo! )

Isso também pode ser uma causa de crescimento de log descontrolado e irrestrito. Não importa o modelo de recuperação, mas geralmente aparece como "Mas estou no modelo de recuperação simples - por que meu log ainda está crescendo?!"

A razão aqui é simples: se o SQL estiver usando esse log de transações para fins de recuperação, como descrevi acima, ele precisará voltar ao início de uma transação.

Se você tiver uma transação que leva muito tempo ou faz muitas alterações, o log não pode truncar no ponto de verificação para nenhuma das alterações que ainda estão em transações abertas ou que foram iniciadas desde o início dessa transação.

Isso significa que uma grande exclusão, a exclusão de milhões de linhas em uma instrução de exclusão, é uma transação e o log não pode truncar até que toda a exclusão seja concluída. Em Full Recovery Model, essa exclusão é registrada e pode haver muitos registros de log. Mesma coisa com o trabalho de otimização de índice durante as janelas de manutenção. Isso também significa que um mau gerenciamento de transações e não observar e fechar transações abertas pode realmente prejudicar você e seu arquivo de log.

O que posso fazer sobre essas transações de longa duração?

Você pode se salvar aqui:

• Dimensionar adequadamente seu arquivo de log para levar em conta o pior cenário possível - como sua manutenção ou grandes operações conhecidas. E quando você aumentar seu arquivo de log, você deve olhar para esta orientação (e os dois links que ela envia para você) por Kimberly Tripp. O dimensionamento correto é super crítico aqui.
• Observando seu uso de transações. Não inicie uma transação em seu servidor de aplicativos e comece a ter longas conversas com o SQL Server e corra o risco de deixar uma aberta por muito tempo.
• Observando as transações implícitas em suas declarações DML. Por exemplo: UPDATE TableName Set Col1 = 'New Value'é uma transação. Eu não coloquei um BEGIN TRANlá e não preciso, ainda é uma transação que apenas confirma automaticamente quando concluída. Portanto, se estiver realizando operações em um grande número de linhas, considere agrupar essas operações em partes mais gerenciáveis ​​e dar tempo de recuperação ao log. Ou considere o tamanho certo para lidar com isso. Ou talvez procure alterar os modelos de recuperação durante uma janela de carregamento em massa.

Esses dois motivos também se aplicam ao Log Shipping?

Resposta curta: sim. Resposta mais longa abaixo.

Pergunta: "Estou usando o envio de logs, então meus backups de logs são automatizados... Por que ainda estou vendo o crescimento do log de transações?"

Resposta: continue lendo.

O que é Log Shipping?

O envio de log é exatamente o que parece - você está enviando seus backups de log de transações para outro servidor para fins de DR. Há alguma inicialização, mas depois disso o processo é bastante simples:

• Um trabalho para fazer backup do log em um servidor,
• um trabalho para copiar esse backup de log e
• um trabalho para restaurá-lo sem recuperação (ou NORECOVERYou STANDBY) no servidor de destino.

Há também alguns trabalhos para monitorar e alertar se as coisas não saírem como você planejou.

Em alguns casos, você pode querer fazer a restauração do envio de logs apenas uma vez por dia ou a cada três dias ou uma vez por semana. Está bem. Mas se você fizer essa alteração em todas as tarefas (incluindo as tarefas de backup e cópia de log), isso significa que você está esperando todo esse tempo para fazer um backup de log. Isso significa que você terá muito crescimento de log - porque você está no modo de recuperação total sem backups de log - e provavelmente também significa um grande arquivo de log para copiar. Você só deve modificar o agendamento da tarefa de restauração e permitir que os backups e cópias de log ocorram com mais frequência, caso contrário, você sofrerá com o primeiro problema descrito nesta resposta.

Solução de problemas gerais por meio de códigos de status

Existem outros motivos além desses dois, mas esses são os mais comuns. Independentemente da causa: existe uma maneira de analisar o motivo desse crescimento inexplicável de log/falta de truncamento e ver quais são.

Ao consultar a sys.databasesvisualização do catálogo, você pode ver informações que descrevem o motivo pelo qual seu arquivo de log pode estar aguardando truncar/reutilizar.

Há uma coluna chamada log_reuse_waitcom um ID de pesquisa do código do motivo e uma log_reuse_wait_desccoluna com uma descrição do motivo da espera. Do artigo online de livros referenciados estão a maioria das razões (as que você provavelmente verá e as que podemos explicar as razões. As que faltam estão fora de uso ou para uso interno) com algumas notas sobre a espera em itálico :

• 0 = Nada
  O que parece... Não deveria estar esperando

• 1 = Checkpoint
  Aguardando a ocorrência de um checkpoint. Isso deve acontecer e você deve ficar bem - mas há alguns casos para procurar aqui para respostas ou edições posteriores.

• 2 = Backup de log
  Você está aguardando a ocorrência de um backup de log. Ou você os programou e isso acontecerá em breve, ou você tem o primeiro problema descrito aqui e agora sabe como corrigi-lo

• 3 = Backup ou restauração ativa
  Uma operação de backup ou restauração está sendo executada no banco de dados

• 4 = Transação ativa
  Há uma transação ativa que precisa ser concluída (de qualquer forma - ROLLBACKou COMMIT) antes que o log possa ser copiado. Esta é a segunda razão descrita nesta resposta.

• 5 = Espelhamento de banco de dados
  Um espelho está ficando para trás ou sob alguma latência em uma situação de espelhamento de alto desempenho ou o espelhamento está pausado por algum motivo

• 6 = Replicação
  Pode haver problemas com a replicação que causariam isso - como um agente de leitura de log não sendo executado, um banco de dados pensando que está marcado para replicação que não está mais e vários outros motivos. Você também pode ver esse motivo e é perfeitamente normal porque você está olhando na hora certa, assim como as transações estão sendo consumidas pelo leitor de log

• 7 = Criação de instantâneo de banco de dados
  Você está criando um instantâneo de banco de dados, você verá isso se observar o momento certo enquanto um instantâneo está sendo criado

• 8 = Varredura de log
  Ainda não encontrei um problema com isso funcionando para sempre. Se você olhar o suficiente e com frequência suficiente, poderá ver isso acontecer, mas não deve ser uma causa de crescimento excessivo do log de transações, que eu já vi.

• 9 = Uma réplica secundária dos Grupos de Disponibilidade AlwaysOn está aplicando registros de log de transações desse banco de dados a um banco de dados secundário correspondente. Sobre a descrição mais clara ainda ..

Como não estou realmente satisfeito com nenhuma das respostas no Stack Overflow , incluindo a sugestão mais votada, e porque há algumas coisas que gostaria de abordar e que a resposta de Mike não, pensei em fornecer minha entrada aqui também. Coloquei uma cópia desta resposta lá também.

Tornar um arquivo de log menor deve realmente ser reservado para cenários em que ele encontrou um crescimento inesperado que você não espera que aconteça novamente. Se o arquivo de log crescer para o mesmo tamanho novamente, não será possível reduzi-lo temporariamente. Agora, dependendo dos objetivos de recuperação do seu banco de dados, essas são as ações que você deve tomar.

Primeiro, faça um backup completo

Nunca faça alterações em seu banco de dados sem garantir que você possa restaurá-lo caso algo dê errado.

Se você se preocupa com a recuperação pontual

(E por recuperação pontual, quero dizer que você se preocupa em poder restaurar para qualquer coisa que não seja um backup completo ou diferencial.)

Presumivelmente, seu banco de dados está no FULLmodo de recuperação. Se não, certifique-se de que é:

ALTER DATABASE yourdb SET RECOVERY FULL;

Even if you are taking regular full backups, the log file will grow and grow until you perform a log backup - this is for your protection, not to needlessly eat away at your disk space. You should be performing these log backups quite frequently, according to your recovery objectives. For example, if you have a business rule that states you can afford to lose no less than 15 minutes of data in the event of a disaster, you should have a job that backs up the log every 15 minutes. Here is a script that will generate timestamped file names based on the current time (but you can also do this with maintenance plans etc., just don't choose any of the shrink options in maintenance plans, they're awful).

DECLARE @path NVARCHAR(255) = N'\\backup_share\log\yourdb_' 
  + CONVERT(CHAR(8), GETDATE(), 112) + '_'
  + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
  + '.trn';

BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;

Note that \\backup_share\ should be on a different machine that represents a different underlying storage device. Backing these up to the same machine (or to a different machine that uses the same underlying disks, or a different VM that's on the same physical host) does not really help you, since if the machine blows up, you've lost your database and its backups. Depending on your network infrastructure it may make more sense to backup locally and then transfer them to a different location behind the scenes; in either case, you want to get them off the primary database machine as quickly as possible.

Now, once you have regular log backups running, it should be reasonable to shrink the log file to something more reasonable than whatever it's blown up to now. This does not mean running SHRINKFILE over and over again until the log file is 1 MB - even if you are backing up the log frequently, it still needs to accommodate the sum of any concurrent transactions that can occur. Log file autogrow events are expensive, since SQL Server has to zero out the files (unlike data files when instant file initialization is enabled), and user transactions have to wait while this happens. You want to do this grow-shrink-grow-shrink routine as little as possible, and you certainly don't want to make your users pay for it.

Note that you may need to back up the log twice before a shrink is possible (thanks Robert).

So, you need to come up with a practical size for your log file. Nobody here can tell you what that is without knowing a lot more about your system, but if you've been frequently shrinking the log file and it has been growing again, a good watermark is probably 10-50% higher than the largest it's been. Let's say that comes to 200 MB, and you want any subsequent autogrowth events to be 50 MB, then you can adjust the log file size this way:

USE [master];
GO
ALTER DATABASE Test1 
  MODIFY FILE
  (NAME = yourdb_log, SIZE = 200MB, FILEGROWTH = 50MB);
GO

Note that if the log file is currently > 200 MB, you may need to run this first:

USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);
GO

If you don't care about point-in-time recovery

If this is a test database, and you don't care about point-in-time recovery, then you should make sure that your database is in SIMPLE recovery mode.

ALTER DATABASE yourdb SET RECOVERY SIMPLE;

Putting the database in SIMPLE recovery mode will make sure that SQL Server re-uses portions of the log file (essentially phasing out inactive transactions) instead of growing to keep a record of all transactions (like FULL recovery does until you back up the log). CHECKPOINT events will help control the log and make sure that it doesn't need to grow unless you generate a lot of t-log activity between CHECKPOINTs.

Next, you should make absolute sure that this log growth was truly due to an abnormal event (say, an annual spring cleaning or rebuilding your biggest indexes), and not due to normal, everyday usage. If you shrink the log file to a ridiculously small size, and SQL Server just has to grow it again to accommodate your normal activity, what did you gain? Were you able to make use of that disk space you freed up only temporarily? If you need an immediate fix, then you can run the following:

USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
-- 200 MB
DBCC SHRINKFILE(yourdb_log, 200);
GO

Otherwise, set an appropriate size and growth rate. As per the example in the point-in-time recovery case, you can use the same code and logic to determine what file size is appropriate and set reasonable autogrowth parameters.

Some things you don't want to do

• Back up the log with TRUNCATE_ONLY option and then SHRINKFILE. For one, this TRUNCATE_ONLY option has been deprecated and is no longer available in current versions of SQL Server. Second, if you are in FULL recovery model, this will destroy your log chain and require a new, full backup.

• Detach the database, delete the log file, and re-attach. I can't emphasize how dangerous this can be. Your database may not come back up, it may come up as suspect, you may have to revert to a backup (if you have one), etc. etc.

• Use the "shrink database" option. DBCC SHRINKDATABASE and the maintenance plan option to do the same are bad ideas, especially if you really only need to resolve a log problem issue. Target the file you want to adjust and adjust it independently, using DBCC SHRINKFILE or ALTER DATABASE ... MODIFY FILE (examples above).

• Shrink the log file to 1 MB. This looks tempting because, hey, SQL Server will let me do it in certain scenarios, and look at all the space it frees! Unless your database is read only (and it is, you should mark it as such using ALTER DATABASE), this will absolutely just lead to many unnecessary growth events, as the log has to accommodate current transactions regardless of the recovery model. What is the point of freeing up that space temporarily, just so SQL Server can take it back slowly and painfully?

• Create a second log file. This will provide temporarily relief for the drive that has filled your disk, but this is like trying to fix a punctured lung with a band-aid. You should deal with the problematic log file directly instead of just adding another potential problem. Other than redirecting some transaction log activity to a different drive, a second log file really does nothing for you (unlike a second data file), since only one of the files can ever be used at a time. Paul Randal also explains why multiple log files can bite you later.

Be proactive

Instead of shrinking your log file to some small amount and letting it constantly autogrow at a small rate on its own, set it to some reasonably large size (one that will accommodate the sum of your largest set of concurrent transactions) and set a reasonable autogrow setting as a fallback, so that it doesn't have to grow multiple times to satisfy single transactions and so that it will be relatively rare for it to ever have to grow during normal business operations.

The worst possible settings here are 1 MB growth or 10% growth. Funny enough, these are the defaults for SQL Server (which I've complained about and asked for changes to no avail) - 1 MB for data files, and 10% for log files. The former is much too small in this day and age, and the latter leads to longer and longer events every time (say, your log file is 500 MB, first growth is 50 MB, next growth is 55 MB, next growth is 60.5 MB, etc. etc. - and on slow I/O, believe me, you will really notice this curve).

Further reading

Please don't stop here; while much of the advice you see out there about shrinking log files is inherently bad and even potentially disastrous, there are some people who care more about data integrity than freeing up disk space.

• A blog post I wrote in 2009, when I saw a few "here's how to shrink the log file" posts spring up.

• A blog post Brent Ozar wrote four years ago, pointing to multiple resources, in response to a SQL Server Magazine article that should not have been published.

• A blog post by Paul Randal explaining why t-log maintenance is important and why you shouldn't shrink your data files, either.

• Mike Walsh has a great answer above, of course, covering some of these aspects too, including reasons why you might not be able to shrink your log file immediately.

You can also see the content of your log file. To do that, you can use the undocumented fn_dblog, or a transaction log reader, such as ApexSQL Log.

It doesn't show index reorganization, but it shows all DML and various DDL events: ALTER, CREATE, DROP, trigger enable/disable, grant/revoke permissions, object rename.

Disclaimer: I work for ApexSQL as a Support Engineer

This is the most frequently faced issue for almost all the DBAs where the logs grows and fills out the disk.

•What are some reasons the transaction log grows so large?

1. Long Active Transaction
2. High logging transactions like Index rebuild, re-organise, Bulk Insert, Deletes etc.
3. Any HA like Replication, Mirroring configured which holds the log and does not allow it release the log space

•Why is my log file so big?

Check for the log_reuse_wait_desc column in sys.databases table to know what is holding the logs from truncating:

select name, log_reuse_wait_desc 
from sys.databases

•What are some ways to prevent this problem from occurring?

Log backups will help you control the log growth unless there is something that is holding up the logs from being reused.

•What do I do when I get myself on track with the underlying cause and want to put my transaction log file to a healthy size?

If you have identified what actually is causing it then try to fix it accordingly as explained in below page.

https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/

Having proper log backups scheduled is the best way of dealing with log growth unless for an unusual situation.