| Author |
Topic |
|
raul11
Starting Member
48 Posts |
 Posted - 2011-04-11 : 10:19:10
|
| why does a database go into suspect mode? can it be recoverd & how |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-11 : 10:45:11
|
| NB, that article is ONLY for SQL 2000. If you are running SQL 2005 or above then it should be ignored.The first step in recovering a suspect database is to identify why it's suspect. Check the SQL error logs, there will be entries indicating why SQL has marked the DB suspect. If this is not a theoretical question, post those errors here and we'll help you further.--Gail ShawSQL Server MVP |
 |
|
|
Jahanzaib
Posting Yak Master
115 Posts |
Posted - 2011-04-15 : 15:01:02
|
| Files Missing then database state is now Suspect ModeRegards,Syed Jahanzaib Bin HassanMCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-16 : 06:10:29
|
| Nope. Files missing results in recovery_pending, not suspect.--Gail ShawSQL Server MVP |
|
|
|
Jahanzaib
Posting Yak Master
115 Posts |
Posted - 2011-04-17 : 14:02:58
|
| ExampleNOT True,Database cant go in recovery mode move because where it will get the missing data of missing file,You have a database with multiples database files on different disk then sql server service is stoped and then 1 disk is removed with 1 file then sql server serive is started then database must be in suspect modeRegards,Syed Jahanzaib Bin HassanMCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBAMy Blogwww.aureus-salah.com |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-17 : 14:44:56
|
quote:
Originally posted by Jahanzaib
You have a database with multiples database files on different disk then sql server service is stoped and then 1 disk is removed with 1 file then sql server serive is started then database must be in suspect mode
Nope. That results in Recovery pending, not suspect. (in SQL 2005 and above that is, in 2000 and below the states weren't as clearly defined)Suspect requires that crash recovery starts (which it can't if there are missing files) and then fails because of data or log corruption.http://sqlinthewild.co.za/index.php/2010/06/29/does-a-missing-data-file-send-a-database-suspect/Yes, they result in much the same thing, a database that is unavailable, but the point is that sending someone that has a suspect database off on a search for missing files is a waste of time, it won't be the cause.--Gail ShawSQL Server MVP |
|
|
|
Jahanzaib
Posting Yak Master
115 Posts |
Posted - 2011-04-18 : 14:13:10
|
| When SQL server service will down and you delete a file then database must be in suspect mode in the next start of sql server service Regards,Syed Jahanzaib Bin HassanMCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBAMy Blogwww.aureus-salah.com |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-18 : 15:30:13
|
| If you delete (or rename) a file while SQL is down (or the DB offline, same effect exactly), when it restarts the database will be recovery pending, not suspect. Read my blog post, test it out if you wish. To get a database suspect, the file must be damaged in some way and SQL must encounter that damage during a crash recovery or a transaction rollback. A missing file does not qualify for that.--Gail ShawSQL Server MVP |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-04-19 : 03:51:16
|
| Gila is right, test yourself, stop SQL Server Services then rename your MDF or LDF file, restart your services and execute this SELECT name,state_desc fROM sys.databasesyou will find it its state as RECOVERY_PENDING--------------------------http://connectsql.blogspot.com/ |
|
|
|
|
database state