February 16, 2009 at 2:57 am
The Gurus,
I have read that when a database is marked as suspect that it implies that there is a problem with the database. On the server that I administer, some databases changes status from NULL to SUSPECT to ONLINE. They do function well after being marked suspect and they could later appeared either as NULL or ONLINE. Below is the statement that I copied from the server. When does a database become corrupt or unusuable and how do you know?
NULL
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Latin1_General_CI_AS, SQLSortOrder=0, IsAutoClose, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled
Status=SUSPECT, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, IsAutoClose, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled
I ran sp_helpdb to get the message below. What other stored procedure or any step could be taken to discover if a database is corrupt?
Thanks in advance.
Regards,
Sahoong.
February 16, 2009 at 3:29 am
to check for database corruption run a DBCC CheckDB first
DBCC checkdb('YourDB') WITH ALL_ERRORMSGS, NO_INFOMSGS
The results will determine how badly the database is corrupted, then there are a number of options for fixing it
Do you have a good backup you can restore from?
February 16, 2009 at 4:50 am
Yes, as advised run the above command against your database and post the messages here. We can help you further.
February 16, 2009 at 5:39 am
After running the query, the only response that I got is command(s) completed successfully.
Like I mentioned, the database doesn't seem to have any problem but they are marked as suspects sometimes. Is there any other way to determine if a database is corupt or unusable? From my little experience SUSPECT doesn't really mean that a database is corrupt or what do you think?
Thanks.
February 16, 2009 at 5:39 am
After running the query, the only response that I got is command(s) completed successfully.
Like I mentioned, the database doesn't seem to have any problem but they are marked as suspects sometimes. Is there any other way to determine if a database is corupt or unusable? From my little experience SUSPECT doesn't really mean that a database is corrupt or what do you think?
Thanks.
February 16, 2009 at 5:53 am
sahoong (2/16/2009)
After running the query, the only response that I got is command(s) completed successfully.Like I mentioned, the database doesn't seem to have any problem but they are marked as suspects sometimes. Is there any other way to determine if a database is corupt or unusable? From my little experience SUSPECT doesn't really mean that a database is corrupt or what do you think?
Thanks.
According to BOL:
"SUSPECT
At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem"
Don't include "NO_INFOMSGS" and run? OR you can check into more granular objects, DBCC CHECKFILEGROUP and check the filegroups integrity may be?
February 16, 2009 at 6:28 am
did you try reading this
http://www.sqlservercentral.com/articles/Administering/unmarksuspect/137/
Terry
February 16, 2009 at 8:28 am
Check the sql error log. If the DBs are getting marked suspect, there will be entries in there saying why.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply