November 13, 2007 at 3:41 am
Hi,
I've had an UPDATE STATISTICS error crop up on our weekly maintenance plan with the following error:
Executing the query "UPDATE STATISTICS [dbo].[myAuditTable]
WITH FULLSCAN
" failed with the following error: "Possible index corruption detected. Run DBCC CHECKDB.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.
I would call myself an accidental DBA at best - I've been having to administer our SQL servers from my role as a developer, and I'm really new to the maintenance tasks.
I would assume from this error that I should run DBCC CHECKDB on the affected database, however the maintenance plan is running it prior to running UPDATE STATISTICS and not erroring. The plan is also running index reorgs and rebuilds prior to the UPDATE without errors. I want to run DBCC CHECKDB, but I have the following things that I'd like to check first:
1. Can DBCC CHECKDB be run while keeping the database online, and what sort of performance hit can it have (the table it is reporting on has about 400,000 rows)?
2. Could I replicate the problem in a safe enviroment by restoring a full backup of the problem DB onto another (test) server?
3. How can I best use DBCC CHECKDB to find out which index might be corrupted?
I'm sorry if these sound like really basic questions - I'm trying to understand best practice rather than just blindly trying to fix things.
Thanks in advance,
Ali
November 14, 2007 at 4:29 am
Hi Ali,
You do need to run DBCC CHECKDB. Beware that even if CHECKDB finds corruptions, it will complete successfully, Depending on how your maintenance job is setup, you may miss a corruption report if you're not checking the output from CHECKDB, or checking that @@ERROR is non-zero. I recommend running it manually on the database in question.
With regards to your questions:
1) CHECKDB runs online by default from SS2000 onwards. The performance hit depends on the workload and resource limitations of your server. Be aware that CHECKDB is pretty IO and CPU intensive. The exact perf hit on your workload can only be determine by you testing it.
2) Yes
3) Run it and look at the results - post them here and I'll help you interpret them (I wrote CHECKDB for SS2005)
As far as your maintenance plans go, you do not need to run index reorgs and index rebuilds. Pick one or the other as they both do the same thing. If you're not a full-on DBA, I'd recommend going with index rebuilds. If you go with index rebuilds, you do not need to run update statistics. Index rebuilds do a better job of updating stats and you could actually end up with worse stats by doing both operations. See Q4 in this blog post for details - http://www.sqlskills.com/blogs/paul/2007/11/05/ConferenceQuestionsPotPourri1IndexesStatsCorruptionAndEnterpriseonlyFeatures.aspx
Let me know if you have any more questions.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
November 14, 2007 at 10:43 am
Paul,
Thank you so much for your replies - I hadn't anticipated having the author of CHECKDB answering!
I was able to set up a copy of the DB in a safe environment from a full backup taken after the UPDATE STATISTICS error was reported.
DBCC CHECKDB with no additional parameters appears to have returned no errors.
I then looked at the generated SQL for the UPDATE STATISTICS task and ran that in the Query window. I got several "Cannot create or update statistics on view "dbo.myView" because both FULLSCAN and NORECOMPUTE options are required." errors, but nothing that matched up with the "Possible index corruption on myAuditTable" error.
The rebuild indexes task still does not report any errors.
So, as a best practice for this novice DBA - I should look to Rebuild Indexes but not Update Statistics in my maintenance tasks?
Thanks again for your reply.
Ali.
November 14, 2007 at 10:07 pm
I'm intrigued - can you post the T-SQL you're using? I'm not a stats expert but I can probably get Kimberly to take a look.
Yes, if you're doing index rebuilds you can avoid doing stats updates altogether. I would definitely schedule the former if you're not actively managing the database in any depth.
There can be gotchas with doing this but you should be pretty safe.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
December 10, 2007 at 5:47 am
Paul Randal (11/14/2007)
I'm intrigued - can you post the T-SQL you're using? I'm not a stats expert but I can probably get Kimberly to take a look.Yes, if you're doing index rebuilds you can avoid doing stats updates altogether. I would definitely schedule the former if you're not actively managing the database in any depth.
There can be gotchas with doing this but you should be pretty safe.
Thanks
Hi Paul - firstly I'm sorry for not getting back earlier, really rather rude of me after your advice. Alas, office moves and server rebuilds got the better of me!
The SQL I was using was simply auto-generated by the Maintenance Plan wizard in SQL2005. The stats one followed the following format (for each table or view):
use [mydb]
GO
UPDATE STATISTICS [dbo].[tableOrView]
WITH FULLSCAN
Strangely, the maintenance plan has been completing without failures for the past few weeks, with no intervention taken. I wonder if it could be related to website activity and the types of calls that are being passed at the time of the backup.
Thanks again,
Ali
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply