Reconstruindo o MSDB em caso de corrupção

Imagine a situação onde por algum motivo o MSDB da sua instância do SQL Server ficou corrompido.

Com isso, o serviço do SQL Server Agent não “sobe” e você corre o risco de perder todos os jobs e tudo mais que o MSDB armazena.

No meu caso, recebi a mensagem de erro abaixo e o banco apareceu como SUSPECT:

Error: 9003, Severity: 20, State: 9.

The log scan number (52869:200:1) passed to log scan in database ‘msdb’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

Se você tentar executar um DBCC CHECKDB no banco, o SQL Server vai exibir uma mensagem dizendo que o banco está como SUSPECT e que você não pode executar tal comando.

Nesse momento o mais fácil a se fazer é restaurar o backup do seu MSDB (afinal, você faz backup dos seus bancos de dados de sistema, certo?!).

Caso por algum motivo você não possa restaurar o MSDB, ainda há a possibilidade de realizar alguns procedimentos para reconstruir o seu MSDB, mas obviamente nesse caso você vai perder tudo que tinha armazenado no mesmo. Após isso o seu serviço do SQL Agent irá “subir” novamente e você poderá iniciar o processo de reconstrução de jobs e tudo mais.

A primeira ideia que deve vir a mente da maioria é realizar o procedimento “padrão” que era realizado no SQL Server 2000, ou seja, ativar as atualização em tabelas de sistema, realizar um UPDATE para colocar o banco em modo de emergência e então realizar o procedimento de recuperação com o DBCC CHECKDB.

O problema é que a partir do SQL Server 2005 a Microsoft bloqueou os UPDATES em tabelas de sistema, então se realizarmos o procedimento que citei anteriormente:

sp_configure ‘allow updates’, 1

GO

RECONFIGURE WITH OVERRIDE

GO

UPDATE MASTER..SYSDATABASES SET STATUS = -32768 WHERE NAME = ‘BANCO’

GO

sp_configure ‘allow updates’, 0

GO

Ao tentar realizar o UPDATE em SYSDATABASES você receberá a mensagem informando que:

Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.

Com isso temos que realizar um procedimento alternativo para reconstrução do MSDB.

Como iremos RECONSTRUIR o MSDB, precisamos de uma forma de “iniciar” o SQL Server sem que haja dependências ao MSDB, e para devemos parar o serviço do SQL Server e iniciar ele com os seguintes parâmetros de inicialização e trace flags:

-c;-m;-T3608;

Você pode fazer isso tanto através da interface gráfica:

Parâmetros de inicialização

Como também através da linha de comando:

  1. Abra um prompt de comando
  2. Navegue até a pasta BINN da instância
  3. Digite: start sqlservr.exe -c -m -T3608

Na verdade o parâmetro -m não é realmente necessário, mas eu prefiro subir a instância em modo de single-user.

Já o trace flag 3608 será necessário para que o SQL Server inicie apenas o banco de dados MASTER e assim nós poderemos alterar o banco de dados MSDB.

Maiores informações sobre parâmetros de inicialização podem ser encontradas nesse link.

Após configurar esse parâmetro, inicie a instância do SQL Server e conecte na mesma utilizando o SQLCMD:

  1. Abra um prompt de comando
  2. Digite: SQLCMD -dmaster -U USUARIO -P SENHA -S INSTANCIA

Ao conectar na instância alguns podem pensar (como eu pensei), “ah, agora posso tentar fazer dettach do MSDB e fazer attach apenas do arquivo de dados (.mdf) e mandar reconstruir o log”.

Bem, o primeiro ponto é que não é recomendável fazer dettach de um banco SUSPECT, pois a tendência é de que ele não vá fazer attach posteriormente (palavras do Paul Randall).

Mas caso você tente, ocorrerá o seguinte:

1) fazer dettach do MSDB

use master
go
sp_detach_db ‘msdb’
go

2) Renomear ou remover o arquivo de log pelo menos (já que iremos criar outro na mesma pasta)

3) CREATE DATABASE [msdb] ON ( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MSDBData.mdf’ ) FOR ATTACH_REBUILD_LOG;

Você deve receber a mensagem:

File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MSDBLog.ldf” may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘MSDB’. CREATE DATABASE is aborted.

Isso ocorre, pois como diz a mensagem, o banco de dados não sofreu um shutdown normal e não está em um estado “limpo”.

Com isso nos resta apenas a opção de reconstruir o MSDB, com o seguinte processo:

1) Fazer o dettach do mesmo (que só é possível devido ao -T3608)

use master
go
sp_detach_db ‘msdb’
go

2) Renomear ou apagar os arquivos de dados e log do MSDB, já que iremos criar os arquivos novamente

3) Abrir uma conexão via SQLCMD informando um arquivo .sql para ser executado. No caso, estamos apontando para o arquivo instmsdb.sql que fica na pasta INSTALL do SQL Server e que tem exatamente a funcionalidade de criar o MSDB (vazio obviamente).

SQLCMD -A -dmaster -U USUARIO -P SENHA -S INSTANCIA -i”C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\instmsdb.sql”

*veja que nos meus scripts aponto a pasta MSSQL.1, mas esse número pode ser diferente no seu ambiente.

Após isso, você verá que ele reconstrói os objetos do MSDB.

Agora basta parar o serviço do SQL Server (stop sqlservr.exe) ou pela GUI, remover os parâmetros de inicialização e então inicializar a instância novamente.

Após isso, você verá que seu MSDB foi reconstruído, mas que todos os jobs e demais objetos que havia criado foram perdidos. Caso não faça backup dos seus bancos de sistema, essa é uma boa hora para começar a fazer!

Links relacionados ao assunto desse post:

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

6 respostas para Reconstruindo o MSDB em caso de corrupção

  1. Tiago Balabuch disse:

    Show! Passei por esse problema essa semana… Resolvi de outra forma… Mas com certeza essa opção é essencial!

  2. Pingback: Recriando as databases de sistema « Alex Souza

  3. Ricardo Oliveira disse:

    perfeito ! ótima explicação ! resolveu o problema !

  4. Gustavo disse:

    Parabéns! Segui passo a passo e resolvi meu problema perfeitamente. Única diferença no comando SQLCMD -A -dmaster -U USUARIO -P SENHA -S INSTANCIA -i”C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install\instmsdb.sql” a opção -A não funcionou no meu SQL Server 2008 R2 Express. Tirei a opção e rodou perfeitamente. Obrigado

  5. Gustavo disse:

    Usei pegando a base de outro computador com a mesma versão do SQL Server

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