dbcc check db changing schema version

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » dbcc check db changing schema version

dbcc check db changing schema version

Rate Topic

Display Mode

Topic Options

Author

Message

Jon.Morisi

Posted Friday, September 17, 2010 11:23 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:12 AM
Points: 275, Visits: 679

I've received a report from a customer of mine that the consistency check is causing a database schema version change and causing issues with lazy schema validation. As a result every first distributed query running after consistency check will fail.

I've not been able to verify this in my research. Anyone out there have any links or references I can use in my research?

Post #988441

Jon.Morisi

Posted Friday, September 17, 2010 11:27 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:12 AM
Points: 275, Visits: 679

http://msdn.microsoft.com/en-us/library/ms190918.aspx
blurb about lazy schema validation

Post #988444

Jon.Morisi

Posted Friday, September 17, 2010 11:29 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:12 AM
Points: 275, Visits: 679

unable to find any articles stating schema version changes will occur with dbcc checkdb

Post #988447

GilaMonster

Posted Friday, September 17, 2010 11:47 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

Shouldn't. CheckDB just checks. It should not make any changes unless run with one of the repair options.

Is that the only job running at the time? No index rebuilds? Nothing running an sp_recompile?

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 #988461

Jon.Morisi

Posted Friday, September 17, 2010 12:07 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:12 AM
Points: 275, Visits: 679

Thanks Guru,

There are no other jobs running for a 1 hour or so before or after. The backups are running about an hour later.
Perhaps it's a user recompile statement...

Post #988480

GilaMonster

Posted Friday, September 17, 2010 12:39 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

That's likely. Can you ask them to run a trace and make sure that there's nothing else running.

If need be, I can check with an ex-dev team member about CheckDB and schema versions, but would like to eliminate other possibilities first.

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 #988497

Jon.Morisi

Posted Friday, September 17, 2010 1:55 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:12 AM
Points: 275, Visits: 679

Thanks again,
I've disabled the daily dbcc checkdb, now running weekly, and we will see if this resolves the issue or not.

Post #988544

Sergey Smirnov

Posted Friday, September 17, 2010 1:56 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, September 18, 2010 5:29 AM
Points: 1, Visits: 9

Hi guys, hi Jon :)
I already mentioned it Jon, in SQL 2005 up to SP2 (or SP3) dbcc checkdb() flushes out procedure cache.
It was fixed later, but frankly speaking I have already seen one bug on 2008 that was fixed on 2005.

Anyway, it seems that on 2008 schema version change happens way too often comparing with 2005 on the exactly,bit to bit same setup - it will be interesting to find why.

Post #988545

« Prev Topic | Next Topic »

Permissions