none
Shrink em logs RRS feed

  • Pergunta

  • Prezados colegas, boa tarde!

    Atendemos uma empresa que o LOG chega facilmente a 120GB e por conta disso, preciso fazer o Shrink todo dia apoós as 0hs.

    Preciso criar um plano de manutenção no Maintanance plan também para rodar no mesmo horários, esse deve ter:

    - Reindex database;

    - Update statistics;

    - Check integrity.

    Infelizmente (pelo menos pelo wizard) eu não consegui agrgar o Shrink. Há como fazê-lo?

    Sei que preciso mudar o Recovery Model de Full para Simple, fazer o Shrink e depois voltar de Simple para Full. É possível agregá-lo ao meu maintanance plan? Se não, é possível fazê-lo por jobs?

    Agradeço desde já.

    Att

    sexta-feira, 29 de junho de 2012 17:22

Respostas

  • Boa tarde Akira

    O modo de recuperação está full, não é? Pq você não implementa o backup de log que trunca automaticamente seu arquivo de log? Rodar o shrink no arquivo de log, deve ser evitado sempre que possível, pq voce vai encolher algo que ele vai precisar "esticar novamente", causando um grande esforço de processamento. Se sua base de dados não é critica, voce "pode" deixa-la como simple, porém é valido avaliar o custo/beneficio desta opção.

    At.
    Rafael Melo

    • Marcado como Resposta Professor Akira terça-feira, 10 de julho de 2012 15:18
    sexta-feira, 29 de junho de 2012 18:26
  • É uma péssima pratica fazer o que você deseja, de qualquer forma...

    Você pode colocar um T-SQL e executar as ações que achar necessárias ex.:

    USE [master]
    ALTER DATABASE dba_TESTE SET RECOVERY SIMPLE WITH NO_WAIT
    USE dba_TESTE
    DBCC SHRINKFILE (2,1)
    GO
    ALTER DATABASE [dba_TESTE] SET RECOVERY FULL WITH NO_WAIT

    Não fosse algumas necessidades bem específicas, o shirink deveria ser abolido do SQL Server.

    Cuidado com isso Professor, pode invalidar sua rotina de backups. No blog do Gustavo Maia Aguiar na sessão de "Piores Prátricas" ele detalha muito bem o assunto.


    View Ricardo Muramatsu's profile on LinkedIn

    • Marcado como Resposta Professor Akira terça-feira, 10 de julho de 2012 15:19
    sexta-feira, 29 de junho de 2012 18:29

Todas as Respostas

  • Boa tarde Akira

    O modo de recuperação está full, não é? Pq você não implementa o backup de log que trunca automaticamente seu arquivo de log? Rodar o shrink no arquivo de log, deve ser evitado sempre que possível, pq voce vai encolher algo que ele vai precisar "esticar novamente", causando um grande esforço de processamento. Se sua base de dados não é critica, voce "pode" deixa-la como simple, porém é valido avaliar o custo/beneficio desta opção.

    At.
    Rafael Melo

    • Marcado como Resposta Professor Akira terça-feira, 10 de julho de 2012 15:18
    sexta-feira, 29 de junho de 2012 18:26
  • É uma péssima pratica fazer o que você deseja, de qualquer forma...

    Você pode colocar um T-SQL e executar as ações que achar necessárias ex.:

    USE [master]
    ALTER DATABASE dba_TESTE SET RECOVERY SIMPLE WITH NO_WAIT
    USE dba_TESTE
    DBCC SHRINKFILE (2,1)
    GO
    ALTER DATABASE [dba_TESTE] SET RECOVERY FULL WITH NO_WAIT

    Não fosse algumas necessidades bem específicas, o shirink deveria ser abolido do SQL Server.

    Cuidado com isso Professor, pode invalidar sua rotina de backups. No blog do Gustavo Maia Aguiar na sessão de "Piores Prátricas" ele detalha muito bem o assunto.


    View Ricardo Muramatsu's profile on LinkedIn

    • Marcado como Resposta Professor Akira terça-feira, 10 de julho de 2012 15:19
    sexta-feira, 29 de junho de 2012 18:29
  • Boa tarde Akira

    O modo de recuperação está full, não é? Pq você não implementa o backup de log que trunca automaticamente seu arquivo de log? Rodar o shrink no arquivo de log, deve ser evitado sempre que possível, pq voce vai encolher algo que ele vai precisar "esticar novamente", causando um grande esforço de processamento. Se sua base de dados não é critica, voce "pode" deixa-la como simple, porém é valido avaliar o custo/beneficio desta opção.

    At.
    Rafael Melo

    É que o backup fica na Alog e volta e meia não sei o porque, não sei se o backup falha, mas o log fica com 120GB e para a produção.

    Entendi que o log funciona mais ou menos como os discos virtuais com alocação dinâmica correto? Demandando muito processamento toda vez que é necessários "realocar" informações, já que é preciso "esticar" novamente...

    Obrigado

    sexta-feira, 29 de junho de 2012 19:11
  • Exatamente Akira....

    Bom, suponhamos que o backup não falhe muitos dias seguidos... você deve ter algum processo que está gerando muito log, pq para alcançar 120GB de log usando apenas as transações de uma aplicação por exemplo. Por acaso voce nao tem rotina de reindex rodando nesses dias que seu log enche? Pq quando usamos o modo de recuperação full todas as transações são completamente logadas, inclusive as operações de reindex.

    At.
    Rafael


    sexta-feira, 29 de junho de 2012 20:27
  • Você tem backup de log deste database?

    Veja, quando você faz um backup de log (pode ser full ou dif também) o SQL libera o espaço (como vc disse virtual) dentro do log para re-uso.

    Se você não faz backup de log, não tem Mirroring nem Logshipping, avalie a possibilidade de manter o log como Single.

    Por se tratar de produção, é muito importante definir se é estratégicamente aceitável a perda de dados (desde seu ultimo backup full ou diff), se não for viável esta perda, configure os backups de log (neste caso você NÃO deverá fazer o shirink sob pena de perder o lsn e impossibilitar um restore da sequencia de logs).

    Aqui na empresa meu Log está estável em 70Gb e o banco possui 250Gb, faço backup log a cada meia hora.

    Quanto ao "esticar" veja, o Log se definido como autogrouth irá "esticar" até um tamanho que suporte suas transações e ficará estável (assim como o meu). O Shirink "encolhe" o arquivo no NTFS e claro sempre que há a necessidade de crescimento haverá mais demanda do hardware (podendo reduzir a performance). Famoso efeito sanfona.

    O Shirink deve ser evitando nem tanto pelo "efeito sanfona" (uma vez que é possível melhorar a performance de disco em um Raid 10 adicionando mais discos, mas sim pela fragmentação que isto irá causar (se não estive em storage) e principalmente pelo forte impacto em rotinas de backup.

    Considere usar o recovery mode em Simple, ou reduzir (ou implementar) o tempo entre um backup log e outro. Desta forma poderá dispensar o Shirink e eliminar o risco de um conjunto de backups inválidos.

    Como está na Alog, veja se tem contrato de DR neste caso, colocar o banco em Simple é bem mais viável.


    View Ricardo Muramatsu's profile on LinkedIn

    sexta-feira, 29 de junho de 2012 20:40
  • Olá Professor Akira,

         Você pode sim ter o Reindex, Update Statistics e checkdb em um unico plano de manutenção.

         Segue o modelo de que você pode implementar no seu ambiente e ainda evitar o crescimento do log:

    1º - Task: ALTER DATABASE base SET RECOVERY SIMPLE (T-SQL Task) ---Vai evitar que o log fique muito grande no momento do reindex e Up.Statistics;

    2º - Task: Reindex (Rebuild Index Task)

    3º - Task: Update Statistics (Update Statistics Task)

    4º - Task: Backup FULL --- Para evitar que o backup diferencial fiquei muito grande (Caso utilize)

    5º - Task: ALTER DATABASE base SET RECOVERY FULL (T-SQL Task)

         Toda essa rotina você pode montar pelo Plano de manuteção.

    Caso tenha alguma dificuldade pode informar que vamos te ajudar.

    segunda-feira, 2 de julho de 2012 12:33
  • Cara, não tem segredo !! Backup de LOG e shirink depois ! Conforme os amigos disseram anteriormente, verifique se realmente há necessidade do seu banco está full, ja que você levantou a hipotese de passá-lo pra SIMPLE e voltá-lo pra full, creio que vocês não utilizam restore point in time, tail log, etc.
    segunda-feira, 9 de julho de 2012 01:31
  • Obrigado a todos.

    Att

    terça-feira, 10 de julho de 2012 15:20
  • Pessoal,

    Aproveitando o assunto!!

    Tenho que fazer backup de varias bases espalhadas por vários servidores na empresa e muitas vezes apagam e criam databases sem eu saber.

    Para não ter que ficar toda hora olhando o que foi criado e o que foi apagado, fiz um script vbs utilizando sqlcmd que pega a lista atual de todas as bases naquele servidor (select name from sysdatabases) e faz um BKP full de cada uma delas e mantenho os arquivos por uma semana (7 dias).
    (backup database ["DATABASE"] to disk = 'lalalala.bak' with noformat, noinit, NAME = N'LALALALA - Full backup')

    Tudo é executado via vbs, sqlcmd e Windows Task.

    Depois de um bom tempo sempre recebo uma reclamação de aumento de uso de espaço em disco e tenho que fazer shirink nos arquivos ldf.

    Faço o famoso " Alterar para 'Simple' fazer o Shink e depois voltar para 'Full' ".

    Mas agora estou com uma dúvida:

    Já que os logs servem para fazer um recovery 'point in time' a partir de um backup full e meu Backup é executado todos os dias com retenção de 7 dias, então só preciso manter 7 dias de log também. Certo?

    Posso adicionar o shirink no script para que todos os dias execute junto com o backup?

    Mas então como faço para que ele não apague todo o log e mantenha sempre os últimos 7 dias? É possível?





    quinta-feira, 12 de julho de 2012 17:38
  • Jullyson,

    Com o que entendi sobre o seu texto, a política de backup utilizado na empresa são os backups full e os transaction log das bases. Não utiliza os backups diferenciais. Os backups full das bases são executados diariamente.

    Dependendo da criticidade e tolerância de perdas de dados em uma falha adotado pela empresa, não há necessidade de se ter todos os transaction log da semana já que os backups full são executados diariamente e estes possuem todas as atualizações que também encontra-se nos transaction log.

    Um exemplo: Backup full - Domingo à Domingo sempre às 20h e Transaction log - a cada 20 minutos. Ocorrendo uma falha na Quinta às 19:45, restaura-se o backup full da Quarta e em seguida restauram-se os transaction log gerados depois do último backup full até o momento da falha que neste exemplo seria às 19:40, ou seja, nesse caso haveria a perda de dados na base de apenas 5 minutos.

    Nesse caso, reter todos os backups Transaction log gerados a partir da última execução do backup full pois os Transaction log gerados antes do último backup full estão desatualizados em relação ao backup full recente.

    Segue script T-SQL para a realização do shrink log file:

    USE AdventureWorks2008R2;
    GO
    -- Para a execução do shrink truncando o arquivo de log, é necessário mudar a database para o modo RECOVERY SIMPLE.
    ALTER DATABASE AdventureWorks2008R2
    SET RECOVERY SIMPLE;
    GO
    -- Executa o Shrink do arquivo de log, truncando e setando o novo valor para 1 MB.
    DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
    GO
    -- Após o término do shrink altera-se a base para o modo RECOVERY FULL.
    ALTER DATABASE AdventureWorks2008R2
    SET RECOVERY FULL;
    GO

    Espero ter ajudado!

    Abraço

    sexta-feira, 27 de julho de 2012 20:06
  • Boa Noite,

    Eu desaconselho em qualquer situação rodar o seguinte script:

    -- Para a execução do shrink truncando o arquivo de log, é necessário mudar a database para o modo RECOVERY SIMPLE.
    ALTER DATABASE AdventureWorks2008R2
    SET RECOVERY SIMPLE;
    GO
    -- Executa o Shrink do arquivo de log, truncando e setando o novo valor para 1 MB.
    DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
    GO
    -- Após o término do shrink altera-se a base para o modo RECOVERY FULL.
    ALTER DATABASE AdventureWorks2008R2
    SET RECOVERY FULL;
    GO

    No momento em que você muda o Recovery Model para SIMPLE, você está dizendo o seguinte ao SQL Server:

    - Por favor SQL Server, quebre minha sequencia de logs, pois, eu não quero mais arquivá-los no arquivo LDF. Estou ciente de que ao pedir isso pra você não poderei mais restaurar um backup de log desse ponto em diante até a hora que eu fizer um backup full ou diferencial e também estou plenamento ciente desse risco.

    Isso significa que a partir daquele momento, você está impossibilitado de tirar novos backups de log e sua base não poderá ser restaurada nesse ponto em diante (mesmo mudando para recovery model full logo em seguida). Um novo backup full naturalmente volta a tornar possível o backup de log e seu uso para restauração, uma vez que representa um novo ponto de partida e os logs subsequentes ao backup full poderão ser utilizados.

    A informação de que para a execução do shrink truncando o arquivo de log, é necessário mudar a database para o modo RECOVERY SIMPLE não é verdadeira. O que acontece é que para efetuar o SHRINK, o arquivo de log precisa ter espaço livre e as entradas do log não podem enchê-lo. Se o Recovery Model é Simple, a cada CHECKPOINT essas entradas são livres. Se o Recovery Model é FULL, basta você tirar um backup de log e conseguirá efetuar o SHRINK. O script abaixo demonstra que isso é plenamente possível:

    -- Cria um banco de dados
    CREATE DATABASE BDLog

    -- Tira o primeiro backup full da base (é preciso um ponto de partida)
    BACKUP DATABASE BDLog TO DISK = 'C:\Backups\BDLog.BAK'

    -- Mostra o tamanho do Log (Tamanho 568, usado 256)
    SELECT counter_name, instance_name, cntr_value
    FROM sys.dm_os_performance_counters
    WHERE counter_name LIKE 'Log File(s)%' and instance_name = 'BDLog'

    -- Cria uma tabela na base BDLog
    CREATE TABLE BDLog.dbo.T (Campo CHAR(8000))

    -- Insere 30.000 linhas (demora uns 50 segundos)
    INSERT INTO BDLog.dbo.T VALUES (REPLICATE('a',8000))
    GO 30000

    -- Mostra o tamanho do Log (aumentou)
    SELECT counter_name, instance_name, cntr_value
    FROM sys.dm_os_performance_counters
    WHERE counter_name LIKE 'Log File(s)%' and instance_name = 'BDLog'

    -- Faz um backup de log (demora uns 20 segundos)
    BACKUP LOG BDLog TO DISK = 'C:\Backups\BDLog01.TRN'

    -- Mostra o tamanho do Log (o usado diminuiu logo existe área livre)
    SELECT counter_name, instance_name, cntr_value
    FROM sys.dm_os_performance_counters
    WHERE counter_name LIKE 'Log File(s)%' and instance_name = 'BDLog'

    -- Efetua o SHRINK no log (sem mudar o Recovery Model)
    -- Pode não reduzir por conta do VLF
    USE BDLog
    go
    DBCC SHRINKFILE(2,1)
    GO

    -- Mostra o tamanho do Log (o usado diminuiu logo existe área livre)
    SELECT counter_name, instance_name, cntr_value
    FROM sys.dm_os_performance_counters
    WHERE counter_name LIKE 'Log File(s)%' and instance_name = 'BDLog'

    -- Faz um segundo backup de log (instantaneo)
    BACKUP LOG BDLog TO DISK = 'C:\Backups\BDLog02.TRN'

    -- Efetua o SHRINK no log (sem mudar o Recovery Model)
    -- Pode não reduzir por conta do VLF
    USE BDLog
    go
    DBCC SHRINKFILE(2,1)
    GO

    -- Mostra o tamanho do Log (o usado diminuiu logo existe área livre)
    SELECT counter_name, instance_name, cntr_value
    FROM sys.dm_os_performance_counters
    WHERE counter_name LIKE 'Log File(s)%' and instance_name = 'BDLog'

    -- Muda o contexto
    USE master
    GO

    -- Mata o banco
    DROP DATABASE BDLog

    Não mude o Recovery Model para SIMPLE e depois fique voltando para FULL. Se você compreender como funciona o log de transações, verá que em nenhuma situação isso faz sentido.

    [ ]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

    sexta-feira, 27 de julho de 2012 23:07
  • Boa Noite,

    Eu desaconselho em qualquer situação rodar o seguinte script:

    -- Para a execução do shrink truncando o arquivo de log, é necessário mudar a database para o modo RECOVERY SIMPLE.
    ALTER DATABASE AdventureWorks2008R2
    SET RECOVERY SIMPLE;
    GO
    -- Executa o Shrink do arquivo de log, truncando e setando o novo valor para 1 MB.
    DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
    GO
    -- Após o término do shrink altera-se a base para o modo RECOVERY FULL.
    ALTER DATABASE AdventureWorks2008R2
    SET RECOVERY FULL;
    GO

    No momento em que você muda o Recovery Model para SIMPLE, você está dizendo o seguinte ao SQL Server:

    - Por favor SQL Server, quebre minha sequencia de logs, pois, eu não quero mais arquivá-los no arquivo LDF. Estou ciente de que ao pedir isso pra você não poderei mais restaurar um backup de log desse ponto em diante até a hora que eu fizer um backup full ou diferencial e também estou plenamento ciente desse risco.

    Isso significa que a partir daquele momento, você está impossibilitado de tirar novos backups de log e sua base não poderá ser restaurada nesse ponto em diante (mesmo mudando para recovery model full logo em seguida). Um novo backup full naturalmente volta a tornar possível o backup de log e seu uso para restauração, uma vez que representa um novo ponto de partida e os logs subsequentes ao backup full poderão ser utilizados.

    A informação de que para a execução do shrink truncando o arquivo de log, é necessário mudar a database para o modo RECOVERY SIMPLE não é verdadeira. O que acontece é que para efetuar o SHRINK, o arquivo de log precisa ter espaço livre e as entradas do log não podem enchê-lo. Se o Recovery Model é Simple, a cada CHECKPOINT essas entradas são livres. Se o Recovery Model é FULL, basta você tirar um backup de log e conseguirá efetuar o SHRINK. O script abaixo demonstra que isso é plenamente possível:

    -- Cria um banco de dados
    CREATE DATABASE BDLog

    -- Tira o primeiro backup full da base (é preciso um ponto de partida)
    BACKUP DATABASE BDLog TO DISK = 'C:\Backups\BDLog.BAK'

    -- Mostra o tamanho do Log (Tamanho 568, usado 256)
    SELECT counter_name, instance_name, cntr_value
    FROM sys.dm_os_performance_counters
    WHERE counter_name LIKE 'Log File(s)%' and instance_name = 'BDLog'

    -- Cria uma tabela na base BDLog
    CREATE TABLE BDLog.dbo.T (Campo CHAR(8000))

    -- Insere 30.000 linhas (demora uns 50 segundos)
    INSERT INTO BDLog.dbo.T VALUES (REPLICATE('a',8000))
    GO 30000

    -- Mostra o tamanho do Log (aumentou)
    SELECT counter_name, instance_name, cntr_value
    FROM sys.dm_os_performance_counters
    WHERE counter_name LIKE 'Log File(s)%' and instance_name = 'BDLog'

    -- Faz um backup de log (demora uns 20 segundos)
    BACKUP LOG BDLog TO DISK = 'C:\Backups\BDLog01.TRN'

    -- Mostra o tamanho do Log (o usado diminuiu logo existe área livre)
    SELECT counter_name, instance_name, cntr_value
    FROM sys.dm_os_performance_counters
    WHERE counter_name LIKE 'Log File(s)%' and instance_name = 'BDLog'

    -- Efetua o SHRINK no log (sem mudar o Recovery Model)
    -- Pode não reduzir por conta do VLF
    USE BDLog
    go
    DBCC SHRINKFILE(2,1)
    GO

    -- Mostra o tamanho do Log (o usado diminuiu logo existe área livre)
    SELECT counter_name, instance_name, cntr_value
    FROM sys.dm_os_performance_counters
    WHERE counter_name LIKE 'Log File(s)%' and instance_name = 'BDLog'

    -- Faz um segundo backup de log (instantaneo)
    BACKUP LOG BDLog TO DISK = 'C:\Backups\BDLog02.TRN'

    -- Efetua o SHRINK no log (sem mudar o Recovery Model)
    -- Pode não reduzir por conta do VLF
    USE BDLog
    go
    DBCC SHRINKFILE(2,1)
    GO

    -- Mostra o tamanho do Log (o usado diminuiu logo existe área livre)
    SELECT counter_name, instance_name, cntr_value
    FROM sys.dm_os_performance_counters
    WHERE counter_name LIKE 'Log File(s)%' and instance_name = 'BDLog'

    -- Muda o contexto
    USE master
    GO

    -- Mata o banco
    DROP DATABASE BDLog

    Não mude o Recovery Model para SIMPLE e depois fique voltando para FULL. Se você compreender como funciona o log de transações, verá que em nenhuma situação isso faz sentido.

    [ ]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

    Gustavo, boa noite. 

    Apenas escrevi no fórum para ajudar um colega nosso! A própria biblioteca do site oficial http://msdn.microsoft.com/pt-br/library/ms189493

    coloca o script como exemplo.

    segunda-feira, 30 de julho de 2012 04:09