Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1130
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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