O que é o Autogrowth?
O Autogrowth é um recurso nativo do SQL Server que realiza o crescimento automático em uma base de dados quando ela atinge o limite de tamanho definido na criação do banco de dados.
Esse recurso é opcional, ou seja, você pode habilitar ou não. O grande problema é que quando o recurso não está habilitado e o seu banco de dados chega ao limite do tamanho, o SQL Server inicia uma série de erros, alegando que não há espaço disponível para crescimento e solicita algum espaço para realizar o crescimento.
O grande problema disso tudo é que existe uma quantidade enorme de ambientes de produção, em diversos locais, no qual o Autogrowth não é configurado corretamente.
Perceba a seguinte situação: Você tem um banco de dados e o autogrowth está configurando para crescimento com o parâmetro de 1MB. Quando seu banco de dados chegar ao limite, ele vai crescer 1MB, só que 1MB, dependendo da quantidade de transações do seu banco de dados, vai acabar rapidamente, então, ele vai atingir ao seu limite de tamanho e novamente vai crescer mais 1MB e assim por diante.
Imagine o SQL Server processando 30 crescimentos em um dia… Se essa quantidade continuar estável, em um mês seu arquivo de dados ou de log poderá ter crescido 900 vezes. Para esse exemplo já é um número alarmante. Afinal, o crescimento exige um recurso de processamento do seu servidor de banco de dados.
Explicado o que é o Autogrowth, agora irei descrever o que ocorreu comigo
Um certo dia, começaram a reclamar de uma aplicação. Quando fomos verificar, identificamos que o banco de dados referente a essa aplicação estava com uma lentidão acima do normal para o simples acesso.
Ao verificarmos os logs do SQL Server, conseguimos identificar inúmeros erros envolvendo o crescimento automático do banco. Decidimos então, investigar o parâmetro inserido no Autogrowth, e advinha só? Estava em 1.500%, isso mesmo, 1.500%. Pense por um momento o que é tentar crescer 1500% de 200GB…. É uma tarefa extremamente custosa para um ambiente de produção no meio do dia.
O processamento referente a esse crescimento, exigiu um custo tão alto para aquele banco de dados, mas tão alto, que nós não conseguíamos alterar o parâmetro ou fazer quase nenhuma outra atividade nesse banco.
Qual foi a solução? O raciocínio levou para um caminho simples, ou seja, se o banco está com uma alta carga de transação e ainda um alto processamento, vamos tentar diminuir. O que fizemos? Colocamos o banco de dados em Single User afim de tentar reduzir a quantidade de transações abertas para este banco de dados e realmente acabou funcionando. Para quem não sabe o que é o Single User, ele garante que apenas um usuário acesse aquele banco de dados.
Uma maneira preventiva de evitar esse tipo de problema é verificar como o Autogrowth dos seus bancos de dados estão parametrizados. Caso contrário, você poderá ter problemas com algum banco de dados ocasionado pelo alto processamento exigido no crescimento, seja para um crescimento grande ou pequeno.
Como apoio na solução, segue um pequeno SELECT que irá disponibilizar informações genéricas de como o Autogrowth dos seus bancos de dados estão configurados.
SELECT rtrim(sd.name) as DBName, rtrim(saf.name) AS FileName,
rtrim(saf.filename) FilePath,
saf.size*1.0/128 AS FileSizeinMB,
CASE saf.maxsize
WHEN 0 THEN 'Autogrowth is off.'
WHEN -1 THEN 'Autogrowth is on.'
ELSE 'Log file will grow to a maximum size of 2 TB.'
END AutogrowthStatus,
saf.growth AS 'GrowthValue','GrowthIncrement' =
CASE
WHEN saf.growth = 0 THEN 'Size is fixed and will not grow.'
WHEN saf.growth > 0 THEN 'Growth value is in 8-KB pages.'
ELSE 'Growth value is a percentage.'
END
FROM master..sysaltfiles saf Inner Join master..sysdatabases sd
On saf.dbid = sd.dbid
Para terminar, referencio também o DBA Anderson Aroucha que passou por essa situação juntamente comigo.
Caso tenha dúvida ou queira complementar o conteúdo, deixe seu comentário abaixo!
2 Comentários
Felipe, primeiramente parabéns pelo artigo, esse assunto é de extrema importância e a maioria deixa de lado essa configuração!
Já passei por um cenário super complexo sobre esse assunto!
Gostaria de deixar algumas recomendações para um troubleshooting antes de realizar alterações desse nível!
Validar no nosso amigo errorlog se existem mensagens relacionadas a suas VLF´s como “Database has more than 1000 virtual log files which is excessive”.
Validar a média de tempo de seus backups e caso possua réplicas validar seus restores também, a trace flag [3004, 3605, -1] pode ajudar nessa análise verificando em qual fase demorou mais tempo.
Validar o tamanho atual do seu Transaction Log pois é importante que o autogrowth não ocorra em disco sempre que for preciso, desta forma, é válido deixa-lo com um tamanho físico ideal, pois, em background antes de realizar o autogrowth no LOG é realizado uma verificação no disco para garantir aonde o crescimento ocorrerá, esse tempo de validação no disco é um dos GAPs não verificados nesses cenários e muitas das vezes a causa raiz do problema!
Outro ponto crucial é a versão do engenho, existem algoritmos para o calculo ideal de VLFs, até a versão SQL2008R2 o cálculo de VLFs na expansão é o seguinte:
Até 64 MB gera-se 4 VLFs.
De 64 MB a 1 GB gera-se 8 VLFs.
Mais de 1 GB gera-se 16 VLFs.
Quanto mais VLFs já geradas em seu LOG menos autogrowth será feito, isso significa menos acesso ao disco para autogrowth.
Existem mais insumos para se chegar a uma conclusão e resolução do problema, mas acredito que essas sejam cruciais!
Mais uma vez, parabéns pelo artigo, forte abraço!
Felipe, existe um tamanho ideal ou recomendado?
Meu banco tem 600GB e o autogrowth está com 250MB.