Sunday, March 26, 2017
 
SQL Server Script Repository
Facebook Twitter LinkedIn Google+ Addthis
 
     

SQL Script - Database Compatibility

By Saleem Hakani on 8/25/2014

Setting the database to the right compatibility level is very important as it can have a negative impact on the performance and behavior of your databases. I've seen that many customers upgrade their SQL Servers from 2008 -> 2012 or from 2012 -> 2014 but forget to upgrade the compatibility of their databases. This basically means that 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 compatibility could also lead to inefficient query plans. Therefore, it is important that you upgrade the compatibility of databases right after the database is upgraded to a newer version of SQL Server. This script can help go thru all the databases and provide you with a report on what databases are on old compatibility level and what compatibility level they need to be upgraded to.

Please make sure you read the help instructions by executing the stored procedure without any parameters to learn more about what options are available.

--Author: Saleem Hakani (Http://sqlcommunity.com)

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) & 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

 

 

 

 

 

 

 

 

Database Compatibility
SET COMPATIBILITY_LEVEL
Upgrading a database
Minimize
Script Categories
HOME   |   SQL ARTICLES   |   SCRIPTS   |   DISCUSSIONS   |   SQL TEAMS   |   JOBS   |   ABOUT US
Copyright [2014] by SQLCOMMUNITY.COM