none
Acompanhamento de Crescimento de Log com Alertas do SQL Server RRS feed

  • Pergunta

  • Caros companheiros, configurei o alerta abaixo com intuito de acompanhar o crescimento do arquivo de log, porém tenho recebido alertas.

    USE [msdb]
    GO
    EXEC msdb.dbo.sp_update_alert @name=N'Alerta de Percentual: Crescimento Log > 80%', 
    		@message_id=0, 
    		@severity=0, 
    		@enabled=1, 
    		@delay_between_responses=0, 
    		@include_event_description_in=0, 
    		@database_name=N'', 
    		@notification_message=N'', 
    		@event_description_keyword=N'', 
    		@performance_condition=N'SQLServer:Databases|Percent Log Used|Base|>|80', 
    		@wmi_namespace=N'', 
    		@wmi_query=N'', 
    		@job_id=N'00000000-0000-0000-0000-000000000000'
    GO
    EXEC msdb.dbo.sp_update_notification @alert_name=N'Alerta de Percentual: Crescimento Log > 80%', @operator_name=N'DBA', @notification_method = 1
    GO

    Porém verificando o tamanho do log pelo comando:

    DBCC SQLPERF(LOGSPACE)

    O mesmo se mantém estável.

    DataBase   LogSize(MB) 
    LogSpaceUsed(%) Status
     Base                 2.030.547 5.993.661     0

    O porque de está recebendo o alerta?

    Agradeço desde já!


    quarta-feira, 29 de janeiro de 2014 19:47

Respostas

  • Jeferson,

    pode ser que algum banco da instância esteja com 80% de utilização, já que você não filtrou para enviar alertas apenas desse banco Base.

    Através do Perfmon, selecione esse contador: Percent Log Used e visualize se algum deles está com utilização acima de 80%

    ou então faça esse select na DMV de performance_counters veja se retorna algum registro, possivelmente retornará o banco que está utilizando maior quantidade de log.

    SELECT *
    FROM sys.dm_os_performance_counters
    WHERE object_name = 'SQLServer:Databases'
    	AND counter_name = 'Percent Log Used'
    	and cntr_value > 80

    segunda-feira, 17 de fevereiro de 2014 12:30

Todas as Respostas

  • Jeferson,

    pode ser que algum banco da instância esteja com 80% de utilização, já que você não filtrou para enviar alertas apenas desse banco Base.

    Através do Perfmon, selecione esse contador: Percent Log Used e visualize se algum deles está com utilização acima de 80%

    ou então faça esse select na DMV de performance_counters veja se retorna algum registro, possivelmente retornará o banco que está utilizando maior quantidade de log.

    SELECT *
    FROM sys.dm_os_performance_counters
    WHERE object_name = 'SQLServer:Databases'
    	AND counter_name = 'Percent Log Used'
    	and cntr_value > 80

    segunda-feira, 17 de fevereiro de 2014 12:30
  • Jeferson,

    Qual versão e edição do SQL Server você esta utilizando? Se for a partir da 2008, acredito que você uma possibilidade seria utilizar o Objeto Audit ou até mesmo criar uma Policy Management com base na Condition de Log File.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    terça-feira, 18 de fevereiro de 2014 13:14
    Moderador
  • Desculpa pessoal na demora em responder, mas quero agradecer mais uma vez pela atenção e orientação.

    Utilizei o script e foi proveitoso demais, porém filtrei sim o banco a ser monitorado no alerta:

    USE [msdb]
    GO
    EXEC msdb.dbo.sp_update_alert @name=N'Alerta de Percentual: Crescimento Log Smart > 80%', 
    		@message_id=0, 
    		@severity=0, 
    		@enabled=1, 
    		@delay_between_responses=0, 
    		@include_event_description_in=0, 
    		@database_name=N'', 
    		@notification_message=N'', 
    		@event_description_keyword=N'', 
    		@performance_condition=N'SQLServer:Databases|Percent Log Used|[MINHA_BASE_DADOS]|>|80', 
    		@wmi_namespace=N'', 
    		@wmi_query=N'', 
    		@job_id=N'00000000-0000-0000-0000-000000000000'
    GO
    EXEC msdb.dbo.sp_update_notification @alert_name=N'Alerta de Percentual: Crescimento Log Smart > 80%', @operator_name=N'DBA', @notification_method = 1
    GO

    Mas utilizando a DMV acima só trás o resultado da imagem abaixo:

    quarta-feira, 20 de agosto de 2014 01:47
  • Bem pessoal, executei um backup log do banco Model, no qual estava em 90 o percentual de uso do log e assim reduziu para 17%.

    Alterei também o modelo de recuperação para Simple já que faço backup diário das bases de sistema do SQL Server. Acredito que agora venha resolver os alertas sobre o crescimento acima de 80%.

    Obrigado pela atenção.

    segunda-feira, 25 de agosto de 2014 15:00
  • Olá pessoal, reabrindo a thread, pois voltei a ter o mesmo problema com o alerta de crescimento configurado pra banco produção. Analisando o problema com cautela utilizando o SELECT de nosso amigo Leonardo identifiquei que o problema não estava no banco de sistema relatado acima "Model", mas sim no de produção, pois não teria lógica receber notificação de alerta de um banco que não tem ALERTA nenhum configurado. 

    Então fui analisar a configuração do arquivo de log e tenho a seguinte regra configurada: 

    1 - Configurei arquivo de log exatamente com pelo menos os 20% do arquivo de dados;
    2 - Configurei o autogrowth pra crescer com 1/8 do total dos arquivos log e dados;
    3 - Backup das transações de log de 5 em 5 minutos pra liberar o arquivo de log;
    4 - Manutenção do arquivo de log através do script abaixo**:

    USE master
    GO
    ALTER DATABASE BD_Producao1 SET RECOVERY SIMPLE 
    GO
    USE Base_Producao1
    GO
    CHECKPOINT
    GO
    DBCC SHRINKFILE ('Nome_log',63488) 
    GO
    ALTER DATABASE DB_Producao1 SET RECOVERY FULL 
    GO


    **Ressalto que o script só é executado após a execução do plano de manutenção de atualização das estatísticas e reorganização dos índices.

    Verifiquei o percentual alocado dos arquivos para entender a situação:

    DBCC SQLPERF (LOGSPACE)

    EXEC sp_spaceused


    Alguém tem mais alguma dica?

    Lembrando que a versão do SQL Server 2008 R2.

    Agradeço desde já.


    quarta-feira, 21 de janeiro de 2015 12:57
  • Jerfeson,

    Da maneira que você esta fazendo, o arquivo na verdade esta passando pelo processo de Shrink, mas não esta ocorrendo a desalocação do espaço que estava em uso, para isso é necessário utilizar a opção TruncateOnly.

    Veja este exemplo:

    USE master GO ALTER DATABASE Smart SET RECOVERY SIMPLE GO USE Base_Producao1 GO CHECKPOINT GO DBCC SHRINKFILE ('Nome_log',63488) GO

    DBCC SHRINKFILE ('Nome_log',TruncateOnly) GO


    ALTER DATABASE Smart SET RECOVERY FULL GO

    Outra possibilidade seria fazer o Backup do Log utilizando a opção Truncate_Only, também como forma de liberar espaço:

    --Fazendo o Backup do Log e liberando espaços --
    BACKUP LOG CIPA WITH TRUNCATE_ONLY


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 21 de janeiro de 2015 16:40
    Moderador
  • Galvão, não estava usando essa opção porque já tinha sido descontinuada pra versão que uso do SQL Server, porém alterei meu script de manutenção dos Logs tirando o valor e substituindo pelo TRUNCATE_ONLY.

    Percebi que o alerta só ocorre em alguns períodos e com isso suspeitei que alguma operação estivesse sendo executada, na qual necessitava de mais espaço no arquivo de log.

    Dialogando com o suporte do sistema, me disseram que ocorre algumas vezes uma carga de dados. Com isso tenho a suspeita dessa carga esta influenciando no limite estabelecido no arquivo ldf do log, ultrapassando os 80% configurado no alerta. 

    Analisei as minhas bases de produção e todos estão abaixo de 80% de uso do log, veja na imagem abaixo. Irei acompanhar a mudança que fiz no script e ver se amanhã terei alertas no email.

    quarta-feira, 21 de janeiro de 2015 17:35
  • Prezado Galvão, desculpa a demora em dar um retorno.

    O problema do crescimento estava associado ao tamanho defino para o arquivo de log, no qual não estava suportando a rotinas de manutenção dos índices (Reorganização e Rebuild) e atualização de estatísticas. Então o que ocorria, na madrugada são executados as rotinas de reorganização de índices e atualização de estatísticas diariamente e no domingo a de rebuild. Com isso o arquivo log necessitava crescer para acomodar as instruções que estava ocorrendo no banco BD_Producao1.

    Acompanhei o crescimento da base de dados após a execução das rotinas de manutenção para entender como seria o crescimento e até quanto iria crescer o arquivo de log até concluir as operações. Percebi que no primeiro momento que o arquivo crescia até uns 100 GB, mas analisei também que existiam 511 VLF's. Não encarei no primeiro momento como log com fragmentação, porém pra uma base de 400 GB entendi como um nível aceitável. Configurei no final das rotinas de manutenção um backup de log para esvaziar o arquivo e garantir que o mesmo não viesse a crescer. Monitorando o processo percebi novamente um crescimento físico do arquivo de 100 para 115 GB e o aumento das VLF's pra 624 entendendo que a primeira configuração não foi o suficiente. Esse novo fato já me preocupou, pois existe um risco de ficar com pouco espaço em disco, sendo que não quero aplicar o comando de redução de arquivo "shrink" para reduzir o mesmo pra os 100 GB configurado anteriormente, pelo fato de estarmos avaliando a possibilidade implementar Mirroring neste banco e recurso não suporte esse comando no arquivo ldf.  As configurações feita no banco pra o arquivo de log são de auto crescimento 1024 Megas e ilimitado. É possível que esse valor não esteja atendendo bem as operações e diante disso o número de VLF's esteja crescendo junto com o  arquivo físico.

    Gostaria de uma help da galera para melhor evidenciar o problema e definir a solução mais eficaz e adequada.


    Agradeço desde já

     

    terça-feira, 2 de junho de 2015 21:18