Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1135
  • Last Modified:

sql2000 after suspect it is showing (recovering\suspect)

Hi,
My database name is allen
today i saw mydatabase name :   allen(suspect)
From error messages I understood that it is suspected because of not enough space.

I created more spaces.
With query analyzer, I changed sp_resetstatus
after restarting sql, not it is showing
allen(recovering\suspect)

Last two hours, it is showing like this.
mt database size is 10 gb.

what must i do?


0
zedlin
Asked:
zedlin
  • 3
  • 2
1 Solution
 
arbertCommented:
Do you have a backup???  If so, restore would be your safest bet.  However, you can try and force a recovery from query analyzer:

restore allen with RECOVERY


0
 
arbertCommented:
Oops, should be RESTORE DATABASE allen with RECOVERY
0
 
NotaClueCommented:
Here is a good article on recovery

http://www.winnetmag.com/Article/ArticleID/14060/14060.html

Everytime I've had a DB marked as suspect it was because the physical device the db was saved on had a problem, so be sure you have current backups.

I've never had a db marked as suspect from the drive being full, although I had the transaction log on the same drive, so if the trasaction log is on anther drive  I guess that could happen. When a db is recovering it is going through every transaction and reapplying to the db, if your log is very big that might take a while, if you log is that big, you might what to look at your recovery strategy and shorten the time between  complete backups for the log at least...

HTH,
Jason
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
danblakeCommented:
What was the error that caused the suspect database (do you know  ? this may help us.[will be found in sql-server error log].)


The full steps involved in using sp_resetstatus are:
Execute sp_resetstatus.
Use ALTER DATABASE to add a data file or log file to the database.
Stop and restart SQL Server.
With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database.

Free disk space and rerun recovery.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_494j.asp

Moving DB to/from Emergency Mode:
http://www.devx.com/vb2themax/Tip/18624

Some new info:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_4rol.asp
Refering to how to fix:  (Refering to DBCC RECOVERDB(database))
Reports error message 9002 or 1105 in the Microsoft® SQL Server™ error log.

0
 
arbertCommented:
"What was the error that caused the suspect database "

"From error messages I understood that it is suspected because of not enough space."
0
 
danblakeCommented:
@arbert  & @zedlin

Correction:
An exact error code would help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now