Nota
O acesso a esta página requer autorização. Pode tentar iniciar sessão ou alterar os diretórios.
O acesso a esta página requer autorização. Pode tentar alterar os diretórios.
Versão original do produto: SQL Server
Número original do KB: 315512
Resumo
As configurações de crescimento automático e de redução automática controlam como SQL Server aumenta automaticamente (crescimento automático) ou reduz (autoshrink) o tamanho dos dados do banco de dados e dos arquivos de log. As configurações padrão são apropriadas em muitos sistemas de SQL Server, mas alguns ambientes exigem que você as ajuste. Este artigo explica como essas configurações afetam o desempenho e fornece práticas recomendadas para ajudá-lo a evitar transações lentas, fragmentação de arquivo e uso de espaço em disco não planejado.
Este artigo destina-se aos administradores de banco de dados (DBAs) que configuram o crescimento e o comportamento de redução de arquivos para bancos de dados em SQL Server.
Como definir as configurações de expansão automática e redução automática?
Defina ou modifique as configurações de crescimento automático e de redução automática usando um dos seguintes métodos:
Uma declaração
ALTER DATABASE:- Use as opções Arquivo e Grupo de Arquivos para modificar as configurações de crescimento automático (
FILEGROWTH). - Use as opções SET para definir a
AUTO_SHRINKconfiguração.
- Use as opções Arquivo e Grupo de Arquivos para modificar as configurações de crescimento automático (
Observação
Para obter mais informações sobre como definir essas configurações no nível do arquivo de banco de dados, consulte Adicionar Dados ou Arquivos de Log a um Banco de Dados.
Você também pode configurar a opção de crescimento automático ao criar um banco de dados.
Para exibir as configurações atuais, execute o seguinte comando Transact-SQL:
sp_helpdb [ [ @dbname= ] 'name' ]Você também pode consultar a exibição de catálogo sys.database_files para o banco de dados atual ou a exibição de catálogo sys.master_files para todos os bancos de dados na instância. Essas exibições incluem o incremento de crescimento, se o crescimento é um percentual e o tamanho máximo do arquivo. Para verificar se
AUTO_SHRINKestá ativado, consulte ais_auto_shrink_oncoluna em sys.databases.Lembre-se de que as configurações de crescimento automático são por arquivo. Portanto, você deve defini-los 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 você tiver vários arquivos de dados e de log, deverá definir as opções em cada arquivo. Dependendo do seu ambiente, você pode acabar com configurações diferentes para cada arquivo de banco de dados.
Quais são as configurações de crescimento automático padrão?
No SQL Server 2016 (13.x) e versões posteriores, o valor padrão FILEGROWTH é de 64 MB para arquivos de dados e de log. As versões anteriores usam padrões menores ou baseados em porcentagem. Para o padrão de cada versão, consulte ALTER DATABASE (Transact-SQL) opções de Arquivo e Grupo de Arquivos.
O que devo considerar antes de ativar AUTO_SHRINK?
AUTO_SHRINK é uma opção de banco de dados no SQL Server. Quando você habilita essa opção para um banco de dados, uma tarefa em segundo plano pode reduzir o tamanho do banco de dados. Essa tarefa em segundo plano avalia todos os bancos de dados que atendem aos critérios de redução e, em seguida, reduz os arquivos de dados ou de log.
Avalie cuidadosamente a definição dessa opção para os bancos de dados em uma instância do SQL Server. Operações frequentes de crescimento e redução podem levar a vários problemas de desempenho:
Se vários bancos de dados passarem por operações frequentes de redução e crescimento, a fragmentação do sistema de arquivos poderá ocorrer. A fragmentação pode afetar severamente o desempenho. Esse impacto ocorre quer você use as configurações automáticas, quer aumente e diminua manualmente o tamanho dos arquivos com frequência.
Depois de
AUTO_SHRINKreduzir com êxito o arquivo de dados ou de log, uma operação DML ou DDL subsequente pode ficar significativamente mais lenta se o espaço for necessário e os arquivos precisarem crescer.A
AUTO_SHRINKtarefa em segundo plano pode usar recursos significativos quando muitos bancos de dados precisam ser compactados.A
AUTO_SHRINKtarefa em segundo plano precisa adquirir bloqueios e outros objetos de sincronização que possam entrar em conflito com a atividade regular do aplicativo.
Em vez disso, defina bancos de dados com o tamanho necessário e pré-expanda-os. Deixe o espaço não utilizado nos arquivos de banco de dados se você achar que os padrões de uso do aplicativo precisam dele novamente. Para obter mais informações sobre como reduzir arquivos, consulte Shrink a database e DBCC SHRINKFILE.
O que devo considerar antes de depender do autogrow?
O crescimento automático pode afetar a duração da transação, outras sessões e o número de VLFs (arquivos de log virtual) no log de transações:
Se você executar uma transação que exija mais espaço de log do que está disponível e o crescimento automático estiver ativado para o log de transações desse banco de dados, o tempo necessário para a conclusão da transação inclui o tempo necessário para que o log de transações cresça pelo valor configurado. Se o incremento de crescimento for grande ou outro fator fizer com que o crescimento leve muito tempo, a consulta na qual você abre a transação poderá falhar devido a um erro de tempo limite. O mesmo problema pode resultar de um crescimento automático da parte de dados do banco de dados.
Se você executar uma transação grande que exija que o log cresça, outras transações que exigem uma gravação no log de transações também precisarão aguardar até que a operação de crescimento seja concluída.
Se você tiver muitos crescimentos de arquivos em seus arquivos de log, poderá ter um número excessivamente grande de VLFs (arquivos de log virtuais). Muitas VLFs podem degradar a inicialização do banco de dados, as operações online e outros recursos dependentes de log. Para verificar o número de VLFs, consulte a função de gerenciamento dinâmico sys.dm_db_log_info.
Se você aumentar seu banco de dados em pequenos incrementos, ou se você aumentá-lo e depois reduzi-lo, isso pode resultar em fragmentação de disco. A fragmentação de disco pode causar problemas de desempenho em algumas circunstâncias. Pequenos incrementos de crescimento também podem reduzir o desempenho do seu sistema.
Observação
Se você combinar as opções de crescimento automático e redução automática, poderá criar uma sobrecarga desnecessária. Verifique se os limites que disparam as operações de aumento e redução não causam alterações frequentes de tamanho para cima e para baixo. Por exemplo, você pode executar uma transação que faz com que o log de transações cresça 100 MB no momento em que ele for confirmado. Algum tempo depois disso, a redução automática é iniciada e reduz o log de transações em 100 MB. Em seguida, você executa a mesma transação e faz com que o log de transações cresça 100 MB novamente. Nesse exemplo, você está criando sobrecarga desnecessária e potencialmente criando fragmentação do arquivo de log, o que pode afetar negativamente o desempenho.
Como a inicialização instantânea de arquivo afeta o crescimento automático?
A IFI (inicialização instantânea de arquivo) acelera as operações de crescimento automático de arquivo de dados porque SQL Server não zera o novo espaço. Em SQL Server 2022 (16.x) e versões posteriores, eventos de crescimento automático de log de transações de até 64 MB também podem se beneficiar da IFI. Sem IFI, o crescimento automático leva mais tempo à medida que o incremento de crescimento aumenta. Para obter os requisitos e as limitações, consulte inicialização instantânea de arquivo do banco de dados.
Quais são as melhores práticas para o crescimento automático e a redução automática?
Para um sistema de produção gerenciado, considere o autogrow como apenas uma contingência para um crescimento inesperado. Não gerencie seus dados e o crescimento do log no dia-a-dia usando o crescimento automático.
Use alertas ou programas de monitoramento para monitorar tamanhos de arquivo e aumentar os arquivos proativamente. Essa abordagem ajuda você a evitar a fragmentação e permite que você mude essas atividades de manutenção para horários que não sejam de pico.
Tenha um DBA (administrador de banco de dados) treinado para avaliar cuidadosamente a redução automática e o crescimento automático. Não os deixe sem gerenciamento.
Torne o incremento de crescimento automático grande o suficiente para evitar as penalidades de desempenho descritas anteriormente neste artigo. O valor exato a ser usado na configuração e a escolha entre um crescimento percentual e um crescimento em um tamanho específico em MB depende de muitos fatores em seu ambiente. Uma regra geral que você pode usar para teste é definir a configuração de crescimento automático como cerca de um oitavo do tamanho do arquivo. Para obter recomendações de crescimento do log de transações, consulte Gerenciar o tamanho do arquivo de log de transações.
Ative a configuração
MAXSIZEpara cada arquivo para evitar que qualquer arquivo cresça a ponto de usar todo o espaço em disco disponível.Mantenha o tamanho de suas transações o menor possível para evitar o crescimento não planejado de arquivos.
Por que ainda preciso monitorar o espaço em disco quando o crescimento automático está ativado?
A configuração de crescimento automático 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, se você confiar na funcionalidade de crescimento automático para dimensionar seus bancos de dados, ainda deverá verificar independentemente o espaço disponível no disco rígido. A configuração de crescimento automático também é limitada pelo
MAXSIZEparâmetro selecionado para cada arquivo. Para reduzir a possibilidade de ficar sem espaço, você pode monitorar o contador do Monitor de Desempenho SQL Server: Databases Object: Data File(s) Size (KB) e configurar um alerta quando o banco de dados atingir um determinado tamanho.O crescimento não planejado de dados ou arquivos de log pode ocupar espaço que outros aplicativos esperam estar disponível e pode causar problemas a esses outros aplicativos.
O incremento de crescimento do log de transações deve ser grande o suficiente para ficar à frente das necessidades de suas transações. Mesmo com o crescimento automático ativado, você pode receber uma mensagem informando que o log de transações está cheio se ele não conseguir crescer rápido o suficiente para atender às necessidades da consulta. Para obter mais informações, consulte Solucionar problemas de um log de transações completo (SQL Server erro 9002).
Com que frequência o SQL Server verifica os bancos de dados para redução automática?
O SQL Server não testa constantemente os bancos de dados que atingiram o limite configurado para redução automática. Em vez disso, ele examina os bancos de dados disponíveis e encontra o primeiro configurado para redução automática. Verifica esse banco de dados e o compacta, se necessário. Em seguida, ele aguarda vários minutos antes de verificar o próximo banco de dados configurado para redução automática.
Em outras palavras, o SQL Server não verifica todos os bancos de dados de uma só vez e reduz o tamanho deles de uma só vez. Ele percorre os bancos de dados em esquema round-robin para escalonar a carga durante um período de tempo. Portanto, dependendo de quantos bancos de dados você configurou para redução automática em uma instância específica do SQL Server, pode levar várias horas desde o momento em que o banco de dados atinge o limite até que ele realmente seja reduzido.