Automatizando a verificação de integridade de seus bancos de dados

Para quem administra bancos de dados uma das atividades mais importantes é a verificação da integridade. O problema é que dependendo do número de servidores e/ou de bancos de dados esse trabalho pode tomar um bom tempo do administrador e, como nós sabemos, tempo não é lá uma coisa que sobre no dia a dia de um DBA.

Sendo assim uma boa rotina automatizada de verificação de integridade dos bancos de dados pode ser bastante útil no dia a dia.

Um primeiro ponto em uma rotina desse tipo é ter uma tabela onde possamos armazenar os resultados das verificações para que possamos sempre consultá-la e ter um histórico dos problemas encontrados.

Coloco abaixo um modelo de como poderia ser essa tabela, baseada no retorno do DBCC CHECKDB, com algumas modificações

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CheckDBResult](
 [ServerName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_CheckDBResult_ServerName] DEFAULT (@@SERVERNAME),
 [Database] AS DB_NAME(DBID),
 [Error] [int] NULL,
 [Level] [int] NULL,
 [State] [int] NULL,
 [MessageText] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [RepairLevel] [int] NULL,
 [Status] [int] NULL,
 [DbId] [int] NULL,
 [Id] [int] NULL,
 [IndId] [int] NULL,
 [PartitionId] [int] NULL,
 [AllocUnitId] [int] NULL,
 [File] [int] NULL,
 [Page] [int] NULL,
 [Slot] [int] NULL,
 [RefFile] [int] NULL,
 [RefPage] [int] NULL,
 [RefSlot] [int] NULL,
 [Allocation] [int] NULL,
 [insert_date] [datetime] NOT NULL CONSTRAINT[DF_CheckDBResult_insert_date]  DEFAULT(getdate())
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Agora precisamos montar uma rotina que alimente essa tabela com os resultados da verificação da integridade, que provavelmente será agendada dentro de um Job.

Aqui valem algumas considerações que utilizei na minha rotina

1) Não irei verificar os bancos de dados temporários, inclusive do Reporting Services.

2) Caso não haja falha, armazenarei apenas o registro com o nome do banco e horário da execução.

3) Em caso de falha, irei armazenar todo resultado do DBCC CHECKDB, utilizando a cláusula WITH TABLERESULTS para que possamos manipular melhor os dados retornados.

4) Vou ignorar database snapshots

5) Só irei analisar as bases que estão ONLINE

6) Ao final da execução desejo que seja exibido o resultado do DBCC

7) A rotina deve possibilitar a execução para um banco de dados específico ou para todos os bancos de dados do servidor

Agora que tenho os pré-requisitos definidos (e isso é um exemplo de uma situação, você pode, e deve, muito bem alterar para a sua realidade) podemos avançar para o script em si.

CREATE PROCEDURE [dbo].[_sp_CHECKDB](
	 @Database SYSNAME
)
AS
BEGIN

IF @Database IS NULL  --EXECUTAR PARA TODOS OS BANCOS DE DADOS
BEGIN

	CREATE TABLE #COMANDOS(
		ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
		COMANDO VARCHAR(4000) NOT NULL,
		BANCO SYSNAME NOT NULL
	)

	INSERT INTO #COMANDOS
	(COMANDO,BANCO)
	SELECT
	'INSERT INTO [dbo].[CheckDBResult](
			Error,
			[Level],
			[State],
			MessageText,
			RepairLevel,
			[Status],
			[DbId],
			Id,
			IndId,
			PartitionId,             -- specific to SQL Server 2005, remove for SQL Server 2000 results
			AllocUnitId,             -- specific to SQL Server 2005, remove for SQL Server 2000 results
			[File],
			Page,
			Slot,
			RefFile,
			RefPage,
			RefSlot,
			Allocation
		  )
	DBCC CHECKDB ([' + NAME + ']) WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLERESULTS;',
	NAME
	FROM SYS.DATABASES
	WHERE NAME NOT IN('TempDB','ReportServerTempDB')
	AND SOURCE_DATABASE_ID IS NULL  --NÃO VERIFICAR SNAPSHOTS, APENAS DATABASES
	AND STATE_DESC = 'ONLINE'

	DECLARE @CONTADOR INT, @QUANTIDADE INT, @DB SYSNAME, @SQL NVARCHAR(4000)
	SELECT @CONTADOR = 1, @QUANTIDADE = COUNT(*)
	FROM #COMANDOS

	WHILE @CONTADOR <= @QUANTIDADE
	BEGIN

	SELECT @DB = BANCO
	FROM #COMANDOS
	WHERE ID = @CONTADOR

		INSERT INTO [dbo].[CheckDBResult](
			Error,
			[Level],
			[State],
			MessageText,
			RepairLevel,
			[Status],
			[DbId],
			Id,
			IndId,
			PartitionId,             -- specific to SQL Server 2005, remove for SQL Server 2000 results
			AllocUnitId,             -- specific to SQL Server 2005, remove for SQL Server 2000 results
			[File],
			Page,
			Slot,
			RefFile,
			RefPage,
			RefSlot,
			Allocation
		  )
		EXEC('DBCC CHECKDB(' + @DB + ') WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLERESULTS;')

		IF @@ROWCOUNT = 0
		BEGIN
			INSERT INTO [dbo].[CheckDBResult]
			([DBID])
			SELECT DB_ID(@DB)
		END

	SET @CONTADOR = @CONTADOR + 1;

	END

	SET @SQL = 'SELECT TOP ' + CAST(@QUANTIDADE AS NVARCHAR) + ' * FROM [dbo].[CheckDBResult] ORDER BY Insert_Date DESC'
	EXEC SP_EXECUTESQL @SQL

	DROP TABLE #COMANDOS

END
ELSE  --EXECUTAR APENAS PARA UM BANCO DE DADOS ESPECÍFICO PASSADO COMO PARÂMETRO
BEGIN
	IF EXISTS(
				SELECT 1
				FROM SYS.DATABASES
				WHERE NAME = @Database
				AND SOURCE_DATABASE_ID IS NULL  --NÃO VERIFICAR SNAPSHOTS, APENAS DATABASES
				AND STATE_DESC = 'ONLINE'
				AND NAME NOT IN('TempDB','ReportServerTempDB')
			)
	BEGIN

		INSERT INTO [dbo].[CheckDBResult](
			Error,
			[Level],
			[State],
			MessageText,
			RepairLevel,
			[Status],
			[DbId],
			Id,
			IndId,
			PartitionId,             -- specific to SQL Server 2005, remove for SQL Server 2000 results
			AllocUnitId,             -- specific to SQL Server 2005, remove for SQL Server 2000 results
			[File],
			Page,
			Slot,
			RefFile,
			RefPage,
			RefSlot,
			Allocation
		  )
		EXEC('DBCC CHECKDB(' + @Database + ') WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLERESULTS;')

		IF @@ROWCOUNT = 0
		BEGIN
			INSERT INTO [dbo].[CheckDBResult]
			([DBID])
			SELECT DB_ID(@Database)
		END

		SELECT TOP 1 *
		FROM [dbo].[CheckDBResult]
		ORDER BY Insert_Date DESC

	END
	ELSE
	BEGIN
		RAISERROR(N'BANCO DE DADOS INVÁLIDO!',16,1)
	END
END

END

Veja que adicionei ao resultado do DBCC CHECKDB o nome do servidor onde a rotina foi executada, o nome do banco de dados avaliado e o horário de execução. Veja que para a coluna do nome do banco de dados, utilizei um campo calculado.

Veja que agora para executar a procedure você tem duas opções:

1) Executar para um banco de dados específico

EXEC [_sp_CHECKDB] 'MSDB'

2) Executar para todos os bancos de dados

EXEC [_sp_CHECKDB] NULL

E ainda pode analisar todos os dados históricos presentes na tabela de resultados:

SELECT * FROM [CheckDBResult]

Essa rotina poderia então ser colocada dentro de um job e você pode facilmente alterar o formato da mesma para que envie um e-mail para a equipe de DBAs, por exemplo.

Além desse recurso lembre que você pode sempre consultar a tabela de páginas suspeitas:

SELECT *
FROM MSDB.DBO.SUSPECT_PAGES

Aproveitando o assunto, lembre que em caso de corrupção você pode utilizar o recurso de PAGE RESTORE do SQL Server (desde que tenha a sku ENTERPRISE). Caso não tenha a sku ENTERPRISE, há outras formas para lidar com a situação também.

E nesse ponto o SQL Server 2012 (fka SQL Denali) terá uma novidade, pois haverá uma interface gráfica no SSMS para realizar tal operação.

Para quem quiser mais informações vale a pena uma olhada nos links abaixo:

Outros links que consultei para o artigo:

Esse post foi publicado em Artigos, DBCC, Virtual PASS BR. Bookmark o link permanente.

Uma resposta para Automatizando a verificação de integridade de seus bancos de dados

  1. Pingback: Análise de um banco de dados corrompido | Alex Souza

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