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.