Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql2000 after suspect it is showing (recovering\suspect)

Posted on 2004-03-31
6
Medium Priority
?
1,128 Views
Last Modified: 2012-08-13
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
Comment
Question by:zedlin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10723921
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
 
LVL 34

Expert Comment

by:arbert
ID: 10723924
Oops, should be RESTORE DATABASE allen with RECOVERY
0
 
LVL 1

Expert Comment

by:NotaClue
ID: 10723987
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 13

Accepted Solution

by:
danblake earned 1500 total points
ID: 10724312
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
 
LVL 34

Expert Comment

by:arbert
ID: 10724404
"What was the error that caused the suspect database "

"From error messages I understood that it is suspected because of not enough space."
0
 
LVL 13

Expert Comment

by:danblake
ID: 10724570
@arbert  & @zedlin

Correction:
An exact error code would help.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question