SQL Server Performance Forum – Threads Archive

Database Market SUSPECT

How to recovery ?
SQL Server 7.0 starting up database ‘DATA’
opening file d:dadosdataDATA_data.mdf.
opening file d:dadosdatamsdblog.ldf
opening file d:dadosdata
orthwnd.ldf
opening file d:dadosdatapubs_log.ldf
opening file d:dadosdataDATA_log.ldf
bypassing recovery for database ‘DATA’ because it is market SUSPECT
I don’t have backup. Tanks

These are my notes for suspect databases, not all tested – mainly for 2k How to recover a suspect database. Top Tips 1. If the sytem dbs are ok but the users dbs are suspect maybe the log drive didn’t boot on server reboot,
or the log drive has become disconnected.
Reconnect the log drive and restart the server
2. If you’ve lost the log drive for ever or just have the mdf file use
EXEC sp_attach_single_file_db @dbname = ‘pubs’,
@physname = ‘c

rogram FilesMicrosoft SQL ServerMSSQLDatapubs.mdf’
Format is sp_attach_single_file_db {dbname},{physical location of mdf file}
It says the database should have been detached properly but experience indicates this is not always the case.
This command rebuilds a new log file
———————————————————–
See also:- sp_add_log_file_recover_suspect_db
sp_add_data_file_recover_suspect_db Use these where a database has been marked suspect due to insuffcient free space to expand
check out bol for details
————————————————————
Removing the suspect status sp_resetstatus dbname This changes the status bit in sysdatabases .. the effective command is :- update master.dbo.sysdatabases set status=status^256 where name= dbname Emergency mode… ( if it’s all really bad ) Set the status of the database to 32768, this should allow you the chance to dts/bcp out data from the database.
( not tested )
—————————————————————————————–
If you haven’t a recent backup then you can do the following. Look in your logs first, to see if is autorecovering.
If nothing is in –the logs, run sp_who2 to see if there is a rollback on the db in question.
If there is leave it it will ‘hopefully’ recover. If not, and as reset status will not work you’ll have to set the db to emergency mode update sysdatabases set status = 32768 Then set the db to single user
exec sp_dboption ‘yourdb’, ‘single user’, ‘true’
Then run dbcc checkdb (‘yourdb’, REPAIR_REBUILD)
Set single user to false.
—
–P.S Don’t reboot the server or restart SQL!!
—————————————————————————————————-

Also take help from: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3965 Luis Martin
Moderator
SQL-Server-Performance.com

Take a look at Books online for SP_RESETSTATUS for more information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>