Check for Database Compatibility Issues

Setting the database to the right compatibility level is very important as it can have negative impact on the performance and behavior of your databases. I’ve seen many customers upgrade their SQL Servers from older versions to newer but forget to upgrade the compatibility of their databases. This means that even though you have an upgraded version of SQL Server, your database cannot benefit from any new or enhanced features of SQL Server since your database is running on an old compatibility level.

Incompatible database could also lead to inefficient query plans. Therefore, it is important that you upgrade the compatibility of databases right after the upgrade or right after you restore the database from older version to a newer version.

This script can help scan thru all the databases and provide you with a list of current and recommended database compatibility level.

CREATE Procedure [dbo].[SQLOPS_dbCompatibility] (@cmd Varchar(20)=null) as
--Author: Saleem Hakani (Http://sqlcommunity.com)
--Description: This procedure will check for database level compatibility and will report any anomoly.
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
If (@cmd is null or @cmd='Help' or @cmd <> 'Execute')
Begin
   Print 'SQLOPS_dbCompatibility: Checks for database compatibility and reports if the database compatibility is not configured correctly'
   Print '=================================================================='
   Print 'Author: Saleem Hakani (Http://sqlcommunity.com)'
   Print ' '
   Print 'Syntax: Exec SQLOPS_dbCompatibility <@cmd>;'
   Print '@cmd can be ''HELP'' (To get help on this stored procedure) or it can be ''EXECUTE'' (to execute this stored procedure)'
   Print ' '
   Print 'Example: To get help about this stored procedure'
   Print 'Exec SQLOPS_dbCompatibility ''Help'';'
   Print ' '
   Print 'Example: To execute this stored procedure'
   Print 'Exec SQLOPS_dbCompatibility ''Execute'';'
   Print ' '
Return
End
If (@Cmd='Execute')
Begin
   Declare @SQLVersion int
   SELECT @SQLVersion = CONVERT(int, (@@microsoftversion / 0x1000000) &amp; 0xff);
   BEGIN
      DECLARE @DB_Compat int
      SELECT @DB_Compat = COUNT([compatibility_level]) FROM master.sys.databases (NOLOCK) WHERE [compatibility_level] <> @SQLVersion * 10;
 
      IF @DB_Compat >= 1
      BEGIN
         Select 'Compatibility_Level' AS [Information], name AS [Database_Name], Ltrim(str([compatibility_level]))+' (Please upgrade to '+ltrim(str(@SQLVersion))+'0)' AS [Compatibility_Level]
         FROM master.sys.databases (NOLOCK)
         WHERE [compatibility_level] <> @SQLVersion * 10;
      END
      ELSE
      BEGIN
         SELECT 'Compatibility level for all databases' AS [Check], '[OK]' AS [Results]
      END
   END
End
GO