A. A. In addition to these ideas, also check out support.microsoft.com/support for the Microsoft Knowledgebase. Specifically Q165918.
Firstly look in <sql>\LOG and look at all recent errorlog(s). There WILL be an indication here as to why the database has been marked suspect. You need to fix whatever the problem is first (i.e. missing file, permissions problem, hardware error etc.)
Then, when the problem has been fixed and you're either sure that the data is going to be ok, or you have no backup anyway, so you've nothing to lose, then change the database status to normal and restart SQL Server. To change the database status, and to get more information on recovery, look up the sp_resetstatus sp in the Books Online.
If you don't have access to sp_resetstatus information, then the short version of this is :-
UPDATE master..sysdatabases SET status = status ^ 256 WHERE name = <dbname>
If the database still goes back into suspect mode, and you can't fix the original problem, and you have no recent backup, then you can get information out of the database by putting it into emergency mode. If you do this, extract the data/objects out with bcp/transfer manager and then rebuild the database. Note that the data may be corrupt or transactionally inconsistent.
Issue the following command to put the database into emergency mode (you'll need to allow updates first)
UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'
End of Article
The hint how to put the database in emergency mode is great. The way how to set a database in emergency mode is not mentioned at all in the SQL-Server documentation.
Sören Schlegel May 07, 2001
i do not view databases for suspect source SQL Server Assertion: File: <page.cpp>, line=2992 Failed Assertion = 'spaceNeeded <= spaceContig && spaceNeeded <= space_usable'. I don't urdestend
Hugo Walter Rodriguez June 12, 2001
Here is more details of this solution.. http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html
enjoy,, cpdinesh@gmail.com
Anonymous User July 17, 2005 (Article Rating: )
You must be a registered user or online subscriber to comment on this article. Please log on before posting a comment. Are you a new visitor?
Register now
Picking a favorite product from an impressive crowd of competitive offerings is never an easy task, and such was the case with our Editors' Best and Community Choice awards this year. ...
An often irreverent look at some of the week's other news, including some post-PDC some soul searching, a Google Chrome OS announcement and a Microsoft response, Windows 7 off to a supposedly strong start, the Jonas Brothers and Xbox 360, and so much more ...
Free CDs Offer Fundamental Content for IT Pros Are you up to speed on the latest technologies and solutions? Don't miss out on your chance to get up to speed quickly on fundamental, in-depth information on some of the hottest topics in our library of content.
Let Your Users Reset Their Own Passwords: Free Download Try a 30 day free trial of Desktop Authority Password Self-Service – it provides an easy-to-use, robust system for allowing users to reset their own forgotten passwords or locked accounts.
Get Windows IT Pro & Mark Minasi’s Favorite Power Tools Guide Order Windows IT Pro now and get "More of Mark Minasi's Favorite Power Tools"--a in-depth guide to the most useful Windows commands --FREE with your paid order! Subscribe today, and save 58% off the cover price!
Deep Dive into VMware vSphere, eLearning Series Join John Savill to explore the major functionality capabilities of the vSphere virtualization platform, including identification of the changes from ESX 3.5.
)
Register
Sören Schlegel May 07, 2001