SQL compatibility_level changing to 100 by itself on a production database

Question

Post reply

SQL compatibility_level changing to 100 by itself on a production database

mariobehring

Mr or Mrs. 500

Points: 516
More actions
January 19, 2012 at 10:43 am

#263055

Hi list,
I have a production database on a SQL Server 2008 R2 server that once a week has its compatibility_level set to 100 (by the SQL Server itself it seems) when it should stay at 80.
When the compatibility_level is set to 100 for this particular database, the applications that access it stop working for users that are executing login in the application. For users that are already logged, the application continues working.
Once we identify the problem and return the compatibility_level to 80, everything works fine.
Here is the line on the ERRORLOG where the change occurs:
2012-01-17 17:29:58.66 spid115 Setting database option COMPATIBILITY_LEVEL to 100 for database CorporeRM.
Thanks in advance.
Mario

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

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

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 1

OK, so what is your question specifically.

You have some code somewhere that ran in SQL2000 mode but it doesn't in SQL2008 mode.

Shivrudra SSCrazy Points: 2835 More actions · Answer 2

Hi....as per my view

1. It is not possible to change the comp.level automatically by SQL server itself. we need to change it manually

2. You can not change the comp.level of database directly ...need to analyze the the data with upgrade advisor....need to make change as per suggestion.

3. For application point of view you need to check whether application is supporting the changed version or not ..if not support the changed compatibility it will log error....and by reconfiguring old comp.level it will work.

2012-01-17 17:29:58.66 spid115 Setting database option COMPATIBILITY_LEVEL to 100 for database CorporeRM.

I think it is message which logged when changed the database comp.level of database, it not error

Regards,
Shivrudra W

mariobehring Mr or Mrs. 500 Points: 516 More actions · Answer 3

My question is...how do I keep the compatibility_level at 80.....how do I prevent it from changing to 100.....that´s it basically. For this particular database, of course.

BTW...I know this is not an error...but as far as I know, nobody is changing the CL to 100 manually, so I don´t know what is changing it...

Does this spid1... means anything? Can I trace this?

Cheers,

Mario

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 4

Some one or some ID that has 'sa' rights is about the only thing that can change it really.

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

mariobehring (1/19/2012)
My question is...how do I keep the compatibility_level at 80.....how do I prevent it from changing to 100.....that´s it basically. For this particular database, of course.

You identify who or what is changing it and either ask them not to or change whatever app or job is changing it. SQL will not change compat modes for no reason.

Check the default trace (I don't know if this is something that's logged) or set up your own server-side trace to monitor for this and find login name, host name and application name. Those should allow you to track down the culprit.

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

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

Markus (1/19/2012)
Some one or some ID that has 'sa' rights is about the only thing that can change it really.

Or database owner of this database.

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

Lowell SSC Guru Points: 323518 More actions · Answer 7

I just tested this by toggling my Sandbox database's compatibility level, and it does appear in the default trace;

it shows up as an ALTER DATABASE event, with the whodunnit information of time/loginname and username, but not the specific change of what occurred...just an ALTER DATABASE.

but since you know the time and the spid (which was 115, definitely not a system process, I'd guess)

you can track it down easily.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!