Azure SQL Database – Quanto posso usar de Transaction Log? É ilimitado? – Fabricio Lima

Azure SQL Database – Quanto posso usar de Transaction Log? É ilimitado?

17 de julho de 201817 de julho de 2018Fabrício LimaAzure SQL Database, Managed Instance, SQL Azure, Virtual PASS BR

Fala Pessoal,

Continuando os posts sobre Azure SQL Database, dessa vez vamos falar sobre um erro de Log Full que consegui gerar em uma base no Azure.

O Transaction Log de uma base no azure funciona da mesma forma que em uma instância On-Premise.

Como no azure não conseguimos utilizar uma base com o recovery SIMPLE, toda base tem o recovery FULL, ou seja, nossas operações de INSERT/UPDATE/DELETE ficam no transaction log até que o backup do log salve essas informações e permita que elas sejam liberadas do log.

Nada diferente do que temos hoje no On-Premise.

Um dos problemas que temos hoje é estourar o log com REBUILD ou quando alguém deixa uma transação aberta. Aquele famoso BEGIN TRAN que o desenvolvedor (exemplo meramente ilustrativo) deixou aberto e foi almoçar.

Quando contratamos uma base no Azure, não é definido qual o tamanho do transaction log dessa base.

Por exemplo, nos meus testes eu contratei a base na versão mais barata com 5 DTUs e 2 GB máximo de tamanho de base.

E tamanho do Transaction log? É Ilimitado?

Para tentar pegar essas respostas fiz um teste com os scripts abaixo:

--Cria uma tabela de teste
CREATE TABLE [dbo].[Teste_TransactionLog](
	[Col] [VARCHAR](8000) NULL
) ON [PRIMARY]
GO

--Popula essa tabela
insert into Teste_TransactionLog select replicate ( 'b',8000)
GO 100

INSERT into Teste_TransactionLog
SELECT * FROM dbo.Teste_TransactionLog
GO 4

--Cria uma tabela de teste

CREATE TABLE [dbo].[Teste_TransactionLog](

[Col] [VARCHAR](8000) NULL

) ON [PRIMARY]

--Popula essa tabela

insert into Teste_TransactionLog select replicate ( 'b',8000)

GO 100

INSERT into Teste_TransactionLog

SELECT * FROM dbo.Teste_TransactionLog

GO 4

A tabela ficou bem pequena com apenas 1600 linhas e ocupando 12 MB:

EXEC sp_spaceused Teste_TransactionLog

name                  rows                 reserved           
Teste_TransactionLog   1600                 12808 KB

EXEC sp_spaceused Teste_TransactionLog

name rows reserved

Teste_TransactionLog 1600 12808 KB

Mantendo essa tabela com esse tamanho, abri uma transação e deixei um loop sendo executado realizando um update. Esse update gera alterações que são gravadas no transaction log, que passa a crescer:

SET NOCOUNT ON

--deixa a transação aberta
BEGIN TRAN

--coloca o update para rodar em loop
while 1=1
BEGIN 
	 update Teste_TransactionLog set Col = REPLICATE( SUBSTRING('abcdefghijlmnopqrstuvxz',(CAST((RAND()*5465465) AS INT)%20),1),8000)
end
GO

SET NOCOUNT ON

--deixa a transação aberta

BEGIN TRAN

--coloca o update para rodar em loop

while 1=1

BEGIN

update Teste_TransactionLog set Col = REPLICATE( SUBSTRING('abcdefghijlmnopqrstuvxz',(CAST((RAND()*5465465) AS INT)%20),1),8000)

end

Como minha base tem 5 DTU, ela não consegue processar muita coisa. Deixei o update rodando, fui resolver outras coisas e quando voltei, lá estava a famosa mensagem de Transaction log is full:

Opa…

Então quer dizer que o Transaction Log do Azure não é Ilimitado?

Resposta: Não.

O Transaction Log da minha database que tem um tamanho de 2 GB chegou a aproximadamente 5 GB de tamanho:

Eu entrei em contato com o Xiaochen Wu (Senior Program Manager do time de Azure SQL Database), mostrei esses testes e perguntei se existia alguma regra ou fórmula que determinasse o tamanho máximo de um T-Log no Azure.

A resposta dele foi a seguinte:

“Não existe nenhuma fórmula fixa para determinar o tamanho do Transaction Log. Isso não está documentado porque nós mudamos esses valores dinamicamente de forma frequente para alcançar o melhor caso para a maioria dos clientes. O tamanho do log é determinado pelo volume computacional e não pelo tamanho da database. Podemos ter uma base bem pequena que gere muito log.

Nós raramente vemos um cliente com problema de Log Full no Azure SQL. Na maioria dos casos o usuário fez um rebuild em uma tabela muito grande ou deixou uma transação aberta. Para o REBUILD nós liberamos o ONLINE INDEX REBUILD que não vai bloquear a Limpeza do Log. Para o caso da transação aberta, ela vai morrer em algum momento, dar rollback e liberar o T-Log.”

Perguntei se podia compartilhar isso com vocês e disse que sim. Fica aqui meu agradecimento ao Xiaochen Wu pelo tempo em responder minhas dúvidas.

Resumindo, o transaction log não é infinito e não tem uma fórmula mágica para sabermos até quanto o T-Log da nossa base pode alcançar.

Fica a dica de quando rodar um REBUILD dos índices no Azure, utilizar a opção ONLINE (post futuro) e nunca deixar transações abertas na base e ir almoçar. =)

O Azure não tem um alerta no portal para monitorar o Transaction Log, mas podemos criar um para salvar essa informação em uma tabela e depois via alguma aplicação ler essa tabela e enviar um e-mail. Entretanto, isso já é assunto para outro post futuro.

Espero que tenham aprendido algo novo.

Até a próxima.

Posts relacionados sobre o Azure SQL Database:

Gostou da dica?

Curta, comente, compartilhe com os coleguinhas…

Assine meu canal no Youtube e curta minha Página no Facebook para receber Dicas de Leituras e Eventos sobre SQL Server.

Abraços,

Fabrício Lima

Microsoft Data Platform MVP

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006