Solved

sql2000 after suspect it is showing (recovering\suspect)

Posted on 2004-03-31
6
1,114 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 13

Accepted Solution

by:
danblake earned 500 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

Technology Partners: 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!

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

730 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