Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dinky
Starting Member
37 Posts |
 Posted - 2008-02-18 : 09:28:12
|
| We have a SQL Server 2000 database that user has reported that it's been 'marked as suspect'. How to get it back online.Even though I myself for my machine can connect to that database server and make changes to the database.I am new to sql server. Please advice. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-18 : 09:44:30
|
| Try sp_resetstatus but you have to restart the service. Google for sp_resetstatus for correct syntax |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-18 : 09:46:06
|
| EXEC sp_resetstatus 'DBname' |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-18 : 13:12:29
|
| That doesn't make db in working status, have to find out why db is in suspect and fix the problem. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-18 : 13:25:04
|
| rmaio,Here is from Booksonline.sp_resetstatus turns off the suspect flag on a database. This procedure updates the mode and status columns of the named database in sys.databases. The SQL Server error log should be consulted and all problems resolved before running this procedure. Stop and restart the instance of SQL Server after you execute sp_resetstatus.A database can become suspect for several reasons. Possible causes include denial of access to a database resource by the operating system, and the unavailability or corruption of one or more database files. |
|
|
|
fsilber
Starting Member
11 Posts |
Posted - 2008-02-18 : 15:58:58
|
quote:
Originally posted by rmiao
That doesn't make db in working status, have to find out why db is in suspect and fix the problem.
Where do we learn how to fix the problem?For example, due to a power outage I have torn pages. I ran DBCC CHECKDB ('my database') WITH ALL_ERRORMSGS, NO_INFOMSGS and it gave me a bunch of cryptic error messages suggesting that some rows had foreign keys that did not exist in the parent table. Most of the error messages were in terms of "object ids". I tried running CHECKDB in EMERGENCY MODE with REPAIR_ALLOW_DATA_LOSS -- though I was able to see the database in QueryAnalyzer it apparently did not delete the offending rows.Do I need to identify the offending rows by hand? If so, how do I go about doing that? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-18 : 20:50:50
|
| >> This procedure updates the mode and status columns of the named database in sys.databases.It's true and that's what it does, but it doesn't fix the db. Period.>> Where do we learn how to fix the problem?No single solution. Check sql log and windows event logs to find out what caused db suspect and fix them. |
|
|
|
|
|
|
Database marked as suspect - bring online?