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,
To repair the database, run the following queries in Query Analyzer,
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
3. Now proceed to the sysdatabases table in the master
database, locate your database row and put 32768 into its status column
you need to switch to an emergency mode, and you do it like this:
you right click the SUSPECT database,and choose properties.
all that is done from the Enterprise Manager.
click Server Settings (tab), and now click (un check) , the “Allow modifications
to be made directly to the system catalogs” and click OK.
now u need to open the “sysdatabases” table from the “master” database.
find the record of the “SUSPECT Ed” database, in the “status” column of that
record change the value to 32768.
by doing that u put the database to an emergency mode.
now do restart to you SQL server.
You click the START button Programs Microsoft SQL Server Import and Export Data and do a
copy from the SUSPECTED database to a totally new database.
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_ERRORMSGSOutput 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’;and you are done.
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
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.
With SQL 2000 .........
First Step
"Suspect Mode" --> "Emergency Mode"
Open Enterprise Manager,
- Right click onto your server node, - an SQL Server
Properties dialog will popup, - Enable "Allow modifications to be made directly to the system catalogs" option
3. Now proceed to the sysdatabases table in the master
database, locate your database row and put 32768 into its status column
- Restart SQL Server and refresh Enterprise Manager
- Open Enterprise Manager - the
database will be marked as "Emergency Mode"
you need to switch to an emergency mode, and you do it like this:
you right click the SUSPECT database,and choose properties.
all that is done from the Enterprise Manager.
click Server Settings (tab), and now click (un check) , the “Allow modifications
to be made directly to the system catalogs” and click OK.
now u need to open the “sysdatabases” table from the “master” database.
find the record of the “SUSPECT Ed” database, in the “status” column of that
record change the value to 32768.
by doing that u put the database to an emergency mode.
now do restart to you SQL server.
You click the START button Programs Microsoft SQL Server Import and Export Data and do a
copy from the SUSPECTED database to a totally new database.
