<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Data Corruption
»
CheckDB fails Sunday, but ok Monday without...
CheckDB fails Sunday, but ok Monday without repair
Rate Topic
Display Mode
Topic Options
Author
Message
Del Lee
Del Lee
Posted Monday, January 21, 2013 9:40 AM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 235,
Visits: 332
Hi all:
Early Sunday morning, our daily check of database integrity failed on one of our databases. The task is not set to auto repair. Then, early this morning the check passed without error. Even though the issue seems to have resolved itself, I am still concerned, so I wanted to post to get other comments.
Other information: According to the error log, just before the integrity check completed there were a couple of entries indicating some I/O requests were taking longer than 15 seconds and another saying "Worker 0x03FA60E8 appears to be non-yeilding on scheduler 1." I've done a google search on this, but I don't see anything obvious so far that relates to my situation. Also, there was a couple of "timeout while waiting for buffer latch - type2" messages.
I'll continue to google search some of this, but I'd be interested in any comments you guys may have based on the above.
Del Lee
Post #1409619
GilaMonster
GilaMonster
Posted Monday, January 21, 2013 9:49 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,732,
Visits: 29,996
Got an index rebuild after the integrity check?
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1409625
Del Lee
Del Lee
Posted Monday, January 21, 2013 9:52 AM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 235,
Visits: 332
GilaMonster (1/21/2013)
Got an index rebuild after the integrity check?
Yes, we sure do. So, are you saying the index rebuild probably fixed the corruption?
Del Lee
Post #1409626
GilaMonster
GilaMonster
Posted Monday, January 21, 2013 10:14 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,732,
Visits: 29,996
Fixed, no. Deallocated the corrupt page, yes.
CheckDB only checks allocated pages. If an index rebuild deallocated a corrupt page as part of the rebuild, the corruption would 'vanish', because it's no longer part of the allocated set of pages
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1409635
Del Lee
Del Lee
Posted Monday, January 21, 2013 1:35 PM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 235,
Visits: 332
GilaMonster (1/21/2013)
If an index rebuild deallocated a corrupt page as part of the rebuild, the corruption would 'vanish', because it's no longer part of the allocated set of pages
Thank you, but I'm not clear on my next course of action. I've read the following links and am still unclear:
Link 1
Link 2
To me, it sounds like the corruption is still there and can reappear when that page or pages become "reallocated." Secondly, one of the articles tells how to enable checksums, but doesn't say how to check if they are already enabled. I've only been here less than a year, so I have no way of knowing if the database was originally created in version 2005 or if it was sooner. Thirdly, if I've only seen this once and not repeatedly, is it necessary to look into possible problems with the I/O subsystem?
Thanks for your time and help.
Del Lee
Post #1409698
GilaMonster
GilaMonster
Posted Monday, January 21, 2013 1:53 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,732,
Visits: 29,996
Del Lee (1/21/2013)
To me, it sounds like the corruption is still there and can reappear when that page or pages become "reallocated."
No. When a page is reallocated, it's rewritten, so anything on that page is overwritten.
Secondly, one of the articles tells how to enable checksums, but doesn't say how to check if they are already enabled. I've only been here less than a year, so I have no way of knowing if the database was originally created in version 2005 or if it was sooner.
page_verify in sys.databases
Thirdly, if I've only seen this once and not repeatedly, is it necessary to look into possible problems with the I/O subsystem?
Yes. Corruption doesn't appear from nowhere for no reason. Something went wrong to cause it at all.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1409703
Del Lee
Del Lee
Posted Thursday, January 31, 2013 10:08 AM
SSC Veteran
Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 235,
Visits: 332
I have not seen any additional corruption since my initial post. My server specialist indicated there were warnings related to I/O latency that evening, but since then he has been monitoring things and here are his comments below.
However, I am still seeing warnings related to I/O latency on SQL02. The warning happens when the average I/O latency increases.
It is normally running in the range of 2ms write and 5ms read.
The spike is a consistent and predictable spike every night during our maintenance window starting at 12am.
The latency jumps to the 50-70ms for reads and 15-20ms for writes.
As soon as everything is done, it drops back to normal.
I'm not sure there's anything I can do about the maintenance jobs that are running (these are CheckDB, Backups, Index Rebuilds, etc). Since we are not experiencing any errors, should I be content or take some other action?
Thanks...
Del Lee
Post #1414245
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
