Transaction Log cheio? E agora?

For the English version, see below!

Pessoal,

Um problema que aconteceu comigo esses dias e gostaria de compartilhar com vocês. Em uma manha recebi a seguinte mensagem:
Msg 9002, Level 17, State 2, Line 3
The transaction log for database ‘MCITP’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Como o próprio erro diz:  O Log de transações da base de dados MCITP esta cheio e não poderá ser reutilizado.
A causa do problema pode ser:

1)  O arquivo de log foi configurado com um tamanho maximo e com isso o arquivo chegou em seu limite maximo e nao conseguiu mais crescer.

2) O arquivo de log nao tem um tamanho maximo, porem nao existe mais espaço em disco.

Como podemos resolver esse problema.

1) Onde esta o arquivo do log e qual seu tamanho maximo, qual o tamanho atual e a proporção de crescimento?
R: select msf.name,physical_name, db.recovery_model_desc,(max_size * 8 /1024) as MaxSizeMB,
(size * 8 /1024) as SizeMB, growth
from sys.master_files as msf
join sys.databases as db
on msf.database_id = db.database_id
where msf.name = ‘MCITP_LOG’


Temos o caminho.

2) Vamos analisar o espaço em disco?
R: exec master..xp_fixeddrives

C	5064
D	6060

Ai ja conseguimos ver qual é o problema. No nosso caso aqui temos um problema é o arquivo de log configurado um tamanho maximo, como o fator de crescimento esta para 20%, o proximo tamanho que o arquivo de log teria seria de 258MB o que ultrapassaria o tamanho maximo de 220MB, por isso o SQL Server nao conseguiu mais crescer o arquivo de log.

Solução.

Aqui temos algumas alternativas.

1) Realizar um backup desse log, e sempre cuidar para manter o backup do log sendo realizado periodicamente.

2) Caso a base de dados nao for importante, realize primeiro um backup desse log e depois passe o modo de recovery para SIMPLE, assim periodicamente o SQL Server se encarrega de manter esse arquivo pequeno, ja que pouquissimas informações serão logadas.

3) Isso nao seria uma alternativa viavel, mas poderiamos ainda realizar um shrink no log, mas eu nao recomendo isso de maneira nenhuma. Se você chega ao ponto de truncar o log, o modo de recovery FULL com certeza não é necessario para você.

4) Você pode verificar também se sua base de dados realmente está no Full Recovery Model e garantir que o transaction log irá se comportar corretamente.

Bom pessoal por hoje é isso, espero que isso seja util para vocês.

Marcos Freccia
MCTS SQL Server 2008
@SQLFreccia

Hi Guys,

Those days a problem happened in one of the databases that I manage and I would like to share a tip with you. This is message
Msg 9002, Level 17, State 2, Line 3
The transaction log for database ‘MCITP’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

As you can see in the error the transaction log is full and cannot be reused. This is a very common error that you can get if you don’t properly setup the database.

The root cause of the problem may be:

1)  The transaction log file was setup with a maximum size and then the file reached it’s limit and then it cannot grow more.

2) The transaction log file has not a maximum size limit and then the disk run out of space.

How can we resolve this issue?

1) Where is it located the transaction log. what is the size of it and what is the growth rate setup.
A: select msf.name,physical_name, db.recovery_model_desc,(max_size * 8 /1024) as MaxSizeMB,
(size * 8 /1024) as SizeMB, growth
from sys.master_files as msf
join sys.databases as db
on msf.database_id = db.database_id
where msf.name = ‘MCITP_LOG’


Now we have something.

2) Let’s take a look in the free space?
R: exec master..xp_fixeddrives

C	5064
D	6060

As you can see. The problem is the transaction log setup has a maximum size and the growth rate is set to 20%, which means that in the next growth the size would be more than the maximum size set and then you get the error.

Solution.

We have a few options

1) Do a Transaction Log Backup, and specifically setup the recurrence of it.

2) In case the database is not that important and you have a good RPO and RTO defined, first do a transaction log backup and then later change the recovery model to SIMPLE. By doing that, SQL Server will still log all operations but the transaction log is truncated after the checkpoint.

4) Or you can validate if the database is really in Full Recovery Model, looking into this blog post sua base de dados realmente está no Full Recovery Model and make sure the database will behavior properly.

I hope you enjoy the tip!

Advertisements

About Marcos Freccia

MVP em SQL Server (Data Plataform) , especialista em SQL Server, e atualmente trabalhando com Microsoft Azure!

Posted on June 13, 2011, in Administração, VirtualPass and tagged , , , , , , . Bookmark the permalink. 6 Comments.

  1. Marcos,

    Comigo esta acontecendo algo semelhante, porem tenho espaço em disco meu arquivo de log esta vazio também, porem continua apresentando a mensagem:

    Msg 9002, Level 17, State 4, Line 1
    The transaction log for database ‘tempdb’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

    Sempre que rodo uma query, query esta que rodava ate hoje de manha.

    existe algo que eu possa fazer?

  1. Pingback: The most read posts of May – 2017 | Freccia's Blog

  2. Pingback: The most read posts of June – 2017 | Freccia's Blog

  3. Pingback: The most read posts of July – 2017 | Freccia's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: