Realizar verificações regulares de integridade do banco de dados é uma tarefa crítica para os administradores de banco de dados. Isso garante a integridade estrutural e a alocação de todos os objetos e índices do banco de dados. No SQL Server, o comando DBCC CheckDB é comumente usado para identificar qualquer corrupção no banco de dados.
No entanto, revisar os logs gerados pelo comando DBCC CheckDB pode ser tedioso, especialmente ao trabalhar com vários bancos de dados. Para simplificar esse processo, criei uma stored procedure SQL que é executada em todos os bancos de dados e envia os detalhes de quaisquer erros de consistência para a equipe de DBA.
Aqui está um exemplo de como você pode automatizar a verificação de consistência de um banco de dados SQL criado no SQL Server Express Edition:
Passo 1: Restaurar o Banco de Dados
Comece restaurando o banco de dados usando o comando RESTORE DATABASE. Por exemplo:
USE [master]
GO
RESTORE DATABASE [DemoCorruptMetadata] FROM DISK = N'C:\SQL Backup\DemoCorruptMetadata2008R2.bak' WITH FILE = 1, MOVE N'DemoCorruptMetadata' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\DemoCorruptMetadata.mdf', MOVE N'DemoCorruptMetadata_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\DemoCorruptMetadata_log.ldf', NOUNLOAD, STATS = 5
Passo 2: Executar o Comando DBCC CheckDB
Após restaurar o banco de dados, execute o comando DBCC CheckDB para visualizar quaisquer erros. Por exemplo:
SET NOCOUNT ON
GO
USE master
GO
DBCC CHECKDB (DemoCorruptMetadata) WITH ALL_ERRMSGS, NO_INFOMSGS
Se o banco de dados estiver corrompido, o comando de verificação de consistência reportará erros.
Passo 3: Criar a Stored Procedure
Crie uma stored procedure que execute a verificação de consistência em todos os bancos de dados SQL e insira quaisquer erros em uma tabela temporária. Aqui está um exemplo da stored procedure:
USE master
GO
CREATE PROCEDURE Sp_db_integrity_check
AS
BEGIN
DECLARE @DBName VARCHAR(150)
DECLARE @dbcccommand NVARCHAR(max)
DECLARE @DBcount INT
DECLARE @i INT = 0
CREATE TABLE #errormsgs (
error INT NULL,
level INT NULL,
state INT NULL,
messagetext VARCHAR(7000) NULL,
repairlevel INT NULL,
status INT NULL,
dbid INT NULL,
dbfragid INT NULL,
objectid INT NULL,
indexid INT NULL,
partitionid INT NULL,
allocunitid INT NULL,
riddbid INT NULL,
ridpruid INT NULL,
[file] INT NULL,
page INT NULL,
slot INT NULL,
refdbid INT NULL,
refpruid INT NULL,
reffile INT NULL,
refpage INT NULL,
refslot INT NULL,
allocation INT NULL
)
CREATE TABLE #userdatabases (NAME VARCHAR(500))
INSERT INTO #userdatabases
SELECT NAME FROM sys.databases WHERE database_id > 4
SET @DBcount = (SELECT COUNT(1) FROM #userdatabases)
WHILE (@DBcount > @i)
BEGIN
SET @DBName = (SELECT TOP 1 NAME FROM #userdatabases)
SET @dbcccommand = 'dbcc checkdb (' + @DBName + ') with no_infomsgs, all_errormsgs, tableresults'
INSERT INTO #errormsgs
EXEC ('dbcc checkdb (' + @DBName + ') with no_infomsgs, all_errormsgs, tableresults')
DELETE FROM #userdatabases WHERE NAME = @DBName
SET @i = @i + 1
END
--select MessageText from #errormsgs
DECLARE @subject NVARCHAR(max)
DECLARE @tableHTML NVARCHAR(max)
SET @subject = 'Relatório de Verificação de Consistência do Banco de Dados para o Servidor: ' + @@servername
SET @tableHTML = '
Resumo da Verificação de Consistência do Banco de Dados no Servidor ' + @@servername + '
MessageText
Banco de Dados Corrompido
'
+ CAST((
SELECT ISNULL(messagetext, '') AS 'TD', '', ISNULL(DB_NAME(dbid), '') AS 'TD', '', ISNULL(repairlevel, '') AS 'TD', ''
FROM #errormsgs
FOR XML PATH('tr'), ROOT
) AS NVARCHAR(max))
+ '
'
EXEC msdb..sp_send_dbmail
@profile_name = 'DBMailProfile',
@recipients = 'ni*********87@outlook.com',
@subject = @subject,
@importance = 'High',
@body = @tableHTML,
@body_format = 'HTML'
DROP TABLE #userdatabases
END
Passo 4: Configurar o Database Mail e o Agendador de Tarefas do Windows
Como a edição SQL Server Express não suporta o Database Mail e o SQL Server Agent, precisamos configurar o Database Mail usando consultas T-SQL e usar o Agendador de Tarefas do Windows para automatizar a verificação de consistência.
Para obter instruções detalhadas sobre como configurar o Database Mail na edição SQL Server Express, você pode consultar o artigo “Configuração do Database Mail na edição SQL Server Express”.
Para automatizar a verificação de consistência, crie um arquivo em lote que execute a stored procedure usando o comando SQLCMD. Aqui está um exemplo do arquivo em lote:
sqlcmd -S Nisarg-PC\SQLExpress -Q "EXEC sp_DB_Integrity_check" -d DBATools
Salve o arquivo em lote com a extensão .bat.
Em seguida, crie uma tarefa no Agendador de Tarefas do Windows para executar o arquivo em lote em um intervalo especificado. Aqui estão os passos:
- Abra o Painel de Controle
- Abra as Ferramentas Administrativas
- Abra o Agendador de Tarefas
- Clique em “Criar uma tarefa básica”
- Especifique o nome e a descrição da tarefa
- Escolha a frequência (por exemplo, semanalmente)
- Especifique o horário de início e a recorrência
- Escolha a opção “Iniciar um programa”
- Especifique o caminho completo do arquivo em lote
- Revise os detalhes da tarefa e clique em “Concluir”
Agora, a verificação de consistência será realizada automaticamente de acordo com o cronograma especificado.
Seguindo essas etapas, você pode automatizar a verificação de consistência de bancos de dados SQL criados ou restaurados na edição SQL Server Express. Isso pode ser especialmente útil para administradores de banco de dados iniciantes que desejam otimizar suas tarefas de manutenção de banco de dados.
No próximo artigo, exploraremos como automatizar a manutenção de índices em bancos de dados SQL criados na edição SQL Server Express usando o Agendador de Tarefas do Windows. Fique ligado!