MS SQL (7/2000) database "Suspect Mode" / "Emergency Mode" / "Error 1813

Recovering MSSQL from "Suspect Mode" / "Emergency Mode" / "Error 1813"

For SQL 2000 database some time database goes into suspect mode, when you trying to delete database directly.


sp_configure "Allow updates" , 1
go
update sysdatabases
set status = status & ~ 256
where (name = 'SuspectDatabaseName')
go
sp_configure "Allow updates" , 0
go

* user must be SQL admin user.
it will restore database.

if still not works go for hotfix

click here http://support.microsoft.com/kb/889266

try this http://www.gerixsoft.com/node/24
(good post)

keep in mind

The issues that are fixed by this hotfix package.
The prerequisites for installing the hotfix package.
Whether you must restart your computer after you install the hotfix package.
The files that are contained in the hotfix package।


"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
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"
These articles explain the trick behind suspect and emergency mode:http://support.microsoft.com/support/kb/articles/Q165/9/18.ASPhttp://www.swynk.com/friends/knight/unmarksuspect.asphttp://support.microsoft.com/support/kb/articles/Q180/5/00.asp
3. "Emergency Mode" --> "Normal Mode"
Now your database is in "emergency mode". Disregard Enterprise Manager does not show tables, views and procedures - use Query Analyzer's Object Browser instead.
"Emergency mode" means database is locked against any modifications, it is readonly forever. There is no way to bring "emergency" database back to normal state. All we can do is copy all data from it into new database.
Create new database (pubz)
Create new DTS package
Use "Copy SQL Objects" task to transfer data from old database to new database (pubs->pubz)
Execute the DTS job & enjoy!
Note that there might be complex dependencies between database objects, so Copy SQL Objects task might not work properly. For example: view A is dependent on view Z; Copy SQL Objects task will try to create view A first, -- that will fail, because view Z does not exist yet.
4. "Error 1813" --> "Suspect Mode"
If you receive "Error 1813", this probably means that you are trying to attach database you have improperly detached, or haven't detached at all. Only successfully detached databases can be successfully attached back.
Create new database using the same name and file location as database that is failing to attach
Stop SQL Server
Copy non-attachable data files over the new ones
Delete log files
Start SQL Server
Open Enterprise Manager - the database will be marked as "suspect"
Once your database is "suspect", use instructions outlined above...
Note that database will be marked "suspect" only if transaction log contained active sections. To illustrate the problem for 100%, run a few inserts against the database before deleting transaction log.
http://www.gerixsoft.com/trackback/24