Considerações sobre as configurações "autogrow" e "autoshrink" no SQL Server

Traduções deste artigo Traduções deste artigo
ID do artigo: 315512 - Exibir os produtos aos quais esse artigo se aplica.
Expandir tudo | Recolher tudo

Sumário

As configurações autogrow e autoshrink padrão funcionam sem ajustes em muitos sistemas SQL Server. No entanto, há ambientes em que não é necessários ativar as classificações ou em que pode ser necessário ajustar os parâmetros autogrow e autoshrink. Este artigo contém algumas informações que orientarão você a selecionar as configurações do seu ambiente.

Mais Informações

Considere os seguintes tópicos caso decida ajustar os parâmetros autogrow e autoshrink.

Como faço para definir as configurações?

  1. Para definir as configurações autogrow e autoshrink, use um dos seguintes métodos:
    • Uma declaração ALTER DATABASE (não disponível no SQL Server 7.0)
    • O SQL Server Management Studio ou o SQL Enterprise Manager
    • O procedimento armazenado sp_dboption (preterido no SQL Server 2005)
    Observação Se estiver executando o SQL Server 2005, use o SQL Server Management Studio em vez do SQL Enterprise Manager. Para obter mais informações sobre como definir essas configurações no SQL Server 2005, visite os seguintes sites do Microsoft Developer Network (MSDN):
    Como: Adicionar arquivos de dados ou de logs a um banco de dados (SQL Server Management Studio)
    http://msdn2.microsoft.com/pt-br/library/ms189253.aspx
    Propriedades do banco de dados (página Arquivos)
    http://msdn2.microsoft.com/pt-br/library/ms180254.aspx
    Você também pode configurar a opção autogrow ao criar um banco de dados.

    É possível ver as configurações atuais nas propriedades do banco de dados no SQL Enterprise Manager (SEM). Outra alternativa é executar o seguinte comando Transact-SQL:
    sp_helpdb [ [ @dbname= ] 'name' ]
  2. Lembre-se de que as configurações autogrow são definidas por arquivo. Portanto, você precisa defini-las em pelo menos dois locais para cada banco de dados (um para o arquivo de dados primário e outro para o arquivo de log primário). Se tiver muitos arquivos de dados e/ou de log, você deverá definir as opções em cada arquivo individualmente. Dependendo do ambiente, é possível que você obtenha configurações diferentes para cada arquivo de banco de dados.

Como isso afeta o desempenho?

  • Se você executar uma transação que exija mais espaço em log do que o disponível e se tiver ativado a opção autogrow do log de transações do banco de dados, o tempo gasto para a conclusão da transação incluirá o tempo que o log de transações leva para atingir o tamanho configurado. Se o incremento de crescimento for grande ou se houver algum outro fator que faça com que o processo demore muito, poderá ocorrer falha na consulta em que a transação é aberta devido a um erro de tempo limite. O mesmo tipo de problema pode resultar do crescimento automático da parte de dados do banco de dados. Para alterar a configuração autogrow, consulte o tópico "ALTER DATABASE" nos Manuais Online do SQL Server.
  • Se você executar uma transação grande que exija o aumento do log, outras transações que exigem uma gravação no log de transações também precisarão aguardar a conclusão da operação de crescimento.
  • Se combinar as opções autogrow e autoshrink, você poderá criar uma sobrecarga desnecessária. Garanta que os limites que acionam as operações de crescimento e redução não causem alterações de tamanho frequentes. Por exemplo, você pode executar uma transação que faça com que o log cresça 100 MB até o momento da confirmação. Algum tempo depois, a opção autoshrink é iniciada e reduz o log de transações em 100 MB. Em seguida, você executa a mesma transação, o que faz com que o log de transações aumente 100 MB novamente. Nesse exemplo, você está criando sobrecarga desnecessária e provavelmente está causando a fragmentação do arquivo de log. Em ambos os casos, o desempenho pode ser afetado negativamente.
  • A fragmentação física resultante da alteração do tamanho dos arquivos de dados ou de log pode ter um grande impacto no desempenho. Isso acontecerá se você usar as configurações automáticas ou se aumentar e diminuir manualmente os arquivos com frequência.
  • Se aumentar o banco de dados por pequenos incrementos ou se aumentá-lo e, em seguida, reduzi-lo, você poderá causar a fragmentação do disco. Em algumas circunstâncias, a fragmentação do disco pode resultar em problemas de desempenho. Um cenário de pequenos incrementos de crescimento também pode prejudicar o desempenho do sistema.
  • No SQL Server 2005 ou em versões posteriores, você pode habilitar a inicialização imediata de arquivos. A inicialização imediata agiliza as alocações apenas para arquivos de dados. Ela não se aplica a arquivos de log.
  • Se houver muitas operações de aumento nos arquivos de log, talvez você tenha uma quantidade excessiva de arquivos de log virtuais (VLF). Isso pode levar a problemas de desempenho na inicialização do banco de dados ou na replicação, no espelhamento, no CDC (Change Data Capture) e nas operações online. Além disso, às vezes isso pode causar problemas de desempenho na modificação de dados.

Práticas recomendadas

  • Para sistemas de produção gerenciada, considere autogrow uma mera contingência do crescimento inesperado. Não use autogrow para gerenciar o crescimento diário de dados e de log.
  • Você pode usar programas de monitoramento ou alertas para controlar o tamanho dos arquivos e aumentar arquivos de forma pró-ativa. Isso ajuda a evitar a fragmentação e permite transferir essas atividades de manutenção para horários que não sejam de pico.
  • AutoShrink e autogrow devem ser cuidadosamente avaliados por um Administrador de Banco de Dados (DBA) treinado; eles não devem ficar sem gerenciamento.
  • O incremento autogrow deve ser grande o suficiente para evitar as penalidades de desempenho listadas na seção anterior. O valor exato a ser usado na definição de configuração e a seleção do aumento de uma porcentagem ou do aumento de um tamanho específico em MB depende de muitos fatores do ambiente. Um método confiável usado para testes é definir a configuração autogrow como aproximadamente um oitavo do tamanho do arquivo.
  • Ative a configuração <MAXSIZE> de todos os arquivos para impedir que eles cresçam demais, a ponto de usar todo o espaço em disco disponível.
  • Mantenha as transações com o menor tamanho possível para evitar o crescimento não planejado de arquivos.

Por que preciso me preocupar com espaço em disco se as configurações de tamanho são controladas automaticamente?

  • A configuração autogrow não pode aumentar o tamanho do banco de dados além dos limites do espaço em disco disponível nas unidades para as quais os arquivos são definidos. Portanto, mesmo que use a funcionalidade autogrow para dimensionar seus bancos de dados, você deve verificar independentemente o espaço em disco rígido disponível. A configuração autogrow também é limitada pelo parâmetro MAXSIZE selecionado para cada arquivo. Para reduzir a possibilidade de ficar sem espaço, você pode monitorar o contador do Monitor de Desempenho SQL Server: Objeto de Banco de Dados: Tamanho dos Arquivos de Dados (KB) e configurar um alerta para quando o banco de dados atingir um determinado tamanho.
  • O crescimento não planejado de arquivos de dados ou de log pode ocupar um espaço que deveria estar disponível para outros aplicativos, o que pode causar problemas para esses aplicativos.
  • O incremento de crescimento do log de transações deve ser grande o suficiente para suprir as necessidades das unidades de transação. Mesmo com autogrow ativado, você poderá receber uma mensagem informando que o log de transações está cheio, caso ele não consiga crescer de modo suficientemente rápido para satisfazer às necessidades de sua consulta.
  • O SQL Server não realiza testes constantes para detectar bancos de dados que tenham atingido o limite configurado para autoshrink. Em vez disso, ele examina os bancos de dados disponíveis e localiza o primeiro configurado para autoshrink. Ele verifica esse banco de dados e o reduz, se necessário. Em seguida, ele aguarda alguns minutos antes de verificar o próximo banco de dados configurado para autoshrink. Em outras palavras, o SQL Server não verifica todos os bancos de dados de uma só vez, mas reduz todos ao mesmo tempo. Os bancos de dados são processados em rodízio (round robin) para que seja possível organizar a carga ao longo de um período. Portanto, dependendo do número de bancos de dados de uma determinada instância do SQL Server configurados para autoshrink, o processo que ocorre entre momento em que o banco de dados atinge o limite e a redução efetiva desse banco de dados poderá demorar várias horas.

Referências

Para obter mais informações sobre como expandir e reduzir bancos de dados e arquivos de log, clique nos números abaixo para ler os artigos na Base de Dados de Conhecimento Microsoft (os sites podem estar em inglês):
256650 Como reduzir o log de transações do SQL Server 7.0
272318 Reduzindo o log de transações no SQL Server 2000 com o DBCC SHRINKFILE
317375 Um log de transações aumenta de forma inesperada ou fica cheio em um computador que está executando o SQL Server
247751 BUG: O plano de manutenção de banco de dados não reduz o banco de dados
305635 O tempo limite se esgota quando um banco de dados está sendo expandido automaticamente
949523 A latência de uma replicação transacional é alta no SQL Server 2005 quando os valores da propriedade "Tamanho Inicial" e da propriedade Autogrowth são pequenos
Para obter mais informações sobre a inicialização de arquivos de bancos de dados, visite o seguinte site do MSDN:
Inicialização de arquivos de bancos de dados
Para obter mais informações sobre como habilitar a inicialização instantânea de arquivos, visite o seguinte site da Microsoft (o site pode estar em inglês):
Como e por que habilitar a inicialização instantânea de arquivos
Para obter mais informações sobre a arquitetura física do log de transações, visite o seguinte site do MSDN (o site pode estar em inglês):
Arquitetura física do log de transações

Manuais online do SQL Server; tópicos: "Arquitetura física do log de transações"; "Reduzindo o log de transações"

Propriedades

ID do artigo: 315512 - Última revisão: sábado, 17 de setembro de 2011 - Revisão: 3.0
A informação contida neste artigo aplica-se a:
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Palavras-chave: 
kbsqlmanagementtools kbinfo KB315512

Submeter comentários

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com