dbcc check db changing schema version

Question

Post reply

dbcc check db changing schema version

Jon.Morisi

SSChampion

Points: 12846
More actions
September 17, 2010 at 11:23 am

#228013

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?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jon.Morisi SSChampion Points: 12846 More actions · Answer 1

http://msdn.microsoft.com/en-us/library/ms190918.aspx

blurb about lazy schema validation

Jon.Morisi SSChampion Points: 12846 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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, MVP, M.Sc (Comp Sci)
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

Jon.Morisi SSChampion Points: 12846 More actions · Answer 4

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...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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, MVP, M.Sc (Comp Sci)
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

Jon.Morisi SSChampion Points: 12846 More actions · Answer 6

Thanks again,

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

Sergey Smirnov SSC Journeyman Points: 85 More actions · Answer 7

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.