Rebuild

Question

Pessoal durante uma tentativa de realizar um rebuild em uma base prototipo recebi o seguinte erro:

Mensagem 9002, Nível 17, Estado 4, Linha 1
The transaction log for database 'PHARMACY-OUR' is full due to 'ACTIVE_TRANSACTION'. 

Analisando vário exemplos na internet encontrei esse exemplo abaixo:

USE [PHARMACY-OUR]

GO

SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);

-- Coleta as informações de fragmentação e traz também os nomes
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

--Armazena em uma tabela temporária
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
OPEN partitions;
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;

-- Ajuste aqui o nível de fragmentação para REORGANIZE ou REBUILD
IF @frag < 30
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
Print 'OK'
IF @frag >= 30
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
--PRINT N'Executed: ' + @command;
END;
CLOSE partitions;
DEALLOCATE partitions;
select * from #work_to_do

-- Apaga a tabela temporária
DROP TABLE #work_to_do;
GO

Alguem saberia me dizer o que ocorreu?

Answer 1

Olá Eder...

O arquivo de log de transações vai ficar cheio em uma das seguintes situações:

Se o arquivo de log foi configurado com limite de tamanho máximo predefinido, em seguida, o arquivo está completo.

Se o arquivo de log foi configurado com tamanho ilimitado, então talvez o disco está cheio.

Aconselho verificar...

---

Não esqueça de marcar o post como útil caso tenha te ajudado.

Answer 2

Eder, boa tarde, concordo com o Roberson, o erro não tem a ver com o rebuild dos indices mas sim com o transaction log, provavelmente sua base esta configurada como recovery mode FULL, veja se isto é realmente necessario.

O que aconselho é que voce faça um backup do seu log de transação, talvez voce tenha que fazer isto mais de uma vez, em sequencia faça um shrink:

dbcc shrinkdatabase ('Nome da sua base', TRUNCATEONLY)

Isto irá reduzir o tamanho do seu arquivo de log.

---

Alexandre Matayosi Conde Mauricio.

Answer 3

Eder,

Minha sugestão, altere o Modelo de Recuperação de Banco de Dados para Simples e depois realize o processo de ShrinkFile e ShrinkDatabase, conforme o exemplo abaixo:

  ALTER DATABASE @NomeBancodeDados
  SET RECOVERY SIMPLE;

  DBCC ShrinkDatabase('NomeBancodeDados',10)

  DBCC ShrinkFile(1,TruncateOnly);

  DBCC ShrinkFile(2,100);
  
  DBCC ShrinkFile(2,TruncateOnly);

Não adianta querer fazer o Shrink do seu bando de dados se o recovery model estiver full, pois o arquivo de log não será impactado.

---

Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]