February 25, 2008 at 2:14 pm
Hi all. Running SQL 2000 sp3. My DBCC CheckDB doesn't return any errors yet when the job to rebuild indexes ran, it errored out with the infamous "Error 605 - Attempt to fetch logical page (1:385937) in database 'dbname' belongs to object 'object1', not to object 'object2'..
To resolve the problem, I dropped the indexes on object 2 and re-created them. End of problem, for now. Why didn't/wouldn't DBCC CheckDB report any such issues? I run theis consistently on all my servers, against all my databases. I always viewed the results with confidence that all was well with my data. Should I be worried that the results may be misleading? Is there something else I should be doing?
-- You can't be late until you show up.
February 25, 2008 at 2:24 pm
What was the sequence of events here - what time did the index rebuild job fail and what time was the CHECKDB start and finish?
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
February 25, 2008 at 2:33 pm
It's all bundled into one maintenance plan. The CheckDB for this particular database ran at 12:00:59am until 12:03:26am, reporting zero errors. The index build ran at 01:03:22am and failed at 01:03:52. I should also state it's running on VMWare and is a development server. Something else I'm wondering, in the SQL log, the DBCC CheckDB shows "found 0 errors and repaired 0 errors". Is it possible if I sent the results to a text file, that it would show differently than what it logs?
-- You can't be late until you show up.
February 25, 2008 at 3:36 pm
No - it will never show differently than what it logs - both outputs are driven from the same set of zero-or-more errors.
It could be that something went wrong between midnight and 1am - CHECKDB doesn't give any guarantees of consistency except at the point it read the various pages in the database - anything could happen right afterwards. Did you run any consistency checks after the index rebuild job failed?
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
February 25, 2008 at 3:46 pm
Ran it when I got to work today, prior to doing anything to the indexes. Still no error. Also did a checktable, nothing. I did a DBCC DBREINDEX on the table and the error arose in the SQL logs again. I did some searching, came across some things you where working with others on SQLTeam.com. I knew the error was on an index so I scripted the indexes, dropped them and recreated them. Ran another CheckDB, nothing. Ran the DBREINDEX to see if it would still log an issue and it came back clean. Wierd. Also, I know we should be on sp4. This is the beginning of my 3rd week at my new employer and that is definitely in the plans but I seriously doubt sp3 would be causing this.
-- You can't be late until you show up.
February 25, 2008 at 3:49 pm
I don't recall any issues in index rebuild that could show this - but it's possible there was one with parallel index rebuild plans. It could also be a stale read problem (i.e. in your hardware) if the indexes are changing a lot.
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
February 26, 2008 at 7:08 am
Didn't think about the hardware side of this but you may be right. It's a new virtual server and late yesterday I was told by infrastructure that they were having issues with shared resources on the box so the planned rollout of the machine, which was scheduled for this Friday, has been delayed. I'll keep you posted as developments warrant. Thanks for your insight!
-- You can't be late until you show up.
June 5, 2008 at 11:22 am
I'm having a similar problem. Check DB Integrity is part of my maintenance plan, but it returns no errors and the logs say it runs very quickly (around 15 seconds to go through all my DBs). I know that one of my DBs has errors, because when I run "DBCC CHECKDB WITH NO_INFOMSGS" on that database, it takes about 6 minutes to run, and returns a few errors.
Why would CHECKDB in the maintenance plan not return errors (even when I add it as a T-SQL Statement Task, same result is returned), but when I run it manually I get errors?
June 5, 2008 at 11:28 am
Sounds like it's not actually running in your maintenance plan properly if you can manually run it and it takes longer. How do you have your maintenance plan setup? Custom script or using the predefined job steps to do it? Do you have the 'fix minor errors' button selected (which you shouldn't - I had it removed from the product in 2005) and its failing to get into single_user mode?
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
June 5, 2008 at 1:15 pm
Part of the maintenance plan is working, because the backups are running fine. And the log says that the integrity check is succeeding as well, which is why it's odd. I'm using the predefined jobs to do this and "fix minor errors" is not selected (I'm using SQL Server 2005). Everything looks like it should be working, but for some reason it's not running the check properly.
June 5, 2008 at 1:24 pm
ok - could be transient problems in the IO subsystem? Can you post the output from CHECKDB when it does find errors? Is this the same for all DBs or just one?
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
June 5, 2008 at 1:25 pm
oh - just realized you said 2005 - this is a 7.0 and 2000 specific forum - there's another 2005 specific forum.
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
June 5, 2008 at 4:16 pm
I found the problem. Seems the code generated by SQL Server isn't working in the maintenance plan, though it works fine when I run that code manually:
USE [MYDATABASE]
GO
DBCC CHECKDB WITH NO_INFOMSGS
However, if I use this instead, it works. So I've changed the code to use this statement and all is well:
DBCC CHECKDB ([MYDATABASE]) WITH NO_INFOMSGS
June 5, 2008 at 4:33 pm
My guess is that its complaining about the USE statement?
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
June 5, 2008 at 4:39 pm
What version of SQL Server 2005 are you on? There was an issue in an earlier release where the Check Database Integrity Task would not actually run against all databases. It would loop on the master database and run the integrity check on master over and over.
You should be on 9.0.3054 at a minimum.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply