InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 10:22:06 GMT20http://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxhttp://paul.dynalias.com/SQL/SiteAssets/Lists/Posts/AllPosts/sp_RestoreScriptGenie.txtMy Christmas present to the world.Tue, 25 Dec 2012 15:31:55 GMTPaul Brewerhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxI've developed a stored procedure to automate the generation of restore scripts. http://paul.dynalias.com/SQL/_layouts/15/start.aspx#/The procedure is actually a single SQL query, you can specify a STOPAT point and it works out the FULL, DIFF, LOG files needed and generates the necessary script. It includes a CHECKDB at the end, ignores WITH COPY_ONLY , allows overrides to restore file locations using WITH MOVE and so on. It's better than anything else available free I can find.Tue, 25 Dec 2012 15:24:54 GMTPaul Brewerhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxNice article.Tue, 25 Dec 2012 08:37:20 GMTNeha05http://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxNewbee ;Paul this is a very good article but one common trend here is educating the masses that require these backups . Would it be possible to providfe me with names of vitualisation tools which will aid in validating backs as well as restores.Thu, 08 Nov 2012 14:23:33 GMTSQL PGhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxYou must be corrupting a page that is not allocated - you need to figure out which page you're corrupting and then check it's allocation status using DBCC PAGE.For the backup, if you corrupt the backup header then restore verifyonly will catch that, with or without checksum.Sat, 29 Sep 2012 09:16:59 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspx[quote][b]Paul Randal (9/28/2012)[/b][hr]It's nothing to do with DBCC - it's the try/catch eating the errors being printed. No idea how to use that correctly - it has some weird behavior.Nothing changes the behavior of DBCC, but you're changing the way the DBCC output is consumed.All I said was after running DBCC, the value of @@error will be non-zero if errors were found.[/quote]Paul,OK, I confirm what you say about @@error after 'dbcc checkdb'.So now I can catch all 3 situations:dbcc checkdbbackup database with checksumrestore verifyonly with checksumand if something goes wrong I get an email - heaven forbid.:w00t:One thing that still leaves me a bit puzzled is that sometimes modifying a single byte somewhere in the database (MDF file) checkdb does not detect the database to be 'corrupt' (nor does backup with checksum). Maybe it's because the page is 'inactive', so I do a shrinkfile but I can still get this phenomena to occur. If every page (8192 bytes) in the file has a checksum then modifying a single byte at random should corrupt the database. I can only assume that not all pages get checked because they are 'not important' in some deep down technical sense.Regarding 'restore verifyonly', there seems to be no difference between having 'with checksum' and not having it. I can corrupt a backup and I get the same error message with 'with checksum' and without it. But I'm happy doing it with 'with checksum'.I will now stop corrupting things...:-)Sat, 29 Sep 2012 00:54:18 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxAnother significant problem with maintenance plans, well documented, is that the 'Verify Backups' check box doesn't translate to backups WITH CHECKSUM. It actuall runs a restore verify only immediately after the backup, I don't understand why they don't use WITH CHECKSUM (when possible) if this option is enabled.If you assume some responsibility for mature production database servers with maintenance plans that have been running for years, its difficult to make the case to management that they should all be replaced with T-SQL jobs so they can use CHECKSUM.Fri, 28 Sep 2012 12:47:05 GMTPaul Brewerhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspx[quote][b]Paul Randal (9/28/2012)[/b][hr]It's nothing to do with DBCC - it's the try/catch eating the errors being printed. No idea how to use that correctly - it has some weird behavior.Nothing changes the behavior of DBCC, but you're changing the way the DBCC output is consumed.All I said was after running DBCC, the value of @@error will be non-zero if errors were found.[/quote]Thanks for confirming my suspicions Paul. As I said, I don't use it often enough to understand why that happens, but it looks like TRY..CATCH has trouble handling multiple errors in one go.Michael: I ran my SP that I had posted against a broken DB, and b/c there is no TRY..CATCH involved, the Level 16 errors [b]do[/b] get dumped to the output table, which means you could then query that output table. The only reason I included the final TRY..CATCH in the SP is to handle the rare possibility that DBCC CHECKDB [b]itself[/b] fails b/c the DB errors are so severe.RichFri, 28 Sep 2012 12:45:47 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxIt's nothing to do with DBCC - it's the try/catch eating the errors being printed. No idea how to use that correctly - it has some weird behavior.Nothing changes the behavior of DBCC, but you're changing the way the DBCC output is consumed.All I said was after running DBCC, the value of @@error will be non-zero if errors were found.Fri, 28 Sep 2012 12:37:37 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspx[quote][b]Michael Meierruth (9/28/2012)[/b][hr][quote][b]rmechaber (9/28/2012)[/b][hr]Did you see my post on page 3 of the comments?Rich[/quote]Yes, it's 200 lines of code and comments (I like those!)But I'm comparing 1 line of my code (a single DBCC CHECKDATABSE) with 8 lines of my trivial code trying to trap an error.Why are they getting different results in the Messages window?Where am I going wrong?[/quote]Sorry Michael, I didn't read your post closely enough to realize that you were getting different results back when checking the same database. I have no idea, and I'll look forward to Paul Randal's reply. I just tried running your code block against a Broken database I downloaded from Paul's website and compared the output from a direct run [DBCC CHECKDB('broken')] with your code block (also checking DB 'broken'). I saved the output from both runs to text files and ran them through a text comparison utility; the direct run had the following lines in the output that were missing from the TRY..CATCH attempt:[code="plain"]Msg 8928, Level 16, State 1, Line 1Object ID 2073058421, index ID 0, partition ID 72057594038321152, alloc unit ID 72057594042318848 (type In-row data): Page (1:143) could not be processed. See other errors for details.Msg 8939, Level 16, State 98, Line 1Table error: Object ID 2073058421, index ID 0, partition ID 72057594038321152, alloc unit ID 72057594042318848 (type In-row data), page (1:143). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -4.[/code]RichP.S. Add'l: I am wondering if this is an artifact of the TRY..CATCH block itself. I don't use it enough myself to know if this is related, but you might want to look [url=http://social.msdn.microsoft.com/Forums/en-AU/transactsql/thread/d676bfe7-981c-4279-b710-abbc08baadf3]here[/url] and [url=http://www.sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-t-sql-at-a-glance--throw-136849]here[/url].Fri, 28 Sep 2012 12:20:23 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspx[quote][b]rmechaber (9/28/2012)[/b][hr]Did you see my post on page 3 of the comments?Rich[/quote]Yes, it's 200 lines of code and comments (I like those!)But I'm comparing 1 line of my code (a single DBCC CHECKDATABSE) with 8 lines of my trivial code trying to trap an error.Why are they getting different results in the Messages window?Where am I going wrong?Fri, 28 Sep 2012 11:59:00 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxDid you see my post on page 3 of the comments?RichFri, 28 Sep 2012 11:48:36 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspx[quote][b]Paul Randal (9/28/2012)[/b][hr]Too many ambiguities in your post and question. For instance, the 'end' of what? Page? Database? Backup file?Can you give the exact set of steps and T-SQL, from creating the database to running the commands that do/do not fail? Only then can I explain the behavior you're seeing.Thanks[/quote]OK, let's do one at a time: DBCC CHECKDB('...') run on a corrupted database (via a single byte change in the physical file).When I run the above single comand directly I get 'Level 16' errors:[code="plain"]DBCC results for 'A7RETE'.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 3638 rows in 24 pages for object "sys.sysrowsetcolumns".DBCC results for 'sys.sysrowsets'.There are 431 rows in 6 pages for object "sys.sysrowsets".DBCC results for 'sysallocunits'.There are 510 rows in 9 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 3638 rows in 27 pages for object "sys.syshobtcolumns".DBCC results for 'sys.syshobts'.There are 431 rows in 5 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 510 rows in 2 pages for object "sys.sysserefs".DBCC results for 'sys.sysowners'.There are 14 rows in 1 pages for object "sys.sysowners".DBCC results for 'sys.sysprivs'.There are 153 rows in 1 pages for object "sys.sysprivs".DBCC results for 'sys.sysschobjs'.There are 1128 rows in 16 pages for object "sys.sysschobjs".DBCC results for 'sys.syscolpars'.There are 3181 rows in 38 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 742 rows in 12 pages for object "sys.sysidxstats".DBCC results for 'sys.sysiscols'.There are 1071 rows in 7 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 1416 rows in 158 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 1033 rows in 6 pages for object "sys.syssingleobjrefs".DBCC results for 'sys.sysmultiobjrefs'.There are 1346 rows in 12 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 'tr_action'.There are 1 rows in 1 pages for object "tr_action".DBCC results for 'tr_processlog'.There are 0 rows in 0 pages for object "tr_processlog".DBCC results for 'tr_ppbackup'.There are 0 rows in 0 pages for object "tr_ppbackup".DBCC results for 'tr_linkheader'.There are 141 rows in 4 pages for object "tr_linkheader".DBCC results for 'tr_mapping'.There are 14 rows in 1 pages for object "tr_mapping".DBCC results for 'tr_processparamvalue'.There are 0 rows in 0 pages for object "tr_processparamvalue".DBCC results for 'tr_demandtypecount'.There are 0 rows in 0 pages for object "tr_demandtypecount".DBCC results for 'tr_resourcerole'.There are 124 rows in 7 pages for object "tr_resourcerole".DBCC results for 'tr_teammember'.There are 0 rows in 0 pages for object "tr_teammember".DBCC results for 'tr_resourcebudget'.There are 0 rows in 0 pages for object "tr_resourcebudget".DBCC results for 'tr_milestonetrend'.There are 0 rows in 0 pages for object "tr_milestonetrend".DBCC results for 'tr_approver'.There are 0 rows in 0 pages for object "tr_approver".DBCC results for 'tr_activityactionmap'.There are 0 rows in 0 pages for object "tr_activityactionmap".DBCC results for 'tr_method'.There are 3 rows in 1 pages for object "tr_method".DBCC results for 'tr_userfield'.There are 43 rows in 2 pages for object "tr_userfield".DBCC results for 'tr_currencyrate'.There are 0 rows in 0 pages for object "tr_currencyrate".DBCC results for 'tr_requirevalue'.There are 0 rows in 0 pages for object "tr_requirevalue".DBCC results for 'tr_request'.There are 2 rows in 1 pages for object "tr_request".DBCC results for 'tr_demandactivityvalue'.There are 0 rows in 0 pages for object "tr_demandactivityvalue".DBCC results for 'tr_benefittype'.There are 0 rows in 0 pages for object "tr_benefittype".DBCC results for 'tr_flow'.There are 0 rows in 0 pages for object "tr_flow".DBCC results for 'tr_costtype'.There are 0 rows in 0 pages for object "tr_costtype".DBCC results for 'tr_usercategory'.There are 23 rows in 1 pages for object "tr_usercategory".DBCC results for 'tr_activity'.There are 0 rows in 0 pages for object "tr_activity".DBCC results for 'tr_milestonevalue'.There are 0 rows in 0 pages for object "tr_milestonevalue".DBCC results for 'trx_file'.There are 729 rows in 9 pages for object "trx_file".DBCC results for 'tr_bucketbudget'.There are 0 rows in 0 pages for object "tr_bucketbudget".DBCC results for 'tr_subscription'.There are 0 rows in 0 pages for object "tr_subscription".DBCC results for 'tr_teamvalue'.There are 0 rows in 0 pages for object "tr_teamvalue".DBCC results for 'trx_taonet'.There are 739043 rows in 27119 pages for object "trx_taonet".DBCC results for 'tr_requestdemand'.There are 0 rows in 0 pages for object "tr_requestdemand".DBCC results for 'tr_link'.There are 142 rows in 2 pages for object "tr_link".DBCC results for 'tr_xlreport'.There are 25 rows in 1 pages for object "tr_xlreport".DBCC results for 'tr_resourcecharac'.There are 55 rows in 3 pages for object "tr_resourcecharac".DBCC results for 'trx_eventlog9'.There are 2103 rows in 22 pages for object "trx_eventlog9".DBCC results for 'tr_stepbudget'.There are 0 rows in 0 pages for object "tr_stepbudget".DBCC results for 'tr_requirement'.There are 0 rows in 0 pages for object "tr_requirement".DBCC results for 'trx_eventlog6'.There are 577 rows in 6 pages for object "trx_eventlog6".DBCC results for 'tr_budgetgraph'.There are 1 rows in 1 pages for object "tr_budgetgraph".DBCC results for 'tr_fiscalperiod'.There are 0 rows in 0 pages for object "tr_fiscalperiod".DBCC results for 'tr_benefit'.There are 0 rows in 0 pages for object "tr_benefit".DBCC results for 'tr_timesheetvalue'.There are 0 rows in 0 pages for object "tr_timesheetvalue".DBCC results for 'tr_statenode'.There are 0 rows in 0 pages for object "tr_statenode".DBCC results for 'tr_assignment'.There are 0 rows in 0 pages for object "tr_assignment".DBCC results for 'tr_xlreportsql'.There are 35 rows in 3 pages for object "tr_xlreportsql".DBCC results for 'tr_demandtypecodemap'.There are 0 rows in 0 pages for object "tr_demandtypecodemap".DBCC results for 'tr_currency'.There are 1 rows in 1 pages for object "tr_currency".DBCC results for 'tr_milestone'.There are 0 rows in 0 pages for object "tr_milestone".DBCC results for 'tr_milestonemap'.There are 0 rows in 0 pages for object "tr_milestonemap".DBCC results for 'tr_stepaccess'.There are 621 rows in 6 pages for object "tr_stepaccess".DBCC results for 'tr_budgetcostpref'.There are 0 rows in 0 pages for object "tr_budgetcostpref".DBCC results for 'tr_resgroup'.There are 0 rows in 0 pages for object "tr_resgroup".DBCC results for 'tr_demandactivitycharac'.There are 0 rows in 0 pages for object "tr_demandactivitycharac".DBCC results for 'tr_resource'.There are 101 rows in 5 pages for object "tr_resource".DBCC results for 'tr_characvalue'.There are 1348 rows in 40 pages for object "tr_characvalue".DBCC results for 'tr_demandactivityinfo'.There are 0 rows in 0 pages for object "tr_demandactivityinfo".DBCC results for 'tr_processparam'.There are 0 rows in 0 pages for object "tr_processparam".DBCC results for 'tr_dataread'.There are 107 rows in 1 pages for object "tr_dataread".DBCC results for 'tr_demandtypecharac'.There are 0 rows in 0 pages for object "tr_demandtypecharac".DBCC results for 'tr_timesheet'.There are 0 rows in 0 pages for object "tr_timesheet".DBCC results for 'tr_costexplanation'.There are 0 rows in 0 pages for object "tr_costexplanation".DBCC results for 'tr_comment'.There are 0 rows in 1 pages for object "tr_comment".DBCC results for 'tr_releasestatus'.There are 11 rows in 1 pages for object "tr_releasestatus".DBCC results for 'tr_alert'.There are 724 rows in 28 pages for object "tr_alert".DBCC results for 'tr_taskrelation'.There are 0 rows in 1 pages for object "tr_taskrelation".DBCC results for 'tr_bucket'.There are 0 rows in 0 pages for object "tr_bucket".DBCC results for 'tr_deptresource'.There are 0 rows in 0 pages for object "tr_deptresource".DBCC results for 'tr_teamcharac'.There are 0 rows in 0 pages for object "tr_teamcharac".DBCC results for 'sys.queue_messages_761105802'.There are 0 rows in 0 pages for object "sys.queue_messages_761105802".DBCC results for 'tr_budgetrespref'.There are 0 rows in 0 pages for object "tr_budgetrespref".DBCC results for 'tr_departmentaccess'.There are 0 rows in 0 pages for object "tr_departmentaccess".DBCC results for 'tr_demandflowinfo'.There are 0 rows in 0 pages for object "tr_demandflowinfo".DBCC results for 'sys.queue_messages_793105916'.There are 0 rows in 0 pages for object "sys.queue_messages_793105916".DBCC results for 'tr_statelink'.There are 0 rows in 0 pages for object "tr_statelink".DBCC results for 'tr_milestonecharac'.There are 0 rows in 0 pages for object "tr_milestonecharac".DBCC results for 'sys.queue_messages_825106030'.There are 0 rows in 0 pages for object "sys.queue_messages_825106030".DBCC results for 'tr_activitycharacmap'.There are 0 rows in 0 pages for object "tr_activitycharacmap".DBCC results for 'tr_riskvalue'.There are 0 rows in 0 pages for object "tr_riskvalue".DBCC results for 'tr_audit'.There are 0 rows in 0 pages for object "tr_audit".DBCC results for 'tr_timesheetcharac'.There are 0 rows in 0 pages for object "tr_timesheetcharac".DBCC results for 'tr_roleaccess'.There are 311 rows in 3 pages for object "tr_roleaccess".DBCC results for 'tr_demandtypecharacgroup'.There are 0 rows in 0 pages for object "tr_demandtypecharacgroup".DBCC results for 'tr_requestcalcfactor'.There are 66 rows in 1 pages for object "tr_requestcalcfactor".DBCC results for 'tr_system'.There are 1 rows in 1 pages for object "tr_system".DBCC results for 'tr_dependency'.There are 0 rows in 0 pages for object "tr_dependency".DBCC results for 'tr_budget'.There are 0 rows in 0 pages for object "tr_budget".DBCC results for 'tr_teamaccess'.There are 0 rows in 0 pages for object "tr_teamaccess".DBCC results for 'tr_demandtype'.There are 0 rows in 0 pages for object "tr_demandtype".DBCC results for 'tr_demandresgroup'.There are 0 rows in 0 pages for object "tr_demandresgroup".DBCC results for 'tr_requestvalue'.There are 0 rows in 0 pages for object "tr_requestvalue".DBCC results for 'tr_investmentallocation'.There are 0 rows in 0 pages for object "tr_investmentallocation".DBCC results for 'tr_step'.There are 14 rows in 1 pages for object "tr_step".DBCC results for 'tr_resourcecost'.There are 0 rows in 0 pages for object "tr_resourcecost".DBCC results for 'tr_indexerqueue'.Msg 8951, Level 16, State 1, Line 1Table error: table 'tr_indexerqueue' (ID 1157579162). Data row does not have a matching index row in the index 'tr_indexerqueue1' (ID 2). Possible missing or invalid keys for the index row matching:Msg 8955, Level 16, State 1, Line 1Data row (1:36621:29) identified by (HEAP RID = (1:36621:29)) with index values 'tkey = 21587 and tkey2 = 0 and HEAP RID = (1:36621:29)'.Msg 8952, Level 16, State 1, Line 1Table error: table 'tr_indexerqueue' (ID 1157579162). Index row in index 'tr_indexerqueue1' (ID 2) does not match any data row. Possible extra or invalid keys for:Msg 8956, Level 16, State 1, Line 1Index row (1:18399:113) with values (tkey = 21586 and tkey2 = 0 and HEAP RID = (1:36621:29)) pointing to the data row identified by (HEAP RID = (1:36621:29)).There are 2922595 rows in 10751 pages for object "tr_indexerqueue".CHECKDB found 0 allocation errors and 2 consistency errors in table 'tr_indexerqueue' (object ID 1157579162).DBCC results for 'tr_demandtypemap'.There are 0 rows in 0 pages for object "tr_demandtypemap".DBCC results for 'tr_demandhistory'.There are 0 rows in 0 pages for object "tr_demandhistory".DBCC results for 'tr_rate'.There are 0 rows in 0 pages for object "tr_rate".DBCC results for 'tr_task'.There are 25669 rows in 1470 pages for object "tr_task".DBCC results for 'tr_requestinherit'.There are 0 rows in 0 pages for object "tr_requestinherit".DBCC results for 'trx_eventlog'.There are 132115 rows in 1456 pages for object "trx_eventlog".DBCC results for 'tr_characgroup'.There are 4 rows in 1 pages for object "tr_characgroup".DBCC results for 'trx_v_element_full'.There are 0 rows in 0 pages for object "trx_v_element_full".DBCC results for 'tr_riskcharac'.There are 0 rows in 0 pages for object "tr_riskcharac".DBCC results for 'trx_v_element'.There are 82595 rows in 381 pages for object "trx_v_element".DBCC results for 'trx_wsloc'.There are 6948 rows in 51 pages for object "trx_wsloc".DBCC results for 'tr_stepcharac'.There are 11 rows in 1 pages for object "tr_stepcharac".DBCC results for 'trx_wslocdata'.There are 14761 rows in 91 pages for object "trx_wslocdata".DBCC results for 'tr_genericresource'.There are 0 rows in 0 pages for object "tr_genericresource".DBCC results for 'trx_wsextdata'.There are 50790 rows in 305 pages for object "trx_wsextdata".DBCC results for 'trx_wsrouting'.There are 2 rows in 1 pages for object "trx_wsrouting".DBCC results for 'trx_wsoperation'.There are 8 rows in 1 pages for object "trx_wsoperation".DBCC results for 'tr_taskvalue'.There are 334023 rows in 5420 pages for object "tr_taskvalue".DBCC results for 'trx_wsitem'.There are 45 rows in 1 pages for object "trx_wsitem".DBCC results for 'tr_team'.There are 0 rows in 0 pages for object "tr_team".DBCC results for 'tr_prioritytype'.There are 3 rows in 1 pages for object "tr_prioritytype".DBCC results for 'trx_usersetting'.There are 136 rows in 2 pages for object "trx_usersetting".DBCC results for 'trx_wsext'.There are 5200 rows in 49 pages for object "trx_wsext".DBCC results for 'tr_charac'.There are 17 rows in 1 pages for object "tr_charac".DBCC results for 'tr_departmentvalue'.There are 0 rows in 0 pages for object "tr_departmentvalue".DBCC results for 'tr_statusreport'.There are 0 rows in 0 pages for object "tr_statusreport".DBCC results for 'tr_requestcharac'.There are 1 rows in 1 pages for object "tr_requestcharac".DBCC results for 'av_activity'.There are 47973 rows in 3548 pages for object "av_activity".DBCC results for 'av_actres'.There are 50178 rows in 963 pages for object "av_actres".DBCC results for 'tr_bucketcosttype'.There are 0 rows in 0 pages for object "tr_bucketcosttype".DBCC results for 'tr_opitem'.There are 0 rows in 0 pages for object "tr_opitem".DBCC results for 'av_project'.There are 499 rows in 24 pages for object "av_project".DBCC results for 'tr_demandtypevalue'.There are 0 rows in 0 pages for object "tr_demandtypevalue".DBCC results for 'tr_fountain'.There are 0 rows in 1 pages for object "tr_fountain".DBCC results for 'tr_statustype'.There are 3 rows in 1 pages for object "tr_statustype".DBCC results for 'tr_role'.There are 18 rows in 1 pages for object "tr_role".DBCC results for 'tr_departmentcharac'.There are 0 rows in 0 pages for object "tr_departmentcharac".DBCC results for 'tr_bucketvalue'.There are 0 rows in 0 pages for object "tr_bucketvalue".DBCC results for 'tr_deptbudget'.There are 0 rows in 0 pages for object "tr_deptbudget".DBCC results for 'tr_resourcetype'.There are 0 rows in 0 pages for object "tr_resourcetype".DBCC results for 'tr_taskresource'.There are 0 rows in 0 pages for object "tr_taskresource".DBCC results for 'avx_dbinfo'.There are 2 rows in 1 pages for object "avx_dbinfo".DBCC results for 'tr_stepvalue'.There are 0 rows in 0 pages for object "tr_stepvalue".DBCC results for 'tr_process'.There are 0 rows in 0 pages for object "tr_process".DBCC results for 'tr_execoption'.There are 0 rows in 0 pages for object "tr_execoption".DBCC results for 'av_respool'.There are 1518 rows in 11 pages for object "av_respool".DBCC results for 'av_resource'.There are 1537 rows in 18 pages for object "av_resource".DBCC results for 'tr_guestroleaccess'.There are 7 rows in 1 pages for object "tr_guestroleaccess".DBCC results for 'tr_risk'.There are 0 rows in 0 pages for object "tr_risk".DBCC results for 'tr_restypeassoc'.There are 0 rows in 0 pages for object "tr_restypeassoc".DBCC results for 'tr_allocbucketpref'.There are 0 rows in 0 pages for object "tr_allocbucketpref".DBCC results for 'tr_deptassociation'.There are 0 rows in 0 pages for object "tr_deptassociation".DBCC results for 'tr_releasefunction'.There are 122 rows in 1 pages for object "tr_releasefunction".DBCC results for 'tr_stepresbudget'.There are 0 rows in 0 pages for object "tr_stepresbudget".DBCC results for 'tr_requirecharac'.There are 0 rows in 0 pages for object "tr_requirecharac".DBCC results for 'tr_etl_event'.There are 0 rows in 0 pages for object "tr_etl_event".DBCC results for 'tr_requestaccess'.There are 6 rows in 1 pages for object "tr_requestaccess".DBCC results for 'tr_usersetting'.There are 253 rows in 9 pages for object "tr_usersetting".DBCC results for 'tr_budgetallocation'.There are 0 rows in 0 pages for object "tr_budgetallocation".DBCC results for 'tr_resourcevalue'.There are 10 rows in 1 pages for object "tr_resourcevalue".DBCC results for 'tr_budgetbenpref'.There are 0 rows in 0 pages for object "tr_budgetbenpref".DBCC results for 'tr_execreport'.There are 0 rows in 0 pages for object "tr_execreport".DBCC results for 'tr_department'.There are 1 rows in 1 pages for object "tr_department".DBCC results for 'tr_filter'.There are 0 rows in 1 pages for object "tr_filter".DBCC results for 'tr_demand'.There are 0 rows in 0 pages for object "tr_demand".DBCC results for 'tr_resgroupuser'.There are 0 rows in 0 pages for object "tr_resgroupuser".DBCC results for 'tr_taskcharac'.There are 86744 rows in 754 pages for object "tr_taskcharac".DBCC results for 'tr_approvalaudit'.There are 0 rows in 0 pages for object "tr_approvalaudit".DBCC results for 'tr_bucketcharac'.There are 0 rows in 0 pages for object "tr_bucketcharac".CHECKDB found 0 allocation errors and 2 consistency errors in database 'A7RETE'.repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (A7RETE).DBCC execution completed. If DBCC printed error messages, contact your system administrator.[/code]When I run the following code (directly or in a procedure):[code="sql"]declare @err intbegin try dbcc checkdb('A7RETE')end trybegin catch set @err=@@error if @err<>0 select @errend catch[/code]I get the following messages with no 'Level 16' errors:[code="plain"]DBCC results for 'A7RETE'.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 3638 rows in 24 pages for object "sys.sysrowsetcolumns".DBCC results for 'sys.sysrowsets'.There are 431 rows in 6 pages for object "sys.sysrowsets".DBCC results for 'sysallocunits'.There are 510 rows in 9 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 3638 rows in 27 pages for object "sys.syshobtcolumns".DBCC results for 'sys.syshobts'.There are 431 rows in 5 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 510 rows in 2 pages for object "sys.sysserefs".DBCC results for 'sys.sysowners'.There are 14 rows in 1 pages for object "sys.sysowners".DBCC results for 'sys.sysprivs'.There are 153 rows in 1 pages for object "sys.sysprivs".DBCC results for 'sys.sysschobjs'.There are 1128 rows in 16 pages for object "sys.sysschobjs".DBCC results for 'sys.syscolpars'.There are 3181 rows in 38 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 742 rows in 12 pages for object "sys.sysidxstats".DBCC results for 'sys.sysiscols'.There are 1071 rows in 7 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 1416 rows in 158 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 1033 rows in 6 pages for object "sys.syssingleobjrefs".DBCC results for 'sys.sysmultiobjrefs'.There are 1346 rows in 12 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 'tr_action'.There are 1 rows in 1 pages for object "tr_action".DBCC results for 'tr_processlog'.There are 0 rows in 0 pages for object "tr_processlog".DBCC results for 'tr_ppbackup'.There are 0 rows in 0 pages for object "tr_ppbackup".DBCC results for 'tr_linkheader'.There are 141 rows in 4 pages for object "tr_linkheader".DBCC results for 'tr_mapping'.There are 14 rows in 1 pages for object "tr_mapping".DBCC results for 'tr_processparamvalue'.There are 0 rows in 0 pages for object "tr_processparamvalue".DBCC results for 'tr_demandtypecount'.There are 0 rows in 0 pages for object "tr_demandtypecount".DBCC results for 'tr_resourcerole'.There are 124 rows in 7 pages for object "tr_resourcerole".DBCC results for 'tr_teammember'.There are 0 rows in 0 pages for object "tr_teammember".DBCC results for 'tr_resourcebudget'.There are 0 rows in 0 pages for object "tr_resourcebudget".DBCC results for 'tr_milestonetrend'.There are 0 rows in 0 pages for object "tr_milestonetrend".DBCC results for 'tr_approver'.There are 0 rows in 0 pages for object "tr_approver".DBCC results for 'tr_activityactionmap'.There are 0 rows in 0 pages for object "tr_activityactionmap".DBCC results for 'tr_method'.There are 3 rows in 1 pages for object "tr_method".DBCC results for 'tr_userfield'.There are 43 rows in 2 pages for object "tr_userfield".DBCC results for 'tr_currencyrate'.There are 0 rows in 0 pages for object "tr_currencyrate".DBCC results for 'tr_requirevalue'.There are 0 rows in 0 pages for object "tr_requirevalue".DBCC results for 'tr_request'.There are 2 rows in 1 pages for object "tr_request".DBCC results for 'tr_demandactivityvalue'.There are 0 rows in 0 pages for object "tr_demandactivityvalue".DBCC results for 'tr_benefittype'.There are 0 rows in 0 pages for object "tr_benefittype".DBCC results for 'tr_flow'.There are 0 rows in 0 pages for object "tr_flow".DBCC results for 'tr_costtype'.There are 0 rows in 0 pages for object "tr_costtype".DBCC results for 'tr_usercategory'.There are 23 rows in 1 pages for object "tr_usercategory".DBCC results for 'tr_activity'.There are 0 rows in 0 pages for object "tr_activity".DBCC results for 'tr_milestonevalue'.There are 0 rows in 0 pages for object "tr_milestonevalue".DBCC results for 'trx_file'.There are 729 rows in 9 pages for object "trx_file".DBCC results for 'tr_bucketbudget'.There are 0 rows in 0 pages for object "tr_bucketbudget".DBCC results for 'tr_subscription'.There are 0 rows in 0 pages for object "tr_subscription".DBCC results for 'tr_teamvalue'.There are 0 rows in 0 pages for object "tr_teamvalue".DBCC results for 'trx_taonet'.There are 739043 rows in 27119 pages for object "trx_taonet".DBCC results for 'tr_requestdemand'.There are 0 rows in 0 pages for object "tr_requestdemand".DBCC results for 'tr_link'.There are 142 rows in 2 pages for object "tr_link".DBCC results for 'tr_xlreport'.There are 25 rows in 1 pages for object "tr_xlreport".DBCC results for 'tr_resourcecharac'.There are 55 rows in 3 pages for object "tr_resourcecharac".DBCC results for 'trx_eventlog9'.There are 2103 rows in 22 pages for object "trx_eventlog9".DBCC results for 'tr_stepbudget'.There are 0 rows in 0 pages for object "tr_stepbudget".DBCC results for 'tr_requirement'.There are 0 rows in 0 pages for object "tr_requirement".DBCC results for 'trx_eventlog6'.There are 577 rows in 6 pages for object "trx_eventlog6".DBCC results for 'tr_budgetgraph'.There are 1 rows in 1 pages for object "tr_budgetgraph".DBCC results for 'tr_fiscalperiod'.There are 0 rows in 0 pages for object "tr_fiscalperiod".DBCC results for 'tr_benefit'.There are 0 rows in 0 pages for object "tr_benefit".DBCC results for 'tr_timesheetvalue'.There are 0 rows in 0 pages for object "tr_timesheetvalue".DBCC results for 'tr_statenode'.There are 0 rows in 0 pages for object "tr_statenode".DBCC results for 'tr_assignment'.There are 0 rows in 0 pages for object "tr_assignment".DBCC results for 'tr_xlreportsql'.There are 35 rows in 3 pages for object "tr_xlreportsql".DBCC results for 'tr_demandtypecodemap'.There are 0 rows in 0 pages for object "tr_demandtypecodemap".DBCC results for 'tr_currency'.There are 1 rows in 1 pages for object "tr_currency".DBCC results for 'tr_milestone'.There are 0 rows in 0 pages for object "tr_milestone".DBCC results for 'tr_milestonemap'.There are 0 rows in 0 pages for object "tr_milestonemap".DBCC results for 'tr_stepaccess'.There are 621 rows in 6 pages for object "tr_stepaccess".DBCC results for 'tr_budgetcostpref'.There are 0 rows in 0 pages for object "tr_budgetcostpref".DBCC results for 'tr_resgroup'.There are 0 rows in 0 pages for object "tr_resgroup".DBCC results for 'tr_demandactivitycharac'.There are 0 rows in 0 pages for object "tr_demandactivitycharac".DBCC results for 'tr_resource'.There are 101 rows in 5 pages for object "tr_resource".DBCC results for 'tr_characvalue'.There are 1348 rows in 40 pages for object "tr_characvalue".DBCC results for 'tr_demandactivityinfo'.There are 0 rows in 0 pages for object "tr_demandactivityinfo".DBCC results for 'tr_processparam'.There are 0 rows in 0 pages for object "tr_processparam".DBCC results for 'tr_dataread'.There are 107 rows in 1 pages for object "tr_dataread".DBCC results for 'tr_demandtypecharac'.There are 0 rows in 0 pages for object "tr_demandtypecharac".DBCC results for 'tr_timesheet'.There are 0 rows in 0 pages for object "tr_timesheet".DBCC results for 'tr_costexplanation'.There are 0 rows in 0 pages for object "tr_costexplanation".DBCC results for 'tr_comment'.There are 0 rows in 1 pages for object "tr_comment".DBCC results for 'tr_releasestatus'.There are 11 rows in 1 pages for object "tr_releasestatus".DBCC results for 'tr_alert'.There are 724 rows in 28 pages for object "tr_alert".DBCC results for 'tr_taskrelation'.There are 0 rows in 1 pages for object "tr_taskrelation".DBCC results for 'tr_bucket'.There are 0 rows in 0 pages for object "tr_bucket".DBCC results for 'tr_deptresource'.There are 0 rows in 0 pages for object "tr_deptresource".DBCC results for 'tr_teamcharac'.There are 0 rows in 0 pages for object "tr_teamcharac".DBCC results for 'sys.queue_messages_761105802'.There are 0 rows in 0 pages for object "sys.queue_messages_761105802".DBCC results for 'tr_budgetrespref'.There are 0 rows in 0 pages for object "tr_budgetrespref".DBCC results for 'tr_departmentaccess'.There are 0 rows in 0 pages for object "tr_departmentaccess".DBCC results for 'tr_demandflowinfo'.There are 0 rows in 0 pages for object "tr_demandflowinfo".DBCC results for 'sys.queue_messages_793105916'.There are 0 rows in 0 pages for object "sys.queue_messages_793105916".DBCC results for 'tr_statelink'.There are 0 rows in 0 pages for object "tr_statelink".DBCC results for 'tr_milestonecharac'.There are 0 rows in 0 pages for object "tr_milestonecharac".DBCC results for 'sys.queue_messages_825106030'.There are 0 rows in 0 pages for object "sys.queue_messages_825106030".DBCC results for 'tr_activitycharacmap'.There are 0 rows in 0 pages for object "tr_activitycharacmap".DBCC results for 'tr_riskvalue'.There are 0 rows in 0 pages for object "tr_riskvalue".DBCC results for 'tr_audit'.There are 0 rows in 0 pages for object "tr_audit".DBCC results for 'tr_timesheetcharac'.There are 0 rows in 0 pages for object "tr_timesheetcharac".DBCC results for 'tr_roleaccess'.There are 311 rows in 3 pages for object "tr_roleaccess".DBCC results for 'tr_demandtypecharacgroup'.There are 0 rows in 0 pages for object "tr_demandtypecharacgroup".DBCC results for 'tr_requestcalcfactor'.There are 66 rows in 1 pages for object "tr_requestcalcfactor".DBCC results for 'tr_system'.There are 1 rows in 1 pages for object "tr_system".DBCC results for 'tr_dependency'.There are 0 rows in 0 pages for object "tr_dependency".DBCC results for 'tr_budget'.There are 0 rows in 0 pages for object "tr_budget".DBCC results for 'tr_teamaccess'.There are 0 rows in 0 pages for object "tr_teamaccess".DBCC results for 'tr_demandtype'.There are 0 rows in 0 pages for object "tr_demandtype".DBCC results for 'tr_demandresgroup'.There are 0 rows in 0 pages for object "tr_demandresgroup".DBCC results for 'tr_requestvalue'.There are 0 rows in 0 pages for object "tr_requestvalue".DBCC results for 'tr_investmentallocation'.There are 0 rows in 0 pages for object "tr_investmentallocation".DBCC results for 'tr_step'.There are 14 rows in 1 pages for object "tr_step".DBCC results for 'tr_resourcecost'.There are 0 rows in 0 pages for object "tr_resourcecost".DBCC results for 'tr_indexerqueue'.There are 2922595 rows in 10751 pages for object "tr_indexerqueue".CHECKDB found 0 allocation errors and 2 consistency errors in table 'tr_indexerqueue' (object ID 1157579162).DBCC results for 'tr_demandtypemap'.There are 0 rows in 0 pages for object "tr_demandtypemap".DBCC results for 'tr_demandhistory'.There are 0 rows in 0 pages for object "tr_demandhistory".DBCC results for 'tr_rate'.There are 0 rows in 0 pages for object "tr_rate".DBCC results for 'tr_task'.There are 25669 rows in 1470 pages for object "tr_task".DBCC results for 'tr_requestinherit'.There are 0 rows in 0 pages for object "tr_requestinherit".DBCC results for 'trx_eventlog'.There are 132115 rows in 1456 pages for object "trx_eventlog".DBCC results for 'tr_characgroup'.There are 4 rows in 1 pages for object "tr_characgroup".DBCC results for 'trx_v_element_full'.There are 0 rows in 0 pages for object "trx_v_element_full".DBCC results for 'tr_riskcharac'.There are 0 rows in 0 pages for object "tr_riskcharac".DBCC results for 'trx_v_element'.There are 82595 rows in 381 pages for object "trx_v_element".DBCC results for 'trx_wsloc'.There are 6948 rows in 51 pages for object "trx_wsloc".DBCC results for 'tr_stepcharac'.There are 11 rows in 1 pages for object "tr_stepcharac".DBCC results for 'trx_wslocdata'.There are 14761 rows in 91 pages for object "trx_wslocdata".DBCC results for 'tr_genericresource'.There are 0 rows in 0 pages for object "tr_genericresource".DBCC results for 'trx_wsextdata'.There are 50790 rows in 305 pages for object "trx_wsextdata".DBCC results for 'trx_wsrouting'.There are 2 rows in 1 pages for object "trx_wsrouting".DBCC results for 'trx_wsoperation'.There are 8 rows in 1 pages for object "trx_wsoperation".DBCC results for 'tr_taskvalue'.There are 334023 rows in 5420 pages for object "tr_taskvalue".DBCC results for 'trx_wsitem'.There are 45 rows in 1 pages for object "trx_wsitem".DBCC results for 'tr_team'.There are 0 rows in 0 pages for object "tr_team".DBCC results for 'tr_prioritytype'.There are 3 rows in 1 pages for object "tr_prioritytype".DBCC results for 'trx_usersetting'.There are 136 rows in 2 pages for object "trx_usersetting".DBCC results for 'trx_wsext'.There are 5200 rows in 49 pages for object "trx_wsext".DBCC results for 'tr_charac'.There are 17 rows in 1 pages for object "tr_charac".DBCC results for 'tr_departmentvalue'.There are 0 rows in 0 pages for object "tr_departmentvalue".DBCC results for 'tr_statusreport'.There are 0 rows in 0 pages for object "tr_statusreport".DBCC results for 'tr_requestcharac'.There are 1 rows in 1 pages for object "tr_requestcharac".DBCC results for 'av_activity'.There are 47973 rows in 3548 pages for object "av_activity".DBCC results for 'av_actres'.There are 50178 rows in 963 pages for object "av_actres".DBCC results for 'tr_bucketcosttype'.There are 0 rows in 0 pages for object "tr_bucketcosttype".DBCC results for 'tr_opitem'.There are 0 rows in 0 pages for object "tr_opitem".DBCC results for 'av_project'.There are 499 rows in 24 pages for object "av_project".DBCC results for 'tr_demandtypevalue'.There are 0 rows in 0 pages for object "tr_demandtypevalue".DBCC results for 'tr_fountain'.There are 0 rows in 1 pages for object "tr_fountain".DBCC results for 'tr_statustype'.There are 3 rows in 1 pages for object "tr_statustype".DBCC results for 'tr_role'.There are 18 rows in 1 pages for object "tr_role".DBCC results for 'tr_departmentcharac'.There are 0 rows in 0 pages for object "tr_departmentcharac".DBCC results for 'tr_bucketvalue'.There are 0 rows in 0 pages for object "tr_bucketvalue".DBCC results for 'tr_deptbudget'.There are 0 rows in 0 pages for object "tr_deptbudget".DBCC results for 'tr_resourcetype'.There are 0 rows in 0 pages for object "tr_resourcetype".DBCC results for 'tr_taskresource'.There are 0 rows in 0 pages for object "tr_taskresource".DBCC results for 'avx_dbinfo'.There are 2 rows in 1 pages for object "avx_dbinfo".DBCC results for 'tr_stepvalue'.There are 0 rows in 0 pages for object "tr_stepvalue".DBCC results for 'tr_process'.There are 0 rows in 0 pages for object "tr_process".DBCC results for 'tr_execoption'.There are 0 rows in 0 pages for object "tr_execoption".DBCC results for 'av_respool'.There are 1518 rows in 11 pages for object "av_respool".DBCC results for 'av_resource'.There are 1537 rows in 18 pages for object "av_resource".DBCC results for 'tr_guestroleaccess'.There are 7 rows in 1 pages for object "tr_guestroleaccess".DBCC results for 'tr_risk'.There are 0 rows in 0 pages for object "tr_risk".DBCC results for 'tr_restypeassoc'.There are 0 rows in 0 pages for object "tr_restypeassoc".DBCC results for 'tr_allocbucketpref'.There are 0 rows in 0 pages for object "tr_allocbucketpref".DBCC results for 'tr_deptassociation'.There are 0 rows in 0 pages for object "tr_deptassociation".DBCC results for 'tr_releasefunction'.There are 122 rows in 1 pages for object "tr_releasefunction".DBCC results for 'tr_stepresbudget'.There are 0 rows in 0 pages for object "tr_stepresbudget".DBCC results for 'tr_requirecharac'.There are 0 rows in 0 pages for object "tr_requirecharac".DBCC results for 'tr_etl_event'.There are 0 rows in 0 pages for object "tr_etl_event".DBCC results for 'tr_requestaccess'.There are 6 rows in 1 pages for object "tr_requestaccess".DBCC results for 'tr_usersetting'.There are 253 rows in 9 pages for object "tr_usersetting".DBCC results for 'tr_budgetallocation'.There are 0 rows in 0 pages for object "tr_budgetallocation".DBCC results for 'tr_resourcevalue'.There are 10 rows in 1 pages for object "tr_resourcevalue".DBCC results for 'tr_budgetbenpref'.There are 0 rows in 0 pages for object "tr_budgetbenpref".DBCC results for 'tr_execreport'.There are 0 rows in 0 pages for object "tr_execreport".DBCC results for 'tr_department'.There are 1 rows in 1 pages for object "tr_department".DBCC results for 'tr_filter'.There are 0 rows in 1 pages for object "tr_filter".DBCC results for 'tr_demand'.There are 0 rows in 0 pages for object "tr_demand".DBCC results for 'tr_resgroupuser'.There are 0 rows in 0 pages for object "tr_resgroupuser".DBCC results for 'tr_taskcharac'.There are 86744 rows in 754 pages for object "tr_taskcharac".DBCC results for 'tr_approvalaudit'.There are 0 rows in 0 pages for object "tr_approvalaudit".DBCC results for 'tr_bucketcharac'.There are 0 rows in 0 pages for object "tr_bucketcharac".CHECKDB found 0 allocation errors and 2 consistency errors in database 'A7RETE'.repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (A7RETE).DBCC execution completed. If DBCC printed error messages, contact your system administrator.[/code]So how do I trap this error in a script or procedure?Fri, 28 Sep 2012 11:45:27 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxToo many ambiguities in your post and question. For instance, the 'end' of what? Page? Database? Backup file?Can you give the exact set of steps and T-SQL, from creating the database to running the commands that do/do not fail? Only then can I explain the behavior you're seeing.ThanksFri, 28 Sep 2012 11:01:58 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxHave been playing around with intentionally corrupting database files and database backup files to see how the various commands that have been discussed so far will behave. This is on a 1GB database.Changing the first byte or a byte some where in the middle causes DBCC CHECKDB and BACKUP WITH CHECKSUM to fail.Changing the last byte or a byte 'near the end' generates no errors.This is true also of RESTORE VERIFYONLY WITH CHECKSUM. For this to generate an error I had to change a byte as far as 20% from the end. Interesting.Now I'm trying to catch errors using this simple code.declare @err intbegin try--dbcc checkdb('mydatabase')--restore verifyonly from disk='mydatabase.bak' with checksum end trybegin catchset @err=@@errorif @err<>0 select @errend catchRESTORE VERIFYONLY correctly shows error 3013.DBCC CHECKDB does not generate any red error messages. But if you run it all by itself the output shows red error messages. Why is this?Fri, 28 Sep 2012 08:38:00 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxA scratch servers the way to go, thanks :-)Thu, 27 Sep 2012 15:43:44 GMTPaul Brewerhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxOr to automate restoring master on a scratch server and running DBCC CHECKDB on it.Thu, 27 Sep 2012 15:41:50 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxSo the only way to verify the integrity of your master database backups on a busy production server is to use restore with verify only!Thu, 27 Sep 2012 15:34:55 GMTPaul Brewerhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxIt won't work because there are some structures that only exist in the master database and may cause corruption errors to be reported if master is restored as a user database and then consistency checked.Thu, 27 Sep 2012 15:24:58 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxI had to recovery a master database in production recently and used this blog as guidance, it worked an absolute treat. http://blogs.technet.com/b/fort_sql/archive/2011/02/01/the-easiest-way-to-rebuild-the-sql-server-master-database.aspxIt's a possible approach to verifying production master, model and msdb backups maybe? Restore the backups to another, test instance to new user databases called master_live, model_live, msdb_live using WITH MOVE. RegardsThu, 27 Sep 2012 14:46:20 GMTPaul Brewerhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxThanks Paul!Thu, 27 Sep 2012 14:25:14 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxThere are fantastic scripts and articles/blogs on your sqlskills website Paul. Anyone with an enquiring mind can use them as a starter for ten and extrapolate out entire features sets and learn a lot about SQL. Add to that the MCM videos and its incredible what you put out for free. Thanks, sincerely.Thu, 27 Sep 2012 14:16:58 GMTPaul Brewerhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspx[quote][b]Paul Brewer (9/27/2012)[/b][hr]The Ola Hallengren maintenance scrips create SQL Agent jobs that use sqlcmd to instantiate the backup and maintenance procedures he has written.[/quote]Superb! Thanks!Thu, 27 Sep 2012 13:58:04 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspx[quote][b]Rahul The Dba (9/27/2012)[/b][hr]Hithis one is really a needed article.But apart from this I want to know one thing, that is I have a store procedure made by myself for (xyz thing) "sorry can't disclose it", so how can i get that patented to my name? I am from INDIA.Hope to see some helpful steps from your end.Thanks.[/quote]You can only patent something original, that someone with the similar experience and knowledge would NOT be able to come up with easily. Therefore, it's extremely unlikely that you could patent a stored-proc to perform backups/restores/consistency checks. Also, the amount of 'prior art' in the area would likely preclude any possibility of you being able to claim originality. And then you'd have to pay a patent lawyer to convert the idea into patent language, and file with the relevant patent offices. It's an expensive, tortuous process that I'm glad Microsoft funded for my patents.Give your proc away for free - you won't make money from it.Thu, 27 Sep 2012 13:24:12 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxThe Ola Hallengren maintenance scrips create SQL Agent jobs that use sqlcmd to instantiate the backup and maintenance procedures he has written. Rahul, if Ola isn't overly concerned about patents and the like, I don't think you need to be either mate. Nothing you've developed will be in the same league as his freely available procedures.Thu, 27 Sep 2012 13:07:27 GMTPaul Brewerhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxOh yes, getting back to my original question, is there anyone using sqlcmd.exe to backup databases? Or has this become 'obsolete'?Thu, 27 Sep 2012 12:50:14 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspx[quote][b]Rahul The Dba (9/27/2012)[/b][hr]Hithis one is really a needed article.But apart from this I want to know one thing, that is I have a store procedure made by myself for (xyz thing) "sorry can't disclose it", so how can i get that patented to my name? I am from INDIA.Hope to see some helpful steps from your end.Thanks.[/quote]Well, I can only assume your idea deals with backing up databases. If not, then please start a new topic.I don't know much about patents except that the whole issue of patents has pretty much run out of control - everywhere. Not to mention Apple vs Samsung. You have now motivated me to dig into that story and exactly how the Apple patents were violated by Samsung.But your question has raised many questions in my mind. Thus what you want to patent is not a stored procedure but an idea. This means that once your idea becomes public, anyone with some sql skills (say a million or so people) can propabily write the stored procedure that does exactly what your idea proposes. In your opinion, would this violate the patent? But what if I write a stored procedure that does the same thing, but is 10 times faster than yours. Or what if I write a process, not using an sql stored procedure, that does the same thing 50 times faster. Or what if I write something that does the same thing but is 3 times better than your idea?In any case, in my opinion, patenting stored procedures is no way to become rich quickly. And besides, this is a forum where you share things.Thu, 27 Sep 2012 12:14:54 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxHithis one is really a needed article.But apart from this I want to know one thing, that is I have a store procedure made by myself for (xyz thing) "sorry can't disclose it", so how can i get that patented to my name? I am from INDIA.Hope to see some helpful steps from your end.Thanks.Thu, 27 Sep 2012 10:10:21 GMTRahul The Dbahttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspx[quote][b]Gazareth (9/24/2012)[/b][hr]Yes, thanks for the article Paul.Is there actually an easy way to check the output from DBCC CHECKDB?I currently scan the SQL log using xp_readerrorlog for the DBCC output info, another way is to use TABLERESULTS clause, and check for the completion line.However, both methods could be called undocumented - you won't find either in BOL![/quote]This caused me much grief, also -- trying to capture the output from DBCC CHECKDB. I did a bit of research into this and found a very useful website (now 404) and info. from Paul Randal (including some very useful deliberately mangled databases that can be used for testing). Based on that -- and much testing and some painful code failures :ermm: -- I put together the following SP. It will run DBCC CHECKDB for every database on the server (with some hard-coded exceptions; change this to suit your needs) and send an HTML-formatted email to you if it finds any errors. There's just one input parameter: your email address.There's a lot of text in the notes section at the top that might be useful to you, including what the shortcomings of using a maintenance plan are, info. on the returned data types from CHECKDB, web links to relevant materials, etc. Note that one of Paul's broken databases deliberately produces a serious enough error that it will prevent the rest of the SP from running for other databases (see notes); to handle situations like that, I wrapped the DBCC CHECKDB in a TRY..CATCH so that I will be notified separately that the script didn't execute and I need to investigate pronto.It's been a while since I built this, and if folks here have suggestions or find problems, I'd appreciate hearing about them.[code="sql"]USE [DBA]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROC Run_CHECKDB @email VARCHAR(8000)AsBEGIN/* Utility SP to run DBCC against a hardcoded list of DBs and report any found errors. Adapted from: http://www.lockergnome.com/sqlsquirrel/2009/03/11/inserting-dbcc-checkdb-results-into-a-table/ (That page has gone 404 as of 2012-05-31)CREATED: 2011-05-21PURPOSE: For daily/weekly DB integrity checks. Why needed? See notes below. There are problems using a maintenance plan task.INPUTS: @email - a [;]-delimited list of recipients to be notified if errors foundHARD-CODED: curDB - Cursor w/ WHERE clause of databases to in/exclude. Modify below as needed.NOTES: Overview - Create a cursor of databases and run DBCC for each. - Run DBCC WITH TABLERESULTS, NO_INFOMSGS to generate a table of output. - Encapsulate everything in TRY...CATCH b/c of the possibility of fatal errors when running DBCC CHECKDB against severely corrupted DBs. - Though I initially coded this to save the results to a temp table, I decided I'd like to have each day's output saved to the DB for later querying if errors were reported. DBCC CHECKDB reporting sucks. - There's no easy way to save/report the results. - If you just run DBCC CHECKDB maint. plan as an agent job and if CHECKDB finds any DB errors, a "failed job step" is logged in msdb, which isn't really true. This results in an unnecessary report from the Failed Job Steps agent job. - Furthermore, a maint. plan "notify operator task" is based on the success/failure of CHECKDB reports for ALL databases; no way to report on specific database success/failure. - Another problem w/ maint. plans: no way to script a dynamic list of databases to EXclude. You specify which ones to INclude, but if you later add new databases, they won't be included automatically. - Even using the method in this SP, there's nothing online about what all the columns mean, nor how to easily identify rows that represent a true error. Corrections to website/datatypes - Several column definitions from the website are wrong, others may be too.... - Which raises another problem with DBCC CHECKDB: As confirmed by Paul Randall (lost the URL...), there is NO complete published list of the output data types. Not listed in BOL either. Grrrrrr... Testing - Tested against production and corrupted databases from Paul Randal's website: http://sqlskills.com/pastConferences.asp - One of his corrupted DBs, DemoFatalCorruption2, deliberately fails so bad that it terminates DBCC CHECKDB early, preventing subsequent databases from being run and which would cause the agent execution to log a failed job step. It returns simply: "Possible schema corruption. Run DBCC CHECKCATALOG." So, use TRY...CATCH w/ an error-handler to terminate gracefully and warn the DBA. How to return just the errors from DBCC? - Running DBCC CHECKDB WITH NO_INFOMSGS excludes the rows we're not interested in, so use that. - Before I found the WITH NO_INFOMSGS option, I tried various WHERE clauses to return just the errors from the table results. I could not find any consistent way to identify just the rows reporting errors. The only sure way I could determine was to look for the last lines of the report output, which are: LIKE 'CHECKDB found%' AND NOT LIKE 'CHECKDB found 0 allocation errors and 0 consistency errors%' See superseded code in comments at bottom if interested.*/SET NOCOUNT ONDECLARE @db NVARCHAR(128);DECLARE @html VARCHAR(MAX);DECLARE @CrLf CHAR(2);SET @CrLf = CHAR(13) + CHAR(10);IF OBJECT_ID('DBCCResults', 'U') IS NOT NULL DROP TABLE DBCCResults; -- Drop and recreate (instead of just TRUNCATE) so we can easily change column list & type below if more turn out to be incorrect.CREATE TABLE DBCCResults(-- Columns I added to make reporting easier: RunDateTime DATETIME DEFAULT CURRENT_TIMESTAMP, ObjectName NVARCHAR(128), DBName NVARCHAR(128),-- Columns matching DBCC WITH TABLERESULTS [Error] [int] NULL, [Level] [int] NULL, [State] [int] NULL, [MessageText] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RepairLevel] NVARCHAR(128) NULL, -- Website incorrectly had this as INT. [Status] [int] NULL, [DbId] [int] NULL, [ObjectID] BIGINT NULL, -- Website incorrectly had this as INT. [IndId] [int] NULL, [PartitionId] BIGINT NULL, -- Website incorrectly had this as INT. [AllocUnitId] BIGINT NULL, -- Website incorrectly had this as INT. [File] [int] NULL, [Page] [int] NULL, [Slot] [int] NULL, [RefFile] [int] NULL, [RefPage] [int] NULL, [RefSlot] [int] NULL, [Allocation] [int] NULL);-- Create cursor of databases to test DECLARE curDB CURSOR LOCAL FOR SELECT [name] FROM master..sysdatabases As DB WHERE [name] NOT IN ('tempdb','Broken');-- Wrap all in TRY..CATCHBEGIN TRY OPEN curDB FETCH NEXT FROM curDB INTO @db; WHILE (@@FETCH_STATUS = 0) BEGIN SET NOCOUNT ON INSERT INTO DBCCResults( Error, [Level], [State], MessageText, RepairLevel, [Status], [DbId], ObjectID, IndId, PartitionId, -- specific to SQL Server 2005, remove for SQL Server 2000 results AllocUnitId, -- specific to SQL Server 2005, remove for SQL Server 2000 results [File], Page, Slot, RefFile, RefPage, RefSlot, Allocation ) EXEC ('DBCC CHECKDB(''' + @db + ''') WITH TABLERESULTS, NO_INFOMSGS'); FETCH NEXT FROM curDB INTO @db; END CLOSE curDB; DEALLOCATE curDB; -- Update our added columns, based on info. returned by DBCC UPDATE DBCCResults SET ObjectName = OBJECT_NAME(ObjectID, DbID), DBName = DB_Name(DbID); -- If any errors found, send an email with an abbreviated list of errors IF EXISTS(SELECT * FROM DBCCResults) BEGIN -- Create html SELECT @html = '<html>' + @CrLf + '<table border="1" cellpadding="2">' + @CrLf + '<tr><th>RunDateTime</th><th>Database</th><th>Object</th></tr>' + @CrLf; SELECT @html = @html + '<tr>' + '<td>' + Convert(VARCHAR(30), RunDateTime, 120) + '</td>' + '<td>' + COALESCE(DBName, '') + '</td>' + '<td>' + COALESCE(ObjectName, '') + '</td>' + '</tr>' + @CrLf FROM DBCCResults-- ORDER BY DBName, ObjectName; SELECT @html = @html + '</table>' + @CrLf + '</html>'; -- Send email out EXEC msdb.dbo.sp_send_dbmail @recipients = @email, @subject = 'DBCC CHECKDB Encountered Errors', @body = @html, @body_format = 'HTML'; ENDEND TRYBEGIN CATCH-- If we're here, then a fatal error may have been thrown while checking a DB.-- If cursor is still open, close it. IF CURSOR_STATUS('local', 'curDB') >= 0 BEGIN CLOSE curDB; DEALLOCATE curDB; END EXEC msdb.dbo.sp_send_dbmail @recipients = @email, @subject = 'Fatal Errors Were Encountered Running SQL Agent DBCC CHECKDB!', @body = 'Run DBCC CHECKDB ASAP and verify most recent backups.', @body_format = 'HTML'; END CATCHENDGO[/code]I went to archive.org and found an old version of that now-404 web page and saved it off to a PDF. See attached. As indicated in the notes of my SP, [b]some of the data types in that web page are incorrect[/b]. I discovered these during testing when I received "data type mismatch" errors or some such.RichP.S. (added later). You'll see a reference in the notes to "Failed Job Steps agent job." This is a separate Agent job I created to query msdb for any failed agent job [u]steps[/u] and notify me about them by email. I have many multi-step, non-sequential jobs that need to continue execution if any individual step fails, so I cannot simply rely on having an email sent if the [u]job[/u] fails: in these cases, the job succeeds, only a step fails.P.P.S.: Developed and run on SQL 2005 Enterprise.Wed, 26 Sep 2012 10:01:06 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxFor those interested, it's worth mentioning that [url=http://www.red-gate.com/products/dba/sql-backup/]Red Gate SQL Backup[/url] has an automation routine built into the newest release that makes backup testing a lot easier. It will do the full restore, with options enabled, run a DBCC against it and clean it up for you, all on the fly as part of your scheduled processing. It might be worth checking out if you haven't yet built a bespoke version of this process.DISCLOSURE: I'm a proud & happy Red Gate employee.Wed, 26 Sep 2012 04:25:31 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxI run CHECKDB on the restored backup to make sure that, not only is the prod database healthy but also the backup and restore process doesn't create corruption.I have a SQL Agent job that backs up the prod database every night and restores it to a dev database which I then use to code against STEP 1 - BACKUPBACKUP DATABASE [ServicePro] TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\#####.bak' WITH NOFORMAT, INIT, NAME = N'#####-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GOdeclare @backupSetId as intselect @backupSetId = position from msdb..backupset where database_name=N'#####' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'####' )if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''####'' not found.', 16, 1) endRESTORE VERIFYONLY FROM DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\#####.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWINDGOSTEP 2 - RESTOREALTER DATABASE ######DEVSET SINGLE_USER WITH ROLLBACK IMMEDIATEGORESTORE DATABASE ######DEV FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\####.bak' WITH MOVE '###_Data' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\####DEV_Data.MDF', MOVE '###_Log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\###DEV_Log.LDF',REPLACEGOALTER DATABASE #####DEVSET MULTI_USER WITH ROLLBACK IMMEDIATETue, 25 Sep 2012 15:45:45 GMTAndre Ranierihttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxExactly - doesn't need to be superfast, just needs to be capable of doing it so you know the back at least restores.Tue, 25 Sep 2012 11:12:43 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxI wasn't able to convince my boss to set SAN space aside to restore our databases but since drives for a workstation are cheaper I was able to convince him to get a drive big enough to restore our largest DB. It won't be a good performance test but it does let us restore any DB so we can automate restores.Tue, 25 Sep 2012 10:50:07 GMTcfradenburghttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxHi James,As as already been suggested, you could use a virtual restore product instead of full restores. However, as hard as it may be, even for many mutli-TB databases, if you're not testing your backups actually work, you're putting yourself at risk in the event of a disaster. I can only suggest you make it clear to the business that backups may fail to restore (on any platform, not just SQL Server) and that they provide some budget for a test restore system and time to implement an automated mechanism to do it.I know it's hard to do, but there's just no alternative. Even with the best redundancy system, it can fail and you're left relying on your backups.ThanksTue, 25 Sep 2012 08:54:28 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspx[quote][b]JamesMorrison (9/24/2012)[/b][hr]What would you suggest for those that cannot do the 'verify backup integrity' due to time constraits? In my environment we have multiple production database clusters, each with multiple TB databases. 1) It doesn't seem feasible to use 'verify backup integrity' because of the extra time requirements.2) Restoring all of the backups elsewhere does not seem viable just due to the number of backups that exist.[/quote]One option would be to to the restore verify with checksum on another machine. That way you're not using the space it would take to restore but you're getting some level of assurance it's good. You can also look into a tool that lets you run a DB from a backup. RedGate has one as does Idera's backup software. I'm not sure if Quest's does it yet or not but if not I'm betting the next version or two will.[quote][b]JamesMorrison[/b][hr]We are requested to refresh QA, Dev or Performance testing environments every few weeks. So we do take prod backups and restore them elsewhere. But there is not a fixed schedule for this.[/quote]If the organization is open to a schedule for one of those environments that's something to consider. It may even be worth doing outside this since there are additional benefits to that which may outweigh the drawbacks.Tue, 25 Sep 2012 06:46:23 GMTcfradenburghttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspx[quote][b]Paul Randal (9/24/2012)[/b][hr]2) Did CHECKDB complete and find errors? Check the value of @@ERROR afterwards - guarantees to be non-zero if CHECKDB found/had a problem.[/quote]Thanks Paul, that's a much better way to do it!Tue, 25 Sep 2012 03:08:50 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxVery nice article. I have started using this in my backup after reading it 4-5 month back in some other forum.Also, I have configured another job to verify the backup. This job includes1) Verify the backup2) Fill the result in a table3) Put the table in dailycheck list once you back in office.The below steps configured in job to verify the backup.[code="sql"]begin tryrestore verifyonly from disk = 'your backup file path'with checksumend trybegin catchinsert into DBMaint.dbo.bkpverify (ErrorMessage ,dateNtime) ( SELECTERROR_MESSAGE() AS ErrorMessage,getdate())END CATCH;[/code]The code to create this bkpverify table is :-[code="other"]create table DBMaint.dbo.bkpverify (ErrorMessage varchar(200),dateNtime datetime)[/code]Also, you can configure to purge the records in this table which are older than 5 days(or your preferred value)Tue, 25 Sep 2012 01:47:53 GMTcrazy4sqlhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxPaul Randal,What would you suggest for those that cannot do the 'verify backup integrity' due to time constraits? In my environment we have multiple production database clusters, each with multiple TB databases. 1) It doesn't seem feasible to use 'verify backup integrity' because of the extra time requirements.2) Restoring all of the backups elsewhere does not seem viable just due to the number of backups that exist.We are requested to refresh QA, Dev or Performance testing environments every few weeks. So we do take prod backups and restore them elsewhere. But there is not a fixed schedule for this.So what would be suggested then? ThanksMon, 24 Sep 2012 12:17:09 GMTJamesMorrisonhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspxYes, that's what does the WITH CHECKSUM option on RESTORE VERIFYONLY. Either do it on the prod system, or copy and restore somewhere else.Mon, 24 Sep 2012 11:19:17 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic1363312-2616-1.aspx[quote][b]QUESTION: The database maintenance plan dialogs in Microsoft SQLServer2000 (yes, we're still running it) has a "Verify the integrity of the backup upon completion" option. [/quote]The problem with that option is that it takes forever with a large database. When we were using it the database backup times were twice as long. We had to stop using it due to the lack of time available each night. It blew through our window.Mon, 24 Sep 2012 10:27:28 GMTJamesMorrison