Alertas aleatórios e constantes de "Log Growths", como fazer isso parar?

Isso está informando que um ou vários logs de transações estão crescendo repetidamente, o que significa que está definido para o modo de recuperação FULL ou BULK LOGGED sem backups de log de transações ou com backups insuficientes ou alguém escreveu uma consulta muito ruim ou longa que está mantendo o log de transações ativo.

Você deve procurar todos os bancos de dados do modelo de recuperação COMPLETO naquele servidor e quando foi a última vez que um backup do log de transações ocorreu primeiro, pois isso é o mais perigoso. O tlog pode crescer até ocupar todo o disco, interrompendo todas as gravações nesse banco de dados ou em qualquer outro banco de dados com dados ou logs nesse disco, supondo que não haja um tamanho máximo definido. Se não houver, movendo para frente, você provavelmente desejará definir um tamanho máximo para que 1 DB não derrube todos os DBs tlogs.

SELECT name, recovery_model_desc 
FROM sys.databases

Vê algum que está cheio? Em caso afirmativo, quando foi o último backup do log de transações? Aqui está um script para ajudá-lo nisso:

SELECT   d.name,
         d.recovery_model_desc,
         MAX(b.backup_finish_date) AS backup_finish_date
FROM     master.sys.databases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'L'
GROUP BY d.name, d.recovery_model_desc
ORDER BY backup_finish_date DES

Se isso não fornecer as informações de que você precisa, aqui está uma maneira de encontrar as 10 principais transações mais antigas do msdn:

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
GO

Finalmente, depois de ver qual banco de dados tem arquivos de log crescentes, você pode ver o que está mantendo o arquivo de log aberto. Pode até ser algo como configuração de replicação ou outras soluções HA que não foram totalmente removidas. Isso lhe dirá:

SELECT [log_reuse_wait_desc]
    FROM [master].[sys].[databases]
--  WHERE [name] = N'DBNAME';
GO

Isso não é um erro. É apenas um arquivo de log muito pequeno e com crescimento automático ativado. Isso significa que, enquanto houver transações em execução, o processo do servidor SQL precisa expandir o arquivo de log. A expansão do arquivo de log é um processo de encadeamento único e cada transação que precisa gravar algo no log de transações deve esperar. E não: sem IFI - você tem que esperar enquanto o servidor sql está gravando todos os zeros no disco.

Felizmente, você pode consultar os traços padrão sobre todos os eventos de encolhimento e crescimento. Modificado de sqlblog.com para eventos de redução/crescimento de log:

DECLARE @filename VARCHAR(MAX)
SELECT @filename = CAST(value AS VARCHAR(MAX))
FROM fn_trace_getinfo(DEFAULT)
WHERE property = 2
  AND value IS NOT NULL

SELECT gt.EventClass,
       te.Name AS EventName, 
       gt.TEXTData,
       gt.NTUserName,
       gt.NTDomainName,
       gt.HostName,
       gt.ApplicationName,
       gt.LoginName,
       gt.SPID,
       gt.StartTime,
       gt.EndTime,
       gt.ObjectName,
       gt.DatabaseName,
       gt.FileName
FROM [fn_trace_gettable](@fileName, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE  EventClass In (92, 95) 
ORDER BY StartTime;