DBCC CHECKDB with DATA_PURITY

A grande maioria de vocês já deve conhecer o comando DBCC CHECKDB, utilizado para verificar a integridade de bancos de dados.

O que pouca gente conhece são algumas opções que este comando oferece para situações menos comuns. Uma delas é a opção WITH DATA_PURITY. Segue um exemplo do comando:

DBCC CHECKDB [BANCO] WITH DATA_PURITY;

Todo banco de dados criado a partir da versão 2005, por padrão, ao executar o comando DBCC CHECKDB executa a opção DATA_PURITY, que verifica se os valores das colunas são válidos e se estão dentro do range do seu data type. Um exemplo da possível mensagem de erro retornada por esse comando, retirada do site de suporte da Microsoft, segue abaixo:

Resultados do DBCC para “account_history”.
Msg 2570, nível 16, estado 2, linha 1
Página (1:1073), slot 33 no objeto 1977058079 ID, identificação do índice 0, partição 129568478265344 de identificação, 129568478265344 de identificação de unidade de alocação (tipo de “-linha dados”). Valor da coluna “account_name_japan” está fora do intervalo para tipo de dados “nvarchar”. Coluna de atualização para um valor válido.
Msg 2570, nível 16, estado 2, linha 1
Página (1:1156), slot 120 no ID do objeto 1977058079, identificação do índice 0, partição ID 129568478265344, 129568478265344 de identificação de unidade de alocação (tipo de “-linha dados”). Valor da coluna “account_name_japan” está fora do intervalo para tipo de dados “nvarchar”. Coluna de atualização para um valor válido.
Existem 153137 linhas nas páginas 1080 de objeto “account_history”.
CHECKDB encontrado erros de alocação de 0 e erros de consistência 338 na tabela “account_history” (objeto 1977058079 ID).
CHECKDB encontrado erros de alocação de 0 e 338 erros de consistência no banco de dados ‘BadUnicodeData’.
Execução de DBCC foi concluída. Se o DBCC imprimiu mensagens de erro, contate o administrador do sistema.

Você deve estar se perguntando “Quer dizer que o SQL Server permite que eu armazene valores “errados” dentro das colunas das tabelas?”. SIM e NÃO! Até a versão 2000 isso podia acontecer em algumas situações, então é muito importante que ao migrar bancos da versão 2000 para alguma posterior, ou migrar algum banco que foi criado na versão 2000 e migrado para outra versão, mas que não teve essa validação feita que você faça essa validação. Lembro que, caso você utilize a versão 2005 em diante, essa validação só precisa ser feita explicitamente uma vez, pois após isso o SQL Server se encarrega de executá-la por padrão ao executar o DBCC CHECKDB.

Sabendo disso, devemos conhecer uma forma de verificar quais bancos precisam ter a execução do DBCC CHECKDB WITH DATA_PURITY executada e faremos isso com o comando:

DBCC TRACEON (3604);
GO
DBCC DBINFO ('master');
GO
DBCC TRACEOFF (3604);
GO

Com ele teremos o seguinte resultado (parte dele):

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBINFO STRUCTURE:

DBINFO @0x00000000119BEA40

dbi_dbid = 1                         dbi_status = 65544                   dbi_nextid = 39671189
dbi_dbname = master                  dbi_maxDbTimestamp = 4000            dbi_version = 611
dbi_createVersion = 611              dbi_ESVersion = 0
dbi_nextseqnum = 1900-01-01 00:00:00.000                                  dbi_crdate = 1900-01-01 00:00:00.000
dbi_filegeneration = 0
dbi_checkptLSN

m_fSeqNo = 16074                     m_blockOffset = 2320                 m_slotId = 1
dbi_RebuildLogs = 0                  dbi_dbccFlags = 0
dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000
dbi_dbbackupLSN

Veja que destaquei duas informações.

dbi_CreateVersion que informa a versão no qual o database foi criado, onde 611 é o 2005.

dbi_dbccFlags que informa o status da execução do DATA_PURITY que pode ter os seguinte valores:

0: significa que as validações do DATA_PURITY não estão habilitadas por padrão

1: significa que não houve execução completa do DBCC CHECKDB desde que o banco foi criado ou que o log foi reconstruído.

2: significa que as verificações do DATA_PURITY estão habilitadas.

Você pode encontrar também uma explicação sobre isso no blog do Paul Randal.

Sendo assim, sugiro que você analise seus bancos de dados e veja em quais precisa executar esse comando (em uma janela de manutenção, preferencialmente!).

Abaixo coloco um script que fiz para facilitar esse trabalho de análise, já que com o comando que coloquei anteriormente você teria que executá-lo uma vez para cada banco de dados de sua instância.

CREATE TABLE #temp
(
	ID INT IDENTITY(1,1),
	ParentObject VARCHAR(255) ,
	[Object] VARCHAR(255) ,
	Field VARCHAR(255) ,
	Value VARCHAR(255)
);

CREATE TABLE #DBCCResults
(
	Field VARCHAR(255),
	Value VARCHAR(255)
);

EXEC master.dbo.sp_MSforeachdb
@command1 = 'USE ? INSERT INTO #temp EXECUTE (''DBCC DBINFO WITH TABLERESULTS'')',
@command2 = 'INSERT INTO #DBCCResults SELECT field, value FROM #temp WHERE Field IN(''dbi_dbccFlags'',''dbi_createVersion'',''dbi_dbname'') '

DECLARE @qtde int
SELECT @qtde = count(*)
FROM SYS.DATABASES

;with resultado(banco,versao,flag)
as(
select [dbi_dbname],[dbi_createVersion],[dbi_dbccFlags]
from (
	select ntile(@qtde) over(order by id) as ID,field, value
	from #temp
) as tab
pivot(
	max(value)
	for field in([dbi_dbname],[dbi_dbccFlags],[dbi_createVersion])
) as pivottable
)
select banco,versao,flag,
case
	when versao < 611 and flag <> 2 then 'DBCC CHECKDB [' + banco + '] WITH DATA_PURITY'
end as status
from resultado

drop table #temp
drop table #DBCCResults
Esse post foi publicado em Artigos, DBCC, Virtual PASS BR. Bookmark o link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s