Database suspect-find recovery model | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Database suspect-find recovery model

hello, hw to find the recovery model of a database if its in suspect mode . thnx in advance
First you must reset the SUSPECT status with SP_RESETSTATUS statement as per the definition in Books online and then SP_DBOPTION to know the database mode; 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.
Sathya, status of my DB in sysdatabase was 16 , when it gone suspect also i can see same satus,16. when i tried to issue command sp_resetstatus ‘Product’, i got followin mesg . "Prior to updating sysdatabases entry for database ‘products2’, mode = 0 and status = 16 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already correctly reset. No error and no changes made." i need to find the recovery model of a DB, even its in suspect mode.the idea is then i wil decide which recovery plan to be used.if its ‘simple’, i need to truncate log first.i faced this problem once my DB was in suspect mode.Please advice me Rajiv
SQL-DBA
FOllow as per this MSDN articlehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_494j.asp to reset the suspect status. To know the database status, run: SELECT dbpropertyex("database", "recovery") Until unless the suspect status is set as per the books online, it is not possible to set or even know the database settings. 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.
Sathya,<br /><br />reseting the DB is not at al a solution.it needs restart of the server <img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />( .<br />could you pls try getin the recory model in ur machine,while db is in suspect mode??<br /><br />wht m using now is, just try …<br />backup log database to c:ackupfile.back with no_truncate<br />this wil tell , my datanase is in simple recovery model. any other suggsetion u hav, pls let me know.<br /><br />thx Sathya<br /><br />Rajiv<br />SQL-DBA
]]>