Recently I was working on a client’s SQL Server and made some changes to their databases compatibility level frequently I see the level set at 100 which corresponds to SQL Server 2008 and 2008 R2.
Anyways I ran into a multiple databases set for Level 100. The SQL Server version was SQL Server 2014 so I updated to 120 and was met with some amazing results. This server has a nightly job that takes about 6 hours to run and now it only takes 5 hours.
I have a SQL query below that will write a change script for each of the databases on your system to the latest level your SQL Server supports. However one bit of caution. Updating compatibility level normally causes no issues but I have seen it cause issues in the past. The good news is that its an easy fix just change the level back to what it used to be and you are back in business.
DECLARE @SQL VARCHAR(MAX) = '';
DECLARE @CompLevel CHAR(3);
SELECT @CompLevel = LEFT(CAST(SERVERPROPERTY('productversion') AS VARCHAR), 2) + '0'
SELECT @SQL += 'ALTER DATABASE ' + QUOTENAME(name) + ' SET COMPATIBILITY_LEVEL = '+ CAST(@CompLevel AS CHAR(3)) + '; -- was set to ' + cast(compatibility_level as varchar(10))
+ CHAR(13) + CHAR(10) -- typically char 13 comes before char 10 for CR/LF
FROM sys.databases
WHERE compatibility_level <> @CompLevel;
PRINT @SQL;
--If nothing displays you are up to date
--If ALTER DATABASE [DBNAME] SET COMPATIBILITY_LEVEL = XXX;
--Displays you can select them and run each one to update your compatibility level to the latest level supported by your SQL version
Leave a Reply