Repair Suspected Database

Repairing Suspect SQL Database

I'm not a DBA but I have a real life experience repairing a suspect databases twice. I guess I can say that I was unlucky at that time as I was presented in a tough situation where I was still in the process of learning how things work and we didn't have a DBA resource (remember I am a developer), but still consider it lucky as even some experienced DBAs might not have even experienced this situation. I experienced a similar situation before, and restoring it was easy before as we have, but that was easier as I have the proper restore points.

Now in this situation, the backups had failed so the last clean backup we had was 2 weeks old, so I could only rely on that one plus the current transaction log which is not backed up for now. So the first thing that came to my mind was to backup the transaction log so I could easily restore the full lot. I tried to backup the transaction log, but I had to remove the suspect status first by running this TSQL:

Collapse | Copy Code

sp_resetstatus 

Now it was not a suspect anymore, I was hoping to run the backup of transaction log by using this TSQL:

Collapse | Copy Code

msdb Database Suspected\Corrupted-Solution (SQL 2000&2005) >> FEBRUARY 13, 2012 One day I got a call from one of my offices that some programs ie point of sale is not working and the auto back up is not running. I reached the office and checked the server for any errors I found that msdb database is corrupted. I looked for the back ups then found that I have not planned auto backups for system database. I searched over the web to find a solution for the problem. I called to all my friends for solutions. One of my friend give me a solution that worked for me. In Sql server 2000 you start the server with trace flag 3608.In SQL Server Enterprise Manager, right-click the server name, and then click Properties. On the General tab, click Startup Parameters. Add the following new parameter: -c -m -T3608 After this restart the sql server and detatch the msdb database and move the mdf and ldf files from the default folder C:\Program Files\Microsoft SQL Server\MSSQL\DAta Then recreate msdb file by executing in new query window instmsdb.sql (C:\Program Files\Microsoft SQL Server\MSSQL\Install) Then remove-c -m -T3608 from the startup parameters in SQL Server Enterprise Manager Then stop and restart the sql server 2000 and then recreate the maintenance plans In SQL Server 2005 Detach the msdb database and install/create new msdb database. This will solve the problem. But the problem is that SQL Server 2005 does not allow to detach a system database. This will be solved by the following another way i-family: Cambria,serif;"> Start--------------Sql Server 2005-------------Configuration Tools----------SQL Server Configuration Manager Then click on SQL server 2005 services.-----On the right side----Right click on SQL Server----Select Properties Click on the Advance Tab and change the Parameters of Start up Parameters ie please add -m;-c;-T3608 in front of the existing parameter. Then restart SQL server detach the msdb database by query ”use master go sp_detach_db ‘msdb’ go and click Execute ” Then move the existing mdf and ldf files of msdb from” C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data “ Then recreate msdb files open the nstmsdb.sql in a new query window and execute.The file is located in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install Then your msdb is restored. Before restarting the sql server remove the parameters (-m;-c;-T3608 )Then restart the sql server. Be and recreate the new maintenance plan.

In Sql server 2000

you start the server with trace flag 3608.In SQL Server Enterprise Manager, right-click the server name, and then click Properties.

On the General tab, click Startup Parameters.

Add the following new parameter:

-c -m -T3608

After this restart the sql server and detatch the msdb database and move the mdf and ldf files from the default folder C:\Program Files\Microsoft SQL Server\MSSQL\DAta

Then recreate msdb file by executing in new query window

instmsdb.sql (C:\Program Files\Microsoft SQL Server\MSSQL\Install)

Then remove-c -m -T3608 from the startup parameters in SQL Server Enterprise Manager

Then stop and restart the sql server 2000

and then recreate the maintenance plans

In SQL Server 2005

Detach the msdb database and install/create new msdb database. This will solve the problem. But the problem is that SQL Server 2005 does not allow to detach a system database. This will be solved by the following

another way  Start--------------Sql Server 2005-------------Configuration Tools----------SQL Server Configuration Manager

Then click on SQL server 2005 services.-----On the right side----Right click on SQL Server----Select Properties

Click on the Advance Tab and change the Parameters of Start up Parameters ie please add -m;-c;-T3608 in front of the existing parameter.

Then restart SQL server

detach the msdb database by query

”use master

go

sp_detach_db ‘msdb’

go

and click Execute

”

Then move the existing mdf and ldf files of msdb from” C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data “

Then recreate msdb files open the nstmsdb.sql in a new query window and execute.The file is located in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install

Then your msdb is restored. Before restarting the sql server remove the parameters (-m;-c;-T3608 )Then restart the sql server. Be and recreate the new maintenance plan.

Recovery of Damaged msdb database

If your msdb goes suspect then you have two choices, restore it from a backup or recreate it (and then recreate any scheduled jobs). Obviously everyone has a comprehensive and valid set of backups, right? If only.

Of course, the very first thing you do is work out why it went suspect in the first place and take any necessary steps to stop it happening again.

Now you'd hope that if you don't have a valid msdb backup then you can at least run repair on it and so you don't lose everything in there. Well, that works as long as the transaction log isn't damaged. Ok, but then surely we can stick the database into the now-documented emergency mode (alter database dbname set emergency) and run emergency mode repair? (dbcc checkdb (dbname, repair_allow_data_loss)in emergency mode). Nope, msdb can't be put into emergency mode.

So, you're out of options and you're going to have to recreate msdb. Here's what to do (change the directory paths to suit your installation):

1. Detach the damaged msdb. You can't just detach msdb because you're not allowed to detach system databases. However, you can if you start the server with trace flag 3608. I did this by shutting down the server, navigating to the directory 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn' and doing the following: start sqlservr.exe -c -T3608

2. Move or rename the damaged msdb files (msdbdata.mdf and msdblog.ldfin the
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' directory)
3. Run the instmsdb.sql script in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory
4.Shutdown and restart the server without the 3608 trace flag

This works on SQL Server 2000 as well.
Hopefully you'll never have to do this but if you ever do, let me know how it goes for you.

Courtesy : sysadmindop.blogspot.in

HOW TO REPAIR A SQL SUSPECTED DATABASE

Sometimes when you connect to your database server, you may find it in suspect mode. Your database server won’t allow you to perform any operation on that database until the database is repaired.

A database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.

To get the exact reason of a database going into suspect mode can be found using the following query

DBCC CHECKDB (‘YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS

Output of the above query will give the errors in the database.

To repair the database, run the following queries in Query Analyzer,

EXEC sp_resetstatus ‘yourDBname’;

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb(‘yourDBname’)

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

And you are done.

You should keep one thing in mind while using the above queries that the repair mode used here , REPAIR_ALLOW_DATA_LOSS, is a one way operation i.e. once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database before executing above mentioned queries.