May 5, 2009 at 10:18 pm
Dear Experts,
I have found the below error report when i ran a server reports for management in management studio.
Total Number of Severe Errors:*9
Error Severity Count First OccurrenceLast Occurrence Last Error Message
9100 23 9 5/5/2009 05:24:39 PM5/5/2009 05:33:48 PM Possible index corruption
detected. Run DBCC CHECKDB.
Please find the attached as well..
So, according to the above error report i just ran "DBCC CHECKDB ('ApplDB', NOINDEX)" (on the test database)- first level
The result shows number of rows info. I Couldnot understand what the result says.
Can anyone please look into and help me what the result stands for and what should i do now.
--------------------------------------------------
DBCC results for 'eCOEApplDB'.
Warning: NO_INDEX option of checkdb being used. Checks on non-system indexes will be skipped.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 12622 rows in 202 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 749 rows in 46 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 799 rows in 75 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 3 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 12622 rows in 227 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 749 rows in 49 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 799 rows in 13 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 33 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 614 rows in 4 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 3394 rows in 78 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 30450 rows in 574 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 112 rows in 19 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 8666 rows in 205 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 13796 rows in 100 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 11116 rows in 4991 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 28 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
-------------------------------------------------------------------
As we have never used CHECKDB from past 14 months and we dont know that as well.
Please provide me the complete analysis or any links to understand CHECKDB and with pain areas and so and so.
Help me please...!!
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 5, 2009 at 10:23 pm
use [dbname]
DBCC CHECKDB
now u will get the DB related results
May 5, 2009 at 11:17 pm
Thanks Varun,
But even after running the same what you have provided, the result is same and i believe there is no change in th result as well.
Can you please provide how this works and what could be the understanding.
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 6, 2009 at 2:04 am
Please run a full checkDB on that database. By leaving out the indexes you may be missing corruption, especially since the error says that it's possible index corruption.
DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS
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
May 6, 2009 at 4:44 am
Thanks Gail,
But i received only this : (after running the command - which you have given.)
Command(s) completed successfully.
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 6, 2009 at 4:51 am
That means there are no error messages. 🙂
You need not worry and eventhough you haven't ran DBCC CHECKDB for last 14 months doesn't mean that there may be any corruption.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 6, 2009 at 5:12 am
Ohh Yeah thanks... 🙂
But i wish i would have seen some errors and fix them. So that i would have learnt new thing..
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 6, 2009 at 6:17 am
SQL-KV (5/6/2009)
But i received only this : (after running the command - which you have given.)Command(s) completed successfully.
That means there's no corruption, which is a good thing.
But i wish i would have seen some errors and fix them. So that i would have learnt new thing..
Believe me, you do not want to learn about corruption by having to deal with it. Paul Randal's blog[/url] has some sample corrupt DBs that you can play with, and there's an article here that goes over some of the more common corruptions - http://www.sqlservercentral.com/articles/65804/
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
May 6, 2009 at 10:34 am
since it's a possible index corruption you should rebuild your indexes and schedule it at least once a week via maintenance plan.
May 6, 2009 at 11:52 am
Another option when you get a index error message is to just drop and re-create the index. If it is a clustered index then you may have more of an issue.
May 6, 2009 at 12:01 pm
Henry Treftz (5/6/2009)
Another option when you get a index error message is to just drop and re-create the index.
No, it depends on the level of corruption on the indexes. Dropping and recreating is not always a solution.
May 6, 2009 at 12:08 pm
AlexSQLForums (5/6/2009)
since it's a possible index corruption you should rebuild your indexes and schedule it at least once a week via maintenance plan.
Choosing index rebuild or not depends on the level of fragmentation too. It could be once a day or once a week and also it should not depend on database corruption but the amount of fragmentation. Corruption should a,ways be resolved either by restoring or repairing or building the objects
Ideally you should be running integrity checks as part of your maintenance plan.
May 6, 2009 at 12:12 pm
SQL-KV (5/6/2009)
Ohh Yeah thanks... 🙂But i wish i would have seen some errors and fix them. So that i would have learnt new thing..
Definitely you should practice, even when you don't get to face them quite frequently. A DBA should always be ready for the unexpected and resolve it efficiently that's it it makes them so special to an Organization :).
Corrupting a databases is easy but to secure them is challenging, you can corrupt in many ways but the popular method is to use the text editor.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply