Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 database state

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

Posted - 2011-04-11 : 10:37:21
see here:
http://www.windowsitpro.com/article/john-savills-windows-faqs/my-sql-server-database-has-been-marked-quot-suspect-quot-what-can-i-do-


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-15 : 15:01:02
Files Missing then database state is now Suspect Mode

Regards,
Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-17 : 14:02:58
Example

NOT 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 mode


Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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 Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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.databases
you will find it its state as RECOVERY_PENDING

--------------------------
http://connectsql.blogspot.com/
Go to Top of Page
   

- Advertisement -