| Author |
Topic |
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
 Posted - 2010-03-17 : 23:22:50
|
| Our environment is 95 percent Production Server Maintenance.I haven't executed the CheckDB process on the individual databases for a long time. What is the best practise to implement the CHECKDB process on database(s), I have checked it as this SP related to CHECKDB driven for more than hours and should I implement it as an Agent Job?If so, how to view the Errors that were generated as a part of this execution.Is there any thing that I miss in manipulating this CHECKDB and what are the other repurcussions.Even if the errors being generated how to ANALYSE them.Can anyone reply to this. Many thanks. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-17 : 23:29:28
|
| I'm not sure I completely understand what you're saying. But if CHECKDB is running too long for production, restore a backup to a DEV server and run it there.If errors are generated, post 'em here. We'll be happy to help you analyze them |
 |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2010-03-18 : 00:20:16
|
| Now may I ask you how and where I need to point the errors for the following results.DBCC results for 'EDENGARDEN'.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 9135 rows in 54 pages for object "sys.sysrowsetcolumns".DBCC results for 'sys.sysrowsets'.There are 586 rows in 4 pages for object "sys.sysrowsets".DBCC results for 'sysallocunits'.There are 597 rows in 7 pages for object "sysallocunits".DBCC results for 'sys.sysfiles1'.There are 2 rows in 1 pages for object "sys.sysfiles1".DBCC results for 'sys.syshobtcolumns'.There are 9135 rows in 56 pages for object "sys.syshobtcolumns".DBCC results for 'sys.syshobts'.There are 586 rows in 4 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 597 rows in 5 pages for object "sys.sysserefs".DBCC results for 'sys.sysowners'.There are 17 rows in 1 pages for object "sys.sysowners".DBCC results for 'sys.sysprivs'.There are 123 rows in 1 pages for object "sys.sysprivs".DBCC results for 'sys.sysschobjs'.There are 4187 rows in 75 pages for object "sys.sysschobjs".DBCC results for 'sys.syscolpars'.There are 7550 rows in 120 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 0 rows in 0 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 949 rows in 16 pages for object "sys.sysidxstats".DBCC results for 'sys.sysiscols'.There are 2188 rows in 15 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 5083 rows in 198 pages for object "sys.sysobjvalues".DBCC results for 'sys.sysclsobjs'.There are 14 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".DBCC results for 'sys.syssingleobjrefs'.There are 133 rows in 1 pages for object "sys.syssingleobjrefs".DBCC results for 'sys.sysmultiobjrefs'.There are 429 rows in 3 pages for object "sys.sysmultiobjrefs".DBCC results for 'sys.sysdbfiles'.There are 2 rows in 1 pages for object "sys.sysdbfiles".DBCC results for 'sys.sysguidrefs'.There are 0 rows in 0 pages for object "sys.sysguidrefs".DBCC results for 'sys.sysqnames'.There are 91 rows in 1 pages for object "sys.sysqnames".DBCC results for 'sys.sysxmlcomponent'.There are 93 rows in 1 pages for object "sys.sysxmlcomponent".DBCC results for 'sys.sysxmlfacet'.There are 97 rows in 1 pages for object "sys.sysxmlfacet".DBCC results for 'sys.sysxmlplacement'.There are 17 rows in 1 pages for object "sys.sysxmlplacement".DBCC results for 'sys.sysobjkeycrypts'.There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".DBCC results for 'sys.sysasymkeys'.There are 0 rows in 0 pages for object "sys.sysasymkeys".DBCC results for 'sys.syssqlguides'.There are 0 rows in 0 pages for object "sys.syssqlguides".DBCC results for 'sys.sysbinsubobjs'.There are 0 rows in 0 pages for object "sys.sysbinsubobjs".DBCC results for 'SITER'.There are 1 rows in 1 pages for object "SITEID".DBCC results for 'SECURE'.There are 5 rows in 5 pages for object "SERCURE".DBCC results for 'SHART5'.There are 11974 rows in 1712 pages for object "CHART5".DBCC results for 'UPDATEACCOUNTS'.There are 41 rows in 1 pages for object "UPDATECOUNTS".DBCC results for 'GCHRTLNK'.There are 0 rows in 0 pages for object "GCHRTLNK".DBCC results for 'ADDRIN'.There are 4033 rows in 83 pages for object "ADDR".DBCC results for 'LETTER'.There are 1 rows in 1 pages for object "LETTER".DBCC results for 'PHYLIST'.There are 0 rows in 0 pages for object "PHLIST".DBCC results for 'TIPCASE'.There are 0 rows in 0 pages for object "TPCCASE".DBCC results for 'GRADES'.There are 0 rows in 0 pages for object "GRADE".DBCC results for 'CLINMATETIME'.There are 0 rows in 0 pages for object "NONCLINICALTIME".DBCC results for 'GENERT3'.There are 40 rows in 2 pages for object "GENCMT3".DBCC results for 'ECONFIG'.There are 1 rows in 1 pages for object "SCONFIG".DBCC results for 'RECYCLELOG'.There are 1 rows in 1 pages for object "RECALLLOG".DBCC results for 'FADMISN'.There are 0 rows in 0 pages for object "ADMISHN".DBCC results for 'NCLM2'.There are 1353 rows in 272 pages for object "NHSCLM2".DBCC results for 'WINLOGIN'.There are 23 rows in 3 pages for object "WINLOGON".DBCC results for 'TNOTE'.There are 0 rows in 0 pages for object "LNOTE".DBCC results for 'PATFRAMES'.There are 0 rows in 0 pages for object "PATXRAYS".DBCC results for 'NODAYWORKED'.There are 0 rows in 0 pages for object "OPDAYWORKED".DBCC results for 'TRANSFOR'.There are 13031 rows in 854 pages for object "TRANS4".DBCC results for 'GRAPHICTRIG'.There are 27 rows in 1 pages for object "GRAPHIC".DBCC results for 'PLANMETHODS'.There are 0 rows in 0 pages for object "PLANMETH".DBCC results for 'CHRTCRUT'.There are 0 rows in 0 pages for object "CHRTCMT3".DBCC results for 'RECALLCLIMATE'.There are 1 rows in 1 pages for object "RECALLTYPE".DBCC results for 'GLE'.There are 222 rows in 7 pages for object "GL4".DBCC results for 'APPBROOK'.There are 1 rows in 1 pages for object "APPBOOK".DBCC results for 'NTORTHOT'.There are 0 rows in 0 pages for object "ORTHO".DBCC results for 'CLEANCODE'.There are 0 rows in 0 pages for object "CLINCODE".DBCC results for 'MYMETHOD'.There are 4 rows in 1 pages for object "PMETHOD".DBCC results for 'GRDAUDATE'.There are 0 rows in 0 pages for object "GRDAUDLG".DBCC results for 'JOURNALIST'.There are 0 rows in 0 pages for object "JOURNAL".DBCC results for 'CLINICIA'.There are 1 rows in 1 pages for object "CLINIC".DBCC results for 'PAYBOLD'.There are 135 rows in 4 pages for object "PAYLMT".DBCC results for 'REFER'.There are 0 rows in 0 pages for object "REF3".DBCC results for 'APPTS'.There are 1 rows in 1 pages for object "APPT6".DBCC results for 'GLDEPT'.There are 0 rows in 0 pages for object "GLDEPART".DBCC results for 'PRESCT'.There are 0 rows in 0 pages for object "PRESCPT3".DBCC results for 'GCHART'.There are 0 rows in 0 pages for object "GRDCHART".DBCC results for 'LABITEMS'.There are 0 rows in 0 pages for object "LABITEM5".DBCC results for 'APTDTL'.There are 4356 rows in 84 pages for object "GLDTL3".DBCC results for 'CLEANCON'.There are 0 rows in 0 pages for object "CLNCCON".DBCC results for 'NOLOG'.There are 4949 rows in 106 pages for object "WLLOG".DBCC results for 'REFERREND'.There are 0 rows in 0 pages for object "REFERRAL".DBCC results for 'LOCATIONS'.There are 2472 rows in 99 pages for object "LOCATION".DBCC results for 'TRAVELOG'.There are 71 rows in 2 pages for object "TRANSLOG".DBCC results for 'SITER'.There are 0 rows in 0 pages for object "SITE".DBCC results for 'MYAUDIT'.There are 0 rows in 0 pages for object "CNAUDIT".DBCC results for 'GRDINSTUTE'.There are 0 rows in 0 pages for object "GRDINST".DBCC results for 'NOMONTH'.There are 132 rows in 3 pages for object "GLMONTH".DBCC results for 'PRAWNHRT'.There are 0 rows in 0 pages for object "PRIOCHRT".DBCC results for 'XMF'.There are 0 rows in 0 pages for object "DMF".DBCC results for 'WIGCLASS'.There are 2 rows in 1 pages for object "WLSCLASS".DBCC results for 'REFLOSS'.There are 0 rows in 0 pages for object "REFLOG".DBCC results for 'UNPLAN'.There are 0 rows in 0 pages for object "PAYPLAN".DBCC results for 'TRALLOC'.There are 1619 rows in 63 pages for object "TRNSALLO".DBCC results for 'COCREFS'.There are 9 rows in 1 pages for object "COCREFSRC".DBCC results for 'DEBTACCTS'.There are 830 rows in 25 pages for object "DEBTACC3".DBCC results for 'PRNPAL'.There are 0 rows in 0 pages for object "PRNPFILE".DBCC results for 'SNAPSHOTS'.There are 0 rows in 0 pages for object "SNAPSHOT".DBCC results for 'GRDLANK'.There are 0 rows in 0 pages for object "GRDLNK".DBCC results for 'WLSFLASS'.There are 0 rows in 0 pages for object "WLSCLASS2".DBCC results for 'REFTRT'.There are 0 rows in 0 pages for object "REFSRC".DBCC results for 'CONSENTYEA'.There are 0 rows in 0 pages for object "CONSENTLOG".DBCC results for 'GLOBALCONFIGS'.There are 1 rows in 1 pages for object "GLOBALCONFIG".DBCC results for 'GRDTRST'.There are 0 rows in 0 pages for object "GRDSCLE".DBCC results for 'REFT99'.There are 0 rows in 0 pages for object "REFTO3".DBCC results for 'STPCRDT03'.There are 0 rows in 0 pages for object "STPCRDT".DBCC results for 'LOGONHISTORY'.There are 0 rows in 0 pages for object "LOGHISTORY".DBCC results for 'XYZRBORULE'.There are 0 rows in 0 pages for object "XYZRULE2".DBCC results for 'TRNSTRT'.There are 0 rows in 0 pages for object "TRNSDTL3".DBCC results for 'GRDSTPAGE'.There are 0 rows in 0 pages for object "GRDSTAGE".DBCC results for 'RTFDBS'.There are 0 rows in 0 pages for object "RTFDB".DBCC results for 'GOALS'.There are 0 rows in 0 pages for object "GOAL".DBCC results for 'STYPE99'.There are 0 rows in 0 pages for object "STYPE4".DBCC results for 'PERIWHO'.There are 0 rows in 0 pages for object "PERIOWHO".DBCC results for 'PRBNTRY'.There are 0 rows in 0 pages for object "PROBENTRY".DBCC results for 'SCEPTOR4'.There are 753 rows in 108 pages for object "DEBTOR4".DBCC results for 'COTTS'.There are 0 rows in 0 pages for object "COT".DBCC results for 'SCANIN'.There are 0 rows in 0 pages for object "SCANMODE".DBCC results for 'R23BLOCK'.There are 0 rows in 0 pages for object "REPBLOCK".DBCC results for 'UCINFIG3'.There are 1 rows in 1 pages for object "UCONFIG3".DBCC results for 'SUBSTRT'.There are 0 rows in 0 pages for object "SUBSCRBR".DBCC results for 'APAT99'.There are 2 rows in 1 pages for object "APPTAGE6".DBCC results for 'LITEXT'.There are 0 rows in 0 pages for object "LTRTEXT".DBCC results for 'INSTALY'.There are 0 rows in 0 pages for object "INSTALM".DBCC results for 'REPDT'.There are 0 rows in 0 pages for object "REPDATA".DBCC results for 'SUBS99'.There are 0 rows in 0 pages for object "SUBSCRPT".DBCC results for 'MAILURG'.There are 0 rows in 0 pages for object "MAILMRG".DBCC results for 'PROBLEMETS'.There are 0 rows in 0 pages for object "PROBLEMITEM".DBCC results for 'APATNO'.There are 7 rows in 1 pages for object "APPTRES".DBCC results for 'SUPP6'.There are 0 rows in 0 pages for object "SUPPACC".DBCC results for 'INVLDNT97'.There are 9903 rows in 1415 pages for object "INVLDNT5".DBCC results for 'REGROUP'.There are 0 rows in 0 pages for object "REPGROUP".DBCC results for 'CRTYPE'.There are 0 rows in 0 pages for object "CREDTYPE".DBCC results for 'PFEE09'.There are 37251 rows in 12417 pages for object "PFEES3".DBCC results for 'BINOBJECT'.There are 9 rows in 3 pages for object "BINOBJECT".DBCC results for 'PROBNO'.There are 0 rows in 0 pages for object "PROBLIST".DBCC results for 'RESPYES'.There are 0 rows in 0 pages for object "RESPNSE4".DBCC results for 'CUSTT'.There are 0 rows in 0 pages for object "CUSTACC".DBCC results for 'BLOCKT'.There are 0 rows in 0 pages for object "BLOCK6".DBCC results for 'PROFICENT'.There are 1 rows in 1 pages for object "PROFCENT".DBCC results for 'SUPPLIERS'.There are 0 rows in 0 pages for object "SUPPLIER".DBCC results for 'RESPTXTS'.There are 0 rows in 0 pages for object "RESPTXT".DBCC results for 'CASENOTES'.There are 0 rows in 0 pages for object "CASENOTE".DBCC results for 'CUSTOMER'.There are 0 rows in 0 pages for object "CUSTOMER".DBCC results for 'RNIPARAM'.There are 0 rows in 0 pages for object "RNIPARAMSET".DBCC results for 'YID'.There are 44 rows in 1 pages for object "UID".DBCC results for 'SUPERCODE'.There are 0 rows in 0 pages for object "SUPRCODE".DBCC results for 'ICONFIG'.There are 2 rows in 1 pages for object "MCONFIG".DBCC results for 'PROVIDERS'.There are 21 rows in 2 pages for object "PROVIDER".DBCC results for 'MEDHIST'.There are 0 rows in 0 pages for object "MEDHIST".DBCC results for 'CUSTSACC'.There are 0 rows in 0 pages for object "CUSTSCCAT".DBCC results for 'CHARTMARLOINE'.There are 0 rows in 0 pages for object "CHARTMACROLINE".DBCC results for 'RNIPATT'.There are 0 rows in 0 pages for object "RNIPATIENT".DBCC results for 'TESTS'.There are 0 rows in 0 pages for object "TEST".DBCC results for 'CAT23'.There are 0 rows in 0 pages for object "CAT2".DBCC results for 'UPREFER'.There are 53 rows in 1 pages for object "UPREF".DBCC results for 'CUSTSCYCLIST'.There are 0 rows in 0 pages for object "CUSTSCLIST".DBCC results for 'SCREENER'.There are 0 rows in 0 pages for object "SCREEN".DBCC results for 'USERAPP'.There are 3 rows in 1 pages for object "USERAPPTBK".DBCC results for 'CATEGC'.There are 332 rows in 7 pages for object "CATEGCN2".DBCC results for 'RNIQUEST'.There are 0 rows in 0 pages for object "RNIQUESTION".DBCC results for 'ELIG'.There are 0 rows in 0 pages for object "ELIGCAT".DBCC results for 'DATACON'.There are 0 rows in 0 pages for object "DATACONFIG".DBCC results for 'TPRINT'.There are 4 rows in 1 pages for object "VPRINT2".DBCC results for 'CHARTMACROPLAN'.There are 0 rows in 0 pages for object "CHARTMACROPLAN".DBCC results for 'PATROPE3'.There are 934 rows in 76 pages for object "PATIENT3".DBCC results for 'CATEINN'.There are 69 rows in 2 pages for object "CATEGCON".DBCC results for 'YFEE'.There are 171 rows in 16 pages for object "SFEE".DBCC results for 'EMAILUY'.There are 0 rows in 0 pages for object "EMAILTX".DBCC results for 'DCONFIGS'.There are 1 rows in 1 pages for object "DCONFIG3".DBCC results for 'SCREEND'.There are 0 rows in 0 pages for object "SCREEND".DBCC results for 'SCHEDULEDDATA'.There are 0 rows in 0 pages for object "SCHEDULEDEVENTDATA".DBCC results for 'CATEGORY'.There are 0 rows in 0 pages for object "CATEGOR2".DBCC results for 'RREASON'.There are 0 rows in 0 pages for object "RNIREASON".DBCC results for 'DEBTTYPER'.There are 0 rows in 0 pages for object "DEBTTYPE".DBCC results for 'CHARTSSHOT'.There are 0 rows in 0 pages for object "CHARTSNAPSHOT".DBCC results for 'SCHEDULEDS'.There are 0 rows in 0 pages for object "SCHEDULEDEVENTS".DBCC results for 'TENOTES3'.There are 0 rows in 0 pages for object "PTNOTES3".DBCC results for 'CATEGORIES'.There are 7 rows in 1 pages for object "CATEGORY".DBCC results for 'ERRLOGTAB'.There are 0 rows in 0 pages for object "ERRLOG".DBCC results for 'DENCAN4'.There are 17 rows in 6 pages for object "DENTIST4".DBCC results for 'SCREENLISTA'.There are 0 rows in 0 pages for object "SCREENLISTDATA".DBCC results for 'RITA'.There are 11 rows in 2 pages for object "ROTA".DBCC results for 'MOESCORE'.There are 0 rows in 0 pages for object "MOESCORE".DBCC results for 'PUBLIC'.There are 0 rows in 0 pages for object "PUBLICTN".DBCC results for 'UCONFIGS'.There are 0 rows in 0 pages for object "LCONFIG".DBCC results for 'CATGUYU5'.There are 455 rows in 16 pages for object "CATGRYV5".DBCC results for 'GOLDCAT'.There are 0 rows in 0 pages for object "GOLD".DBCC results for 'FEEAUDITS'.There are 0 rows in 0 pages for object "FEEAUDIT".DBCC results for 'QUERYS'.There are 0 rows in 0 pages for object "QUERY".DBCC results for 'SCRIPTS'.There are 0 rows in 0 pages for object "SCRIPT".DBCC results for 'DSIT4'.There are 261 rows in 48 pages for object "DSERV4".DBCC results for 'NCTROSON'.There are 0 rows in 0 pages for object "NOTREASON".DBCC results for 'sys.queue_messages_1977058079'.There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".DBCC results for 'ZREPORT'.There are 60 rows in 2 pages for object "VREPORT".DBCC results for 'sys.queue_messages_2009058193'.There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".DBCC results for 'VRHRTINS'.There are 0 rows in 0 pages for object "GCHRTINS".DBCC results for 'TYS_SD'.There are 0 rows in 0 pages for object "SDS_SD".DBCC results for 'sys.queue_messages_2041058307'.There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".DBCC results for 'NOMASTER'.There are 0 rows in 0 pages for object "NHIMASTER".DBCC results for 'FILEINFOSYS'.There are 194 rows in 2 pages for object "FILEINFO".DBCC results for 'GOLDLOGS'.There are 0 rows in 0 pages for object "GOLDLOG".DBCC results for 'NHSCHDERES'.There are 0 rows in 0 pages for object "NHSCHD".DBCC results for 'WAITTIST'.There are 2030 rows in 82 pages for object "WAITLIST".DBCC results for 'RECALLLOG'.There are 134 rows in 5 pages for object "RECALL".CHECKDB found 0 allocation errors and 0 consistency errors in database 'EDENGARDEN'.DBCC execution completed. If DBCC printed error messages, contact your system administrator. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-18 : 00:25:07
|
| Put the CHECKDB into a SQL job and make sure to specify that the output gets logged. Otherwise you've lost the output when a failure occurs. If the SQL job ever fails, then view the job log.If you have data corruption and you don't catch it with DBCC CHECKDB, you could lose data. Depending upon how far your data corruption goes back, how bad it is, and how far your backups go back, you could lose a lot of data. Note that data corruption is rare. Most happens due to the disk errors.You don't need to analyze them, just post them to the Data Corruption forum for assistance (mostly from Gail) or get on the phone with Microsoft. My company has a support contract with Microsoft, so I immediately contact them for help due to how critical our data is. You should run DBCC CHECKDB on a regular basis, daily or maybe weekly. It's recommended to run it on a copy of the production database as the task is very I/O intensive. We have two systems where the task causes a massive slowdown, so we instead restore a copy of the production database to a dev server and then do it there. We have the whole process automated, so there are no manual steps.How often you run it depends on the criticality of your data, how much risk you can take, and how far back your backups go. My company is only allowed to retain 2 days of backups on many of our systems due to contractual reasons. Due to this, we run DBCC CHECKDB daily on those. If we didn't and did encounter data corruption where we'd lose data and it happened more than 2 days ago, we would not be able to recover that data from backups. That is 100% not acceptable to my company or our customers.How big is your database?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2010-03-18 : 00:33:22
|
| Many Thanks Tkizer and Russel |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-18 : 00:58:42
|
| I have two VLDBs. On the first, we only do it on Sundays and perform it directly on production. On the second, I have custom code that grabs the newest full backup, restores it to a test server and then runs DBCC CHECKDB. I believe that whole process takes 10-12 hours right now. The bigger the database gets, the longer it takes. It took 5-6 hours when I first implemented it. We run the process twice a week.When I run it directly on production, I use the PHYSICAL_ONLY option. I discussed it a bit with Microsoft engineers before adding that option. It's faster, and only misses the checks that would be due to actual SQL Server bugs (which are extremely rare).Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-18 : 01:05:18
|
| automated script restores backups on Saturday night and let it and the CheckDb fly for 12-14 hours. then the business analysts use it as a playground all week! lol.Never thought about the PHYSICAL_ONLY option. Have to lok at that. Also, I know that Paul R made a post a year or so back about checkdb and vldbs...i might need to go re-read that. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-18 : 01:17:57
|
| We're getting a new NetApp SAN this week. I'm hoping that I can run checkdb against clones on that thing -- since I'll be able to flex-clone anything in seconds or minutes (supposedly). Only concern is the I/O impact overall (of the checkdb).But if everything they've convinced me to believe is true, this is going to be like magic!(sorry dbalearner for the thread hijack) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-18 : 03:31:49
|
quote:
Originally posted by dbalearner
Now may I ask you how and where I need to point the errors for the following results.DBCC results for 'EDENGARDEN'.Service Broker Msg 9675, State 1: Message Types analyzed: 14.Service Broker Msg 9676, State 1: Service Contracts analyzed: 6....
@dbalearner : Add WITH ALL_ERRORMSGS, NO_INFOMSGS to your DBCC CHECKDB command. That will hide all that wibble, and thus you will have an empty report if there are no errors (but you will get ALL errors, rather than just the first few hundred, if there is ever a real problem).The other thing that I think is important is to make 100% sure that you get notification if there is an error. I have read on these forums about corruptions that also damaged the EMail system on servers, so DBCC CHECKDB had been reporting errors for days, but the EMail system had failed so they were not read  Either do a quick manual check of the error log each day (and on each server), or have something that is failsafe.We have a tool that collects all the error logs, strips out all the "expected" text like "CHECKDB found 0 allocation errors and 0 consistency errors in database 'EDENGARDEN'" and then emails whatever is left - which will be nothing if there are no errors.Alternative is to have it Email you on Success OR Failure - and then if you don't get the Email you go investigating. That wouldn't work for me, I would never remember how many emails I was supposed to get! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-18 : 11:32:44
|
quote:
Originally posted by russell
We're getting a new NetApp SAN this week.
We are using a NetApp SAN on the system I mention above that gets the automated restores/checkdb. We backup directly to the NetApp and then restore directly from it. So the file never moves around, we just read/write to it. This method doubled our backup time and even higher for the restore. So expect things to take longer with a NetApp rather than the local disk. I understand it has to do with Microsoft copy speeds.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-18 : 13:28:32
|
| wow. i can hardly wait lol. thx |
|
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2010-03-22 : 17:27:51
|
Fortunately we dont have VLDB's Max I can say is around 15 - 20 GB Size of some DB's.We too make alternative arrangements for the Reporting section as this is the gateway for the Application / Business Analysts to work on. I have included as a part of my Indexes checks purposes every week. However, there is no reporting on any such DB, but I do have some concerns while executing this during the Business hours and if I have to take it off I need to do it during After Business hours and sparsely I get the time since I got only just 4 - 5 hours left to do any such operation. So my concern is when is the best time to do the CheckDB;As I have directed, so was, I take on directly to the production environment with the DB which is mostly populated to the least but time is the criterion, so simultaneously I do it on the Development Box on procuring backup, But my question is, while executing the CheckDB there might be some transactions registered and these new transactions carries unawares with the changes made to them. So what should I do in this situation.I know it is just giving check on DB's and these results will be basic ground for analysation.Is there any alternative method to implement directly on to production with minimal affect to the End-User.Thanks all and wonderful discussion so far. and moreover, thought provoking.YOU are Great. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-22 : 18:48:16
|
Do not run DBCC CHECKDB during business hours. It is an I/O hog and will impact your performance.quote:
As I have directed, so was, I take on directly to the production environment with the DB which is mostly populated to the least but time is the criterion, so simultaneously I do it on the Development Box on procuring backup, But my question is, while executing the CheckDB there might be some transactions registered and these new transactions carries unawares with the changes made to them. So what should I do in this situation.
I do not understand this paragraph. Please rephrase.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2010-03-22 : 23:22:44
|
| Tara thanks for help, as I understood many things and also Russel and Kristen for driving me in the right direction.What I refered was,While I do on my development server I tends to get the backup procured during the business hours from Production server then restore onto Dev. box and execute the CHECKDB. so it consumes couple of Minutes meantime there might be some transactions developed.Do I need to restore from Dev. Box to Production, which is 100 percent incorrect.Perhaps the above statement is completely incorrect, but there might be some intrusions going on in the prod. box, so how to negotiate this phenomenon.Secondly, you have narrated complete story on checkDB, apart from putting this as a SQL job yet running and get the report being generated thereafter (if there exists an error) but I would like to draw the attention what are the possible errors (foreseeing) and in the event how to troubleshoot them.Since my last episode with an example of EDENGARDEN has no errors but i would like to know the kind of errors (so this would be informative before I could land into problem) apart from ALLOCATION ERRORS and CONSISTENCY ERRORS.How to negotiate.The Reason why I do ask is, I would like to include CheckDB with Reindex job in the step-wise manner.Thanks SQL- Hall of Fame for your never ending support. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2010-03-23 : 22:57:16
|
| We do procure and maintain 1-day old backups and I am planning once in a week to perform by automating the CheckDB job.But if not mitigating with the same thing just wanna know how to trouble shoot for the errors whatever may be.ex: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.The reason for persisting this again, though this time I would have been freed not doing but with kind of errors I would like to troubleshoot.many thanks for your patient answers and I am getting the results on this issue. |
|
|
|
Next Page
|
CHECKDB
Tara Kizer