Suspect database
How to fix a SQL 2000 Suspect Database when you have no backup.
I am new to database world.
Any information is higly appreciated.
THNX
I am new to database world.
Any information is higly appreciated.
THNX
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
1) Make a copy of the SQL Server logs *immediately*, otherwise they may be lost. The (default) file names are ERRORLOG thru ERRORLOG.6 in (default)
directory '?:\Program Files\Microsoft SQL Server\MSSQL\LOG'. This may be needed to help determine how the db became suspect, and thus how to fix it.
2) Immediately after issuing the EXEC sp_resetstatus, do a backup. Then, in the worst case, you can be back to where you started.
For example, you might need to a DBCC CHECKDB and eventually try to fix errors, which can lose data.
directory '?:\Program Files\Microsoft SQL Server\MSSQL\LOG'. This may be needed to help determine how the db became suspect, and thus how to fix it.
2) Immediately after issuing the EXEC sp_resetstatus, do a backup. Then, in the worst case, you can be back to where you started.
For example, you might need to a DBCC CHECKDB and eventually try to fix errors, which can lose data.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Be very careful before detaching a damaged db -- it may refuse to reattach.
I agree with Scott--I don't think I would attempt a detach...Also, sp_db_kill_users isn't a shipped SQL Server stored proc.....
ASKER
The following is the message i got when i tried , sp_resetstatus xxxx
Prior to updating sysdatabases entry for database 'xxxx', mode = 0 and status = 1073742096 (status suspect_bit = 256).
For row in sysdatabases for database 'xxxx', the status bit 256 was forced off and mode was forced to 0.
Warning: You must recover this database prior to access.
Prior to updating sysdatabases entry for database 'xxxx', mode = 0 and status = 1073742096 (status suspect_bit = 256).
For row in sysdatabases for database 'xxxx', the status bit 256 was forced off and mode was forced to 0.
Warning: You must recover this database prior to access.
You're not working with a "log shipped" database are you???
From query analyzer run this (from the master database)
restore yourdatabasename with recovery
From query analyzer run this (from the master database)
restore yourdatabasename with recovery
ASKER
I used: restore database XXXX with recovery
I got :
Server: Msg 4323, Level 16, State 2, Line 1
The database is marked suspect. Transaction logs cannot be restored. Use RESTORE DATABASE to recover the database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I got :
Server: Msg 4323, Level 16, State 2, Line 1
The database is marked suspect. Transaction logs cannot be restored. Use RESTORE DATABASE to recover the database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Is this a log shipped database?
ASKER
No. It is a user database on a stand alone database.
ASKER
sorry, i meant stand alone server.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How to restore a SQL Server database marked as "suspect"
At times a database appears to be marked as "suspect" in the Enterprise Manager. SQL Server marks a database as suspect with it can't access the database. What happens at a low level is that SQL Server sets one of the bits in the status field in the sysdatabases table.
For More:
http://www.devx.com/vb2themax/Tip/18624
At times a database appears to be marked as "suspect" in the Enterprise Manager. SQL Server marks a database as suspect with it can't access the database. What happens at a low level is that SQL Server sets one of the bits in the status field in the sysdatabases table.
For More:
http://www.devx.com/vb2themax/Tip/18624
sp_resetstatus yourdatabasenamehere