SQL Server: The transaction log for database ‘tempdb’ is full

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

O erro acima é apresentado em todas as conexões e ninguém mais consegue conectar no banco para executar alguma ação.

Prováveis causas:

Muitos DBAs acham que a tempdb somente é usada para as #TEMP. Geralmente isso ocore, porém, é muito fácil encher a tempdb sem fazer uso de #temp tables. As seguintes situações podem usar espaço da tempdb:

• Qualquer ordenação que precise de mais memória do que o que estiver alocado para o SQL Server irá forçar o trabalho no tempdb;

• Se a ordenação necessitar de mais espaço do que estiver disponível  na tempdb este erro também irá ocorrer;

• DBCC CheckDB(‘DB’) será executado na tempdb;

• DBCC DBREINDEX ou comandos DBCC similares com a opção ‘Sort in tempdb’ provavelmente irá preencher a tempdb;

• Grandes result sets  envolvendo unions, order by / group by, produto cartesiano, outer join, cursors, temp tables, temp variables a hashing podem gazer iso da tempdb;

• Qualquer transação ‘não comitada’ e que não tenha rollback pode deixar segmentos `orfãos` na tempdb;

• Caso faça faça uso de conexão ODBC com a opção ‘Create Temporary Stored Procedures’ mantém objectos na tempdb durante toda a ‘vida’ da conexão.

O que deve-se verificar ?

Utilize as seguintes consultas para poder identificar as causas e as soluções possíveis:

USE tempdb 
GO 
EXEC sp_spaceused

USE tempdb 
GO 
 
SELECT name 
    FROM tempdb..sysobjects 
 
SELECT OBJECT_NAME(id), rowcnt 
    FROM tempdb..sysindexes 
    WHERE OBJECT_NAME(id) LIKE '#%' 
    ORDER BY rowcnt DESC

Como resolver o erro sem parar a base ?

Achei duas soluções possível e dependendo de cada caso uma destas duas podem dar certo ou não. A primeira é criar um tempdb log adicional e após isso executar o shrink:

ALTER DATABASE Tempdb
ADD LOG FILE
( NAME = tempdblog2,
FILENAME = 'E:\MSSQL.1\MSSQL\DATA\tempdblog2.ldf',
SIZE = 10MB,
MAXSIZE = 100MB,
FILEGROWTH = 10MB)

dbcc shrinkdb ('TEMPDB');

A outra opção é apenas executar o shrink direto no tempdb, logo após um truncate_only no log do tempdb:

USE tempdb 
GO 
BACKUP LOG tempdb WITH TRUNCATE_ONLY 
GO
DBCC SHRINKDB (‘TEMPDB’)
GO
exec sp_helpfile

O que fazer para este erro não mais ocorrer ?

1) Deixe os tempdbs unlimited (é claro que para fazer isso vai depender  do espaço no disco onde o mesmo esteja criado);

2) Não use ODBC para conectar nos bancos;

3) Configure os tempdbs corretamente seguindo as melhores práticas;

4) Deixe o tempdb no modo de recovery simple;

5) Corrija seus códigos de modo que os mesmos não contenham partes de transação sem commit.

Dúvidas? silverio@siltechconsult.com.br