Solved

sql2000 after suspect it is showing (recovering\suspect)

Posted on 2004-03-31
6
1,101 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
  • 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now