[Administração] SQL Server Autogrowth

Quando o arquivo de um banco SQL Server atinge o limite do seu tamanho, o recurso autogrowth (auto-crescimento) pode aumentar o tamanho do arquivo automaticamente, isso evita que operações DML e DDL falhem por falta de espaço no arquivo; logo, é uma boa prática manter o autogrowth habilitado. Quando um arquivo chega ao limite do tamanho e o autogrowth não está habilitado, temos uma mensagem de erro como esta:

Could not allocate space for object 'nome_tabela' in database 'nome_banco' because the 'PRIMARY' filegroup is full.
Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Por padrão o recurso autogrowth vem habilitado e seu valor padrão é 10% do tamanho atual do arquivo, portanto, se o arquivo tem 10 MB, o autogrowth será de 1MB; se o arquivo tem 1 TB, o autogrowth será de 102 GB. Durante uma grande operação de autogrowth, poderemos ter a percepção de lentidão no ambiente e veremos mensagens como esta no errorlog:

Autogrow of file 'nome_do_arquivo' in database 'nome_da_base' took 254872 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

Podemos mudar o valor do autogrowth, mas considere que um valor muito baixo pode resultar em inúmeras operações de crescimento; um valor muito alto pode resultar em lentidão (e até mesmo não concluir caso o valor do autogrowth seja maior que o espaço livre no disco). É interessante que o autogrowth seja definido com um valor fixo entre 1 MB e 1GB sendo recomendável evitar o autogrowth com valor percentual.

O ideal é que o DBA calcule a tendência de crescimento de uma base e aloque o espaço estimado em uma janela de manutenção adequada ao ambiente; portanto, se a base cresce 100 GB por mês, aloque 600 GB para a base operar pelos próximos 6 meses e periodicamente avalie se o crescimento está de acordo com a estimativa inicial, dessa forma, o autogrowth será executado somente em situações pontuais.

Como avaliar quando ocorreram as últimas operações de autogrowth em um banco?

No Management Studio temos o relatório "Disk Usage" (propriedades da base > Reports > Standard Reports > Disk Usage) que mostra como está a utilização do espaço em cada arquivo e as operações de autogrowth mais recentes.

Importante lembrar que se o instant file initialization estiver habilitado, o tempo de alocação de espaço em arquivos de dados será quase imperceptível, porém essa vantagem não se aplica a arquivos de log (post com mais detalhes sobre o IFI aqui).

Uma forma de avaliar a configuração do autogrowth dos arquivos é consultando a sys.master_files, exemplo:

  -- arquivos com autogrowth em percentual
select name, physical_name, growth as growth_percent
from sys.master_files
where is_percent_growth=1

Referências adicionais:

Database Instant File Initialization: https://msdn.microsoft.com/en-us/library/ms175935.aspx

sys.master_files (Transact-SQL): https://msdn.microsoft.com/pt-br/library/ms186782.aspx

============================

The code and techniques described in this blog are presented to the reader ‘as is’, without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by any of the authors or Microsoft. Further, the authors shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages. Your use of the information contained in these pages, is at your sole risk.