August 7, 2007 at 1:38 am
Hi everybody:
We have recently migrated our DDBB from SQL 2000 to SQL 2005 in several servers. We have 2 DB per server and the size of mdf file is between 10 and 40 GB.
We put Compatibility Level in 90 in SQL 2005 but when we arrive at work we see that our Maintenace Plans failed because the Compatibility Level of one of Data Bases changed to 7.
We have a trace executing the whole day registrying the execution of stored procedure sp_dbcmptlevel but in despite of compatibility level changes, the trace does not registry anithing. Even we have try to register changes in Tables in this stored procedure with a Trigger but the system gives a message telling that we cannot execute a Trigger over a system view or a system table.
Has anyone passed before me for this situation? Thank you in advance and greetings,
Nuria
August 7, 2007 at 4:52 am
Did you or anyone there restore the db from an older (sql 7) backup? Actually there is not a compatibility level of 7; you can have 70, 80 or 90. It's hard to say what could have changed it. Check the compatibility for your model db, it should be 90.
August 8, 2007 at 9:00 am
The upgrade won't change the compatiblity level, you have to do that yourself. I suspect that whomever changed it did it wrong. Now if it is changing back after you change it to 90, then your trace should detect it.
August 8, 2007 at 10:43 am
As far as I know, someone restored the DB from a Backup made in SQL Server 7.0
Don't know the Compatibility Level of that Backup (70, 65, 60)
As you say Actually there is not a compatibility level of 7, I ment the Compatibility Level changes to 70 on its own. We have 2 DDBB in every Server and just one of the two changes its Compatibility Level from 90 to 70.
I have checked the Compatibility Level of our model and it is 90.
Thanks for your answer and greetings,
August 8, 2007 at 10:51 am
Are you sure that the only way for changing the Compatibility Level in SQL Server 2005 is through the execution of Stored Procedure 'sp_dbcmptlevel'?
Could it be possible that the external Application changed it in any way?
I ask you because the trace doesn't detect it when changes on its own. The trace does detects it when I change the Compatibility Level manually.
Thanks in advance,
August 8, 2007 at 1:41 pm
So it is changing back to 70 after you verify that it is set to 90, and this does it consistantly? How many times has it switched back? once, less than 5 more than 10?
There is no way with 2005 to update the sys.databases table directly. (you could in 2000). I looked to see if there might be a reason it is switching back.
You could create a job to get an idea as to when it changes.
INSERT
INTO version_log
SELECT
GETDATE(),name FROM sys.databases
WHERE
compatibility_level != 90
Run that once per hour, or if you must go once a minute.
It is also possible that it is failing to properly upgrade. What version DB did this come from?
August 9, 2007 at 7:07 am
I did some checking (I need to get a life). It is possible to change the compatibility level without calling the stored proc. However!!!!! it is undocumented, and unsupported. I do suspect if it is changing it is either some rogue code that is out there, or it is something that some background process thinks it is not properly upgraded and keeps switching it back.
Do you replicate this database?
August 10, 2007 at 2:12 am
Hi Bob,
First thanks for your answer and your interest. We do not replicate this DB. I have create a job following your advise for detecting the hour of the Compatibility Level' changes.
Actually our Migration was not an Upgrade because new Servers were bought. Then SQL Server 2005 Standard Edition 64 bits was installed and then in some cases a Backup of DB was restored and in some cases an Attach of DB was made.
I find unbelievable that it is possible to change the Compatibility level without calling the Stored Procedure... I am not sure if some roge code of the Application makes this change. Anyway every morning we find Compatibility Level changed to 70.
August 10, 2007 at 9:01 am
OK, you did restores and you did Attaches. Which have the 70 instead of 90? Both? or just one type?
Also, are the codes being reset again, once you manually fix the 70 to 90?
August 10, 2007 at 2:45 pm
Not sure that a restore is different than an attach in the long run. The idea of the job was to narrow down the time so that you can narrow down the possible processes that are switching it. At this point I am suspecting that it might be some sort of maintainence process that you have that runs each night.
Once you get that time. Look at the app logs, security logs. System, jobs etc. Find out what is doing it. They you can if you must put a trace on it for more detail.
I suspect that either some sort of DBCC command is somehow changing it. Or more than that. It is being restored from another system nightly.
August 13, 2007 at 3:55 am
OK, you did restores and you did Attaches. Which have the 70 instead of 90? Both? or just one type? Don't know but I can investigate it asking people that installed the Server.
Also, are the codes being reset again, once you manually fix the 70 to 90? Do you mean the codes of the external Application? If yes, I have been told that Development Team have been working in it. Do you think it is possible that some code of the external Application is beeing the cause of the Compatibility Level's change?
August 13, 2007 at 7:43 am
I apologize for the reset question - I reread the thread this morning (after decent sleep this weekend) and realize I missed your statement about 'every morning'. I'm inclined to agree with Bob - that some process is resetting the level from 90 to 70. An explicit SQL update command could be incorporated in some application code, but it's more likely that there's something going on overnight, between servers and between databases, that is kicking the compatibility setting back to 70 - perhaps to support an older database?
I've never tried this - if you were to set the the compatibility to 90, modify the database with a more 'modern' table - say a small table with an only 2005-compatible type of attribute, and then attempt to reset the table to 70, does the reset fail?
If yes, then it's a risky way to force the exposure of what is resetting overnight. Unless it overwrites the entire database (removing your change), you should catch it out with an execution failure, an alert, or some other error indicator.
Very IFFY to do in a production environment (i.e, choice of last resort), but if there's no other way to capture the process, this may work.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply