THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Master database Compatibility level after an In-place Upgrade

Yesterday a forums member asked why sys.dm_exec_sql_text() wouldn’t work on one instance of SQL where he was a sysadmin while the same code worked correctly on another instance of SQL.  The initial thought was that it was some kind of permissions issue.  Ken Simmons (blog/twitter) pointed out that the compatibility level of the database would affect the ability to use this DMF and that running it from a database at 80 compatibility would fail.  It turns out the person was running their query from the master database which was at 80 compatibility.  I had to test it myself to see if this was an artifact from an in-place upgrade, but in reality a quick search of the BOL would have told me that the compatibility level is left at 80 for master while all of the other system databases (tempdb, model, and msdb) are upgraded to the current compatibility level:

I never gave this much thought since I have always done side by side upgrades and have never done an inplace upgrade of a production server.  It just doesn’t leave much in the way of a rapid rollback should a problem exist.  I can certainly think of a few reasons why master wouldn’t be upgraded, it wasn’t that long ago that I worked with a system where all of the user objects for an application had been created in master during the installation, so in that scenario, which I can only imagine happens more than just the one time I have seen it, the compatibility level change could be a potential problem if legacy features were still in use in the code.  I did fix that server by moving all the objects to a user database when I found it.

According to the above listed BOL topic, after an inplace upgrade to SQL Server 2008, if the master database was at a compatibility level less than 80, for example 70, it will be upgraded to 80 compatibility which is the lowest compatibility level available in SQL Server 2008.  If the master database was at 80 or 90 compatibility it will remain at that compatibility level.  Learned something new this week.

Published Tuesday, May 04, 2010 12:23 PM by Jonathan Kehayias



James Luetkehoelter said:

That's new to me as well. That makes sense to do something like that for user databases, but master???

May 4, 2010 12:52 PM

meltondba said:

Well that explains why I found the master at 80 on a in-place upgrade gone wrong that I helped on last week.  I was wondering why it did not upgrade I know.


May 4, 2010 2:59 PM

Lekss said:

Hi Jon,

I havent done an inplace upgarde either and i too learned something new today ..


May 5, 2010 10:41 PM

ALZDBA said:

If I remember correct, upgrading to sql2005 had a similar issue. The upgrade process didn't alter the dblevels for some system databases.

May 6, 2010 2:27 AM

Chuck Rummel said:

I can think of no more painful thing than a system which installs all user objects in master.  Was that on purpose?

May 6, 2010 8:10 PM
Anonymous comments are disabled

This Blog


Privacy Statement