Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

dbcc check db changing schema version Expand / Collapse
Author
Message
Posted Friday, September 17, 2010 11:23 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 10, 2014 4:15 AM
Points: 293, Visits: 816
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
Posted Friday, September 17, 2010 11:27 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 10, 2014 4:15 AM
Points: 293, Visits: 816
http://msdn.microsoft.com/en-us/library/ms190918.aspx
blurb about lazy schema validation
Post #988444
Posted Friday, September 17, 2010 11:29 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 10, 2014 4:15 AM
Points: 293, Visits: 816
unable to find any articles stating schema version changes will occur with dbcc checkdb
Post #988447
Posted Friday, September 17, 2010 11:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 43,045, Visits: 36,205
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
Posted Friday, September 17, 2010 12:07 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 10, 2014 4:15 AM
Points: 293, Visits: 816
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
Posted Friday, September 17, 2010 12:39 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 43,045, Visits: 36,205
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
Posted Friday, September 17, 2010 1:55 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 10, 2014 4:15 AM
Points: 293, Visits: 816
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
Posted Friday, September 17, 2010 1:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse