Skip to main content
Dave Mason - Mastodon Dave Mason - LinkedIn Dave Mason - GitHub Dave Mason - Counter Social

A SQL Server Upgrade Oversight

SQL Server Compatibility Level

Recently I upgraded a few SQL instances to SQL 2012. One of the things I overlooked was setting databases to the current compatibility level. Days later, when I realized my omission, I quickly identified the databases. As I recall, [model] and [tempdb] (and maybe [msdb]) were at the current level of 110/SQL 2012. [master] was not. Nor were any of the user databases. This simple query was all it took:


SELECT d.name, d.compatibility_level
FROM master.sys.databases d
WHERE d.compatibility_level < 110

Naturally, I wondered if there were other databases on other instances that were not at the current compatibility level. But the query above only works on SQL 2012. After some digging, I discovered a correlation between the current (highest available) compatibility level of a SQL version and the "major" version number within the product version. I came up with this query, which I believe is valid for SQL 2005 and above:


DECLARE @MajorVersion INT

SELECT @MajorVersion = CAST(LEFT(d.ProductVersion, CHARINDEX('.', d.ProductVersion) - 1) AS INT)
FROM ( SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR) AS ProductVersion ) d

SELECT d.name, d.compatibility_level
FROM master.sys.databases d
WHERE d.compatibility_level < @MajorVersion * 10

With a registered server group, I ran the script above and was able to find all the databases in my organization that were not set at the highest available compatibility level.

Comments

Post comment