Suspect database

Question

Post reply

Suspect database

sahana

SSCrazy

Points: 2530
More actions
September 29, 2004 at 3:32 pm

#63440

I could not open my database.It says its Suspect and could not open and not displaying any of the objects
What should be the reason and what is steps i can do make it up and running.
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Douglas De Ivey Old Hand Points: 396 More actions · Answer 1

Is your disk drive full?

If so then try the procedure described in Books Online

Look for "Resetting the Suspect Status" in the index.

If the disk is not full then you may have a corrupted database and you should not do this above procedure without doing further checks as you could make the situation worse.

sahana SSCrazy Points: 2530 More actions · Answer 2

My disk is not full and there is lots of space available.what should be the other reasons.

sahana SSCrazy Points: 2530 More actions · Answer 3

I dont have a backup either.Can i take a backup now?

Thanks.

Douglas De Ivey Old Hand Points: 396 More actions · Answer 4

Sorry but it doesn't look good. This usually requires the database to be restored from backup.

The only other 'easy' fix assumes that the database file has been renamed or inacessable for other reasons such as security settings.

Check the database and log files are accessible.

They will probably a folder something like be in X:\mssql\data or X:\mssql\logs.

Failing that, there are a few other things that might help to to retrieve the contents of the db

sahana SSCrazy Points: 2530 More actions · Answer 5

The database abd log files are accessible.So what should i do now? Please guide me from here.

Thanks.

sahana SSCrazy Points: 2530 More actions · Answer 6

I'm sorry for the typos.The database file and log files are accessible.So what should i do now? Please guide me from here.

Douglas De Ivey Old Hand Points: 396 More actions · Answer 7

Looks like the DB is damaged.

See this thread

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=92&messageid=99258

Read the whole thread, but I think you shoiuld start at the line:

"If you haven't a recent backup then you can do the following. Look in your logs first, to see if is autorecovering."

Once you set emergencey mode, you should be able to select data out of the database.

The next bit in teh thread says how to attempt the repair, but it might not work.

Douglas De Ivey Old Hand Points: 396 More actions · Answer 8

Just noticed teh thread it not quire right

update sysdatabases set status = 32768

should have

where name = 'yourdbname'

added to it or it will set all databases so emergency

sahana SSCrazy Points: 2530 More actions · Answer 9

WHen i ran EXEC sp_resetstatus 'dbname' i got the below message.

Prior to updating sysdatabases entry for database 'ihatespamdb', mode = 0 and status = 4194333 (status suspect_bit = 0).

No row in sysdatabases was updated because mode and status are already correctly reset. No error and no changes made.

Could we make out anything from this message.

Thanks.

Douglas De Ivey Old Hand Points: 396 More actions · Answer 10

It seems to suggest that the db is not suspect (?)

Close Enterprise manager and then restart it to double check the status.

If it is still suspect try

update sysdatabases set status = 32768 where name = 'yourdbname'

to force emergency mode.

If the database is accessible, I would then copy everything out of it into a new database

You can try to fix the db as described in the thread I mentioned with:

---------------------------------------

Then set the db to single user

exec sp_dboption 'yourdb', 'single user', 'true'

Then try a repair with

dbcc checkdb ('yourdb', REPAIR_REBUILD)

Set single user to false.

---------------------------------------

For more help try searching this site for 'suspect' or google for 'SQL Server suspect database'.

sahana SSCrazy Points: 2530 More actions · Answer 11

update sysdatabases set status = 32768 where name = 'pubs'

When i run the above command I got the following server message.

Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.

How can I enable ad hoc updates on the server.

N. Subramanian SSC Enthusiast Points: 178 More actions · Answer 12

sp_dboption 'allow updates',true

reconfigure with override

Otto Fraunbaum SSC Rookie Points: 48 More actions · Answer 13

I had a similar problem. In the master sysdatabases table is a status column. What's the value now ? Onetime I changed the value of the status column to the same value as the other user databases have (16 at my Sql Server). Afterwards the DB was accessible. You must set the option 'allow modification to the systems table directly' before you ca do this. I don't think, that MS would recommend this. In my case it worked. You should make a backup of the system databases, before you try this. No guarantee that this will work.

OF