Restaurando um Database Suspect – SQL Server

Se seu banco de dados estiver em modo suspeito, então nenhuma operação ocorrerá até que o banco de dados seja reparado. Neste post irei mostrar como recuperar o banco de dados nessa situação.

Normalmente esse tipo de situação não é muito comum, porém é um problema crítico, pois causa indisponibilidade no database em questão. Este problema é causado quando ocorre alguma corrupção nos arquivos de dados (DATABASELog.ldf), ou quando falta espaço no disco onde estão os arquivos de DATA e/ou LOG.

Quando ocorre o arquivo de LOG do database está corrompido, você pode receber algumas mensagens de erro que indicam este tipo de situação. Vamos supor que você está usando o SQL Query Analyser, você poderá receber as seguintes mensagens de erro:

Msg 945, Level 14, State 2, Line 1
Database ‘xxxxxx’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Msg 926, Level 14, State 1, Line 1
Database ‘xxxxxx’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

Ou se você estiver utilizando o GUI do SSMS (SQL Server Management Studio) você receberá uma mensagem de erro parecida com a imagem abaixo:

db01

Você também pode ver quais databases estão com o status SUSPECT, executando a query a seguir:

USE master
GO

SELECT 
	Name, State_Desc
FROM 
	sys.databases 
WHERE 
	State_Desc ='SUSPECT'

Se você encontrar algum database com o status SUSPECT, mantenha a calma. A partir do SQL Server 2005 foi introduzida um novo DB Status Emergency. Esse modo pode mudar o DB de modo suspeito para o modo de emergência, para que você possa recuperar os dados somente no modo de leitura. Os passos são simples: depois de de você executar o script a seguir, você terá de volta o seu banco de dados em modo operacional.

Obviamente há mais duas opções disponíveis. A opção REPAIR_ALLOW_DATA_LOSS remove os dados corrompidos, assegurando que o banco de dados seja devolvido a um estado estrutural, transacional e consistente. Tenha em mente que o modo de emergência é uma operação unidirecional, ou seja, qualquer coisa que for feita não pode ser revertida ou desfeita. Para realizar esta operação, realize um BACKUP do database antes de entrar no modo de emergência. Como é uma operação de mão única, você não pode envolvê-lo em uma user-transaction. É uma opção de reparo disponível somente em modo de emergência – se você tentar usar o REPAIR_REBUILD, então ele não irá funcionar.

Obs.: Quando um database está no modo EMEGERNCY, só é possível realizar operações de SELECT no database. UPDATE, INSERT e DELETE não são permitidos. Há perda de dados, pois a opção REPAIR_ALLOW_DATA_LOSS apaga os dados corrompidos.
Nem sempre o SQL Server deixa o database com o status “SUSPECT”. Vamos supor que o problema no arquivo de LOG aconteceu justamento no cabeçalho do mesmo, o SQL Server deixará o status do database como “PENDING STATE”.

Links uteis:
ALTER DATABASE
DBCC CHECKDB

USE Master
GO
-- Coloca o database em modo de emergência
ALTER DATABASE ALAN_TESTE SET EMERGENCY
GO
-- Realiza um check do database
DBCC CHECKDB('ALAN_TESTE')
GO
-- Altera o database para SINGLE_USER, ou seja, só um usuário pode estar conectado
ALTER DATABASE ALAN_TESTE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
-- Realiza o comando para reparo do database
DBCC CHECKDB('ALAN_TESTE', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS
GO
-- Volta a base de dados para multiplos usuários
ALTER DATABASE ALAN_TESTE SET MULTI_USER
GO
-- Restarta o status do database
EXEC sp_resetstatus 'ALAN_TESTE'

Raramente esse tipo de problema irá acontecer, mas quando acontecer é importante analisar detalhadamente para entender o porquê da corrupção dos arquivos de log.

Nesse link, tem uma demo de como deixar um database com o status SUSPECT, e também como resolver esse problema.

Comandos DBCC

Existem uma série de comandos no SQL Server, estes chamados Comandos DBCC. Esta sigla refere-se à Data Base Console Commands. São comandos que fazem a consistência lógica e física da base de dados. A maioria dos comandos retornam apenas informações sobre o banco de dados, porém existem alguns comandos que podem ajudar a resolver alguns problemas.

Estes comandos estão divididos em 4 partes. São elas:

1 – Comandos de Manutenção

Estes comandos fazem a manutenção preventiva ou corretiva no banco de dados:

-- Reconstroí os índices de uma tabela.
DBCC DBREINDEX

-- Apaga um banco corrompido. Use DRP DATABASE ao invés de DBCC DBREPAIR.
DBCC DBREPAIR

-- Desfragmenta um ou mais índices de uma tabela. Melhora a performance do índice.
DBCC INDEXDEFRAG

-- Diminui o tamanho de todos os arquivos de dados ou Log de um banco de dados até um certo tamanho.
DBCC SHRINKDATABASE

-- Diminui o tamanho de um arquivo específico de dados ou Log de um banco de dados até um certo tamanho.
DBCC SHRINKFILE

-- Corrige erros de tamanho de dados retornados pela Stored Procedure sp_spaceused.
DBCC UPDATEUSAGE

2 – Comandos Gerais

Possuem diversas funcionalidades, como alocação de tabela na memória, ajuda sobre outros comandos DBCC:

-- Descarrega da memória uma DLL que possui alguma Extend Stored Procedure associada a ela.
DBCC (FREE)

-- Retorna a sintaxe de algum outro comando DBCC
DBCC HELP

-- ‘Pina’ a tabela , ou seja , faz o SQL Server não liberar da memória algumas informações de uma tabela. 
-- Se utilizado com cuidado , há ganho de performance.
DBCC PINTABLE

-- Faz o SQL Server liberar da memórias algumas informações de uma tabela que foi ‘pinada’ 
-- como comando DBCC PINTABLE.
DBCC UNPINTABLE

-- Simplesmente incluída por compatibilidade. A funcionalidade que este
-- comando proporcionava já é embutida automaticamente no SQL Server 2000
DBCC ROWLOCK

-- Habilita um flag de trace que é necessário para outros comandos DBCC.
DBCC TRACEON

-- Desabilita um flag de trace setado como comando DBCC TRACEON
DBCC TRACEOFF

3 – Comandos de Status

Fazem algumas verificações de algumas configurações do banco de dados:

-- Mostra o último comando enviado por um processo ( usuário ) no SQL Server. 
-- É parecido com o trace que o Profiler mostra , porém limitado.
DBCC INPUTBUFFER

-- Mostra o output atual que o banco retornou para um processo ( usuário ) no SQL Server. 
-- O resultado retornado é em hexadecimal…
DBCC OUTPUTBUFFER

-- Mostra informações sobre a transação mais velha ( mais tempo executando ) em um banco de dados.
DBCC OPENTRAN

-- Mostra informações sobre o Procedure Cache.
DBCC PROCCACHE

-- Mostra várias informações sobre os índices de uma tabela, inclusive o nível de fragmentação do índice.
DBCC SHOWCONTIG

-- Mostra as informações sobre as estatísticas de uma tabela. 
-- Estatísticas são muito importantes para a melhora de performance.
DBCC SHOW_STATISTICS

-- Informações sobre tamanho do Log dos bancos de dados.
DBCC SQLPERF

-- Mostra a situação dos flags de trace que foram setados com o DBCC TRACEON. 
-- Estes traces controlam configurações internas do SQL Server
DBCC TRACESTATUS

-- Mostra várias informações referentes à conexão do usuário.
DBCC USEROPTIONS

4 – Comandos de validação

Realiza a checagem das condições de alguns objetos do banco de dados como tabelas, valores das colunas IDENTITY e constraints:

-- Verifica o espaço para as estruturas de alocações internas do SQL Server. 
-- Permite alguns reparos em caso de erro.
DBCC CHECKALLOC

-- Somente checa a consistência de algumas tabelas de sistema do SQL Server ( que compõem o Database Catalog ). 
-- Não faz reparos
DBCC CHECKCATALOG

-- Checa os relacionamentos de uma determinada constraint no banco de dados. Não faz reparos.
DBCC CHECKCONSTRAINTS

-- Verifica erros de alocação e de consistência em vários objetos do banco de dados. 
-- Pode efetuar reparos importantes em caso de erro.
DBCC CHECKDB

-- Muito parecido com o DBCC CHECKDB , porém só faz a verificação no nível do filegroup de um database. 
-- Não faz reparos.
DBCC CHECKFILEGROUP

-- Chega e corrige, caso necessário, valores de colunas que possuem a propriedade IDENTITY. 
-- Pode inclusive resetar o valor inicial ( seed ) da coluna que possui a propriedade IDENTITY.
DBCC CHECKIDENT

-- Checa e corrige a integridade das páginas de dados, índices, ntext, text 
-- e image para uma tabela ou uma indexed view
DBCC CHECKTABLE

-- Idêntica à DBCC CHECKALLOC. Foi mantida por compatibilidade.
DBCC NEWALLOC

Cada comando executado é gerado uma resposta através de uma mensagem:

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

Isso é meio vago, mas tudo bem pois indica que o comando foi executado sem problemas.