InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 13:16:45 GMT20http://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspx[quote][b]yemi_aderele (9/9/2011)[/b][hr]Come on guys, this is not recommended as an alternative or replacement. It has to be better than doing nothing inbetween the usual 7 days to a month gap DBCC runs on large DB'sCredit to you Ian!![/quote]I agree with Yemi, although I will also not use it because of the DBCC DROPCLEANBUFFERS.Especially liked the idea of the whole check stopping early when an error is detected.Mon, 12 Sep 2011 09:02:00 GMTJon-413357http://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxCome on guys, this is not recommended as an alternative or replacement. It has to be better than doing nothing inbetween the usual 7 days to a month gap DBCC runs on large DB'sCredit to you Ian!!Fri, 09 Sep 2011 04:55:41 GMTyemi_aderelehttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspx[quote][b]Grant Fritchey (8/23/2011)[/b][hr]Excellent code. Nice article. Well explained.But I have to say, articles like this really do make me nervous. It seems like a very healthy percentage of our fellow database professionals are missing a few fundamentals. Suggesting a way to avoid something as important as DBCC CHECKDB seems rife with potential for disaster. I'd just hate to hear "I was following your advice and now we've lost our production system."[/quote]This.Also, the only way for DBCC CHECKDB to be faster is for Microsoft to fix tempdb so that DBAs can allocate filegroups to temporary storage on a per database (or perhaps even more granular) basis. A global tempdb database used for everything is simply the biggest design flaw in SQL Server today, period.Thu, 01 Sep 2011 09:20:28 GMTjohnzabroskihttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxHow would you title this article to make it more innocuous?A Non-Production Index Error Checker?Fri, 26 Aug 2011 20:22:04 GMTRobert.Sterbalhttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspx"By clearing the buffer pool, using the command DBCC DROPCLEANBUFFERS, we ensure any data is read from the underlying physical disks, and this is where we want to find any errors."This sentence is enough to dissuade it's use for me. On a production system that is running 24/7 dropping the data buffers would be terrible for performance. My understanding of DBCC CHECKDB is that it doesn't pollute the data buffers with its reads. Those reads are marked as 'can go away first' so the memory is reused as needed. Obviously Paul knows more on the workings of this but beware of DROPCLEANBUFFERS on a production system.Wed, 24 Aug 2011 07:00:46 GMTdan.holmeshttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspx[quote][b]sqlmashup (8/23/2011)[/b][hr]have not tested this but in case someone already knows the answer, wouldn't this miss items like data purity, filestream and others mentioned specifically in BOL?http://msdn.microsoft.com/en-us/library/ms176064.aspx[/quote]Yes - it will only find IO subsystem corruptions.Tue, 23 Aug 2011 17:33:55 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxDBCC rule... thank Ian and Paul too:cool:Tue, 23 Aug 2011 17:16:56 GMTLeon . Orlov .http://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxhave not tested this but in case someone already knows the answer, wouldn't this miss items like data purity, filestream and others mentioned specifically in BOL?http://msdn.microsoft.com/en-us/library/ms176064.aspxTue, 23 Aug 2011 17:16:01 GMTsqlmashuphttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxHey Ian - one thing to check out with your script is what effect it has on the buffer pool. CHECKDB won't flush the buffer pool as all the pages it uses are marked for immediate disfavor andd reuse by the lazywriter. For table/index scans, this only happens if the amount being read if more than 10% of buffer pool memory. That could mean that on some systems the script will flush the buffer pool, causing dire perf issues while it warms up again. Can you run some tests on your system using the sys.dm_io_virtual_file_stats script I blogged a while back?Again, nice idea.CheersTue, 23 Aug 2011 09:37:30 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxThat's especially why I commended you on thinking on your feet.From us (regular posters), we know the avg level on knowledge of people coming here for help (and the state of mind they are in) so we want to make it absolutely cristal clear as of why not to use it. And when to possibly have a utility for it.That being said I'll keep this in mind if I ever hit this issue like you did.I don't want to start a new debate over this, but this is also why I use checksum + verify on all backups... and then do a test restore on all of them + checkdb then. I can't go more than 24 hours into coruption before detecting it and then I have 1 month PIT recovery available so I'd be really hard pressed to really lose anything.Tue, 23 Aug 2011 07:17:48 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxHi everyoneThanks for your replies.I feel like a sinner today. I did wonder several times if I should publish this article, but decided it hopefully has more advantages than disadvantages. On a related note, a while back I created a C# utility that opens password protected Excel spreadsheets or Word documents, I even created an article about it, but decided not to publish… Firstly, I keep thinking I should say “This utility does not replace DBCC CHECKDB”, but then I notice I have, many times.Sten/Simon, thanks for the code fix! Much appreciated.Erik, thank you for your suggestions, all very sensible.Grant, hopefully I’ve mentioned this utility is not a substitute for DBCC CHECKDB enough times for people to be aware. That said, I do understand your concerns. I would hope, in the right hands, this utility will prove very useful.Let me tell you all why I wrote this utility. I had a 5 terrabyte database, where the weekend DBCC CHECKDB typically took 10 hours to complete, and when there were problems it would sometimes take 48 hours to complete before it gave me any indication of errors. During this time I decided it might be nice to get a head start on what the underlying problems were, and how serious (heap/clustered index problems are much more troublesome). Using this utility, while the associated DBCC CHECKDB was running on another box, allowed me to identify the severity of the error very quickly, and plan/implement corrective action.Whilst for large databases you might run a DBCC CHECKDB on a weekly basis (typically due to its duration), it should be possible to run this utility on a daily basis, resulting in some degree of confidence of the state of the database’s underlying data, between weekend DBCC CHECKDB checks.I feel like I need to say it one more time, so here goes… “This utility does not replace DBCC CHECKDB” ThanksIanTue, 23 Aug 2011 07:06:00 GMTianstirkhttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxI'm with the crowd here, this will not be replacing DBCC CHECKDB on my servers either.I was more than a busted out script because an error was found.Tue, 23 Aug 2011 07:01:44 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxI'm glad to see that I'm not the only one having 2nd thoughts about this one.Don't get me wrong I love the ingenuity behind the idea. But the fact remains that to troubleshoot a corrupt db you need ALL errors and you need more details than just x table's index is screwed.Bravo on the smarts to come up with this idea and putting it together.On a personal choice level I won't be using this.Tue, 23 Aug 2011 06:40:14 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxExcellent code. Nice article. Well explained.But I have to say, articles like this really do make me nervous. It seems like a very healthy percentage of our fellow database professionals are missing a few fundamentals. Suggesting a way to avoid something as important as DBCC CHECKDB seems rife with potential for disaster. I'd just hate to hear "I was following your advice and now we've lost our production system."Tue, 23 Aug 2011 05:38:49 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxHi,Reading through the indexes is a smart move! I might steal the idea :)If I may have some improvement suggestions: 1. Instead of index(1), use select * simply, orelse LOB pages are not checked.2. I'd prefer checking the system tables as well.3. Watch out for filtered indexes - orelse QP will kill the script, not the IO error :)4. I'd pick a different title. As Paul highlighted, DBCC CHECKDB has no real alternative. So the title is catchy but probably a "Detect Disk Corruptions Superfast" or similar would manage expectations better.Cheers,ErikTue, 23 Aug 2011 04:37:56 GMTErik Bitemohttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxI've just used this to make it work with large table names and index names, as the max number of characters is 128 for an identifier.[code="sql"]-- Build SQL to read each page in each index (including clustered index).SELECT @CheckIndexesSQL = @CheckIndexesSQL + CHAR(10) + 'SELECT COUNT_BIG(*) AS [' + LEFT(SchemaName + '.' + TableName + '.' + ISNULL(IndexName, 'HEAP') + '. IxId:' + CAST(indid AS VARCHAR(3)), 128) + '] FROM ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' WITH (INDEX(' + CAST(indid AS VARCHAR(3)) + '));'FROM #IndexDetails[/code]Tue, 23 Aug 2011 03:41:49 GMTsimon.hughes-697036http://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxAfter the following modification to the Dynamic SELECT it works with long tablenames and indexnames.-- Build SQL to read each page in each index (including clustered index).SELECT @CheckIndexesSQL = @CheckIndexesSQL + CHAR(10) + 'SELECT COUNT_BIG(*) AS [Rows], ''TableName: ' + SchemaName + '.' + TableName + ''' AS [Tablename], ''IndexName: ' + ISNULL(IndexName, 'HEAP') + '. IndexId: ' + CAST(indid AS VARCHAR(3)) + ''' AS [Index] FROM ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) + ' WITH (INDEX(' + CAST(indid AS VARCHAR(3)) + '));'FROM #IndexDetailsTue, 23 Aug 2011 02:15:36 GMTstenperershttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxNice.However when i try i get:Msg 103, Level 15, State 4, Line 117The identifier that starts with 'TableName: Land.ATL_Tag_TagVidTrafikplats_RapportSomSkaGoras. IndexName: PK_ATL_Tag_TagVidTrafikplats_RapportSomSkaGoras. IndexI' is too long. Maximum length is 128.The identifier that includes full table name and index name gets too long. Need to do some minor change and put names somewhere else./StenTue, 23 Aug 2011 01:24:46 GMTstenperershttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxNice method. Thanks for the good article.And thanks to Paul Randal for the sage advice.Tue, 23 Aug 2011 01:05:46 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxThanks Paul.Agreed that this is not an optimal solution. But if I want to know where an error is, and quickly, this method does seem fastest for most IO related errors (it stops immediately there is an error!). Sometimes, for whatever reasons, there isn’t time or space to backup to another server (although, again agreed, this is the preferred method).As stated, this is NOT a substitute for DBCC CHECKDB, but a quick ah-hoc method of finding the most common errors very quickly.ThanksIanTue, 23 Aug 2011 00:15:23 GMTianstirkhttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxThanks for the heads up and advice Paul.Mon, 22 Aug 2011 23:09:23 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxNice - but really only the equivalent of BACKUP DATABASE ... WITH CHECKSUM.I would strongly advise not using this for the same reasons I strongly advise not using BACKUP DATABASE ... WITH CHECKSUM as an alternative to DBCC CHECKDB - it will not detect errors introduced by memory problems, SQL Server bugs, or IO subsystem corruptions of pages using torn-page detection.Bottom line - you can't avoid running DBCC CHECKDB - don't fall into the trap of running something faster. Offload your checks to a non-production server but continue running DBCC CHECKDB - it's the only thing that will find all corruptions.ThanksMon, 22 Aug 2011 23:02:56 GMTPaul Randalhttp://www.sqlservercentral.com/Forums/Topic1163700-1358-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/DBCC+CHECKDB/74693/">A faster DBCC CHECKDB?</A>[/B]Mon, 22 Aug 2011 22:02:12 GMTianstirk