Início / dba / Perguntas / 23305
Accepted
Ross Bush
Asked: 2012-08-29 09:57:16 +0800 CST

Sql Server - Práticas recomendadas para aumentar os arquivos de banco de dados

  • 772

Tenho monitorado o crescimento de arquivos por meio do coletor de dados no sql server 2008 r2 por duas semanas. O banco de dados está crescendo consistentemente em torno de 35(MB)/dia. O banco de dados ainda não atingiu o tamanho inicial de 2 GB.

O crescimento automático dos arquivos de banco de dados está definido para 5 MB e eu gostaria de tentar uma abordagem diferente, por isso estou procurando sugestões e/ou comentários.

Há uma tarefa de ajuste que é executada toda semana na noite de domingo à 1h30. A tarefa irá:

  • Verifique a integridade do banco de dados
  • Encolher o arquivo de log - (Isso é bom porque o modo de log é simples)
  • Encolher banco de dados
  • Reorganizar Índice
  • Índice de reconstrução
  • Atualizar estatísticas
  • Limpar histórico

Gostaria de adicionar mais duas etapas ao plano de ajuste semanal:

  1. Aumente o arquivo de banco de dados em 500 MB se o espaço usado atingir um determinado limite ou tamanho total.
  2. Aumente o arquivo de log em 250 MB (após a redução) se o espaço usado atingir um determinado limite de tamanho total.

Ao colocar a carga de crescimento em horas off-line, espero ganhar desempenho reduzindo o número de eventos de crescimento automático durante cargas pesadas.

Eu tenho duas perguntas relacionadas a arquivos de crescimento automático.

  • O melhor lugar para colocar as etapas de crescimento do arquivo seria antes das etapas atuais ou depois?
  • Se eu usar o ALTER DATABASE|MODIFY FILEpara aumentar o arquivo, como posso determinar se SpaceUsedInFile >= (TotalFileSpace-@AllowanceThreshold)?
sql-server sql-server-2008

2 respostas

  1. Best Answer

    Você deve ter como objetivo crescer automaticamente o mínimo possível. Sete vezes por dia é excruciante, mesmo com a inicialização instantânea do arquivo.

    Não faça um banco de dados Shrink. Sempre. Shrinkfile, talvez, mas só depois de um evento extraordinário. Encolhê-lo apenas para crescer novamente é um exercício de futilidade e deveria ser chamado de auto-fragmento.

    Se o modelo de recuperação for simples, não há como aumentar seu arquivo de log em 250 GB. O espaço usado no arquivo será limpo automaticamente ao longo do tempo, a menos que você tenha iniciado uma transação há um mês e não tenha a intenção de confirmá-la ou revertê-la.

    Então meu conselho seria:

    Aumente automaticamente o arquivo de dados manualmente durante um período de silêncio para um tamanho que acomode vários meses de crescimento. Para que você está guardando enquanto isso?

    Defina o incremento de crescimento automático para o arquivo de dados para algo relativamente pequeno (para que ele não interrompa os usuários quando isso acontecer) e alerte sobre esse evento (você pode capturá-lo no rastreamento padrão, por exemplo, ou por meio de estendido eventos). Isso pode dizer que você está atingindo o ponto alto estimado e é hora de crescer manualmente novamente. Neste ponto você vai querer manter este manual caso queira adicionar um novo arquivo / grupo de arquivos em uma unidade diferente para acomodar o espaço, pois eventualmente você preencherá a unidade atual.

    Aumente automaticamente o arquivo de log para, digamos, duas vezes o maior que já foi. Ele não deve crescer automaticamente, a menos que haja alguma transação anormal segurando as coisas. Você deve monitorar este evento também, para que você saiba sobre eles.

    • 28

  2. O crescimento automático é algo que você deve tentar evitar, se possível. O problema é que você não tem controle sobre quando o crescimento pode acontecer e seu sistema pode sofrer um sério golpe enquanto isso.

    Defina o tamanho do seu arquivo para algo sensato por um mês ou mais e monitore sua taxa de crescimento a partir daí, calcule quanto espaço você estima para X período de tempo e defina seu tamanho para isso + uma margem de erro.

    Configurei um trabalho de monitoramento simples que me alertará quando o tamanho do arquivo atingir um máximo predefinido antes do crescimento automático. Você poderia usar algo assim:

    SELECT instance_name,
       [Data File(s) Size (KB)],
       [LOG File(s) Size (KB)],
       [Log File(s) Used Size (KB)],
       [Percent Log Used]
       into ##Logsize
FROM
(
   SELECT *
   FROM sys.dm_os_performance_counters
   WHERE counter_name IN
   (
       'Data File(s) Size (KB)',
       'Log File(s) Size (KB)',
       'Log File(s) Used Size (KB)',
       'Percent Log Used'
   )
     AND instance_name = 'database your interested in' 
) AS Src
PIVOT
(
   MAX(cntr_value)
   FOR counter_name IN
   (
       [Data File(s) Size (KB)],
       [LOG File(s) Size (KB)],
       [Log File(s) Used Size (KB)],
       [Percent Log Used]
   )
) AS pvt 
go
declare @logsize int
Select @logsize = [Percent Log Used] from ##Logsize

If @logsize > the maximum percent you want the log to fill too i.e 90
    BEGIN
        --Do your thing here
    END

Drop table ##Logsize

    Claro que isso poderia ser agendado como um trabalho.

    • 12

relate perguntas