Change database compatibility level – SQL Server

Posted: July 4, 2011 in Database, SQLServer
Tags: , ,

For all installations of SQL Server 2008, the default compatibility level is 100. Databases created in SQL Server 2008 are set to this level unless the model database has a lower compatibility level. When a database is upgraded to SQL Server 2008 from any earlier version of SQL Server, the database retains its existing compatibility level if it is at least 80. Upgrading a database with a compatibility level below 80 sets the database to compatibility level 80. This applies to both system and user databases. Use ALTER DATABASE to change the compatibility level of the database. To view the current compatibility level of a database, query the compatibility_level column in the sys.databases catalog view.

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

  • 80 = SQL Server 2000
  • 90 = SQL Server 2005
  • 100 = SQL Server 2008

or Use sp_dbcmptlevel system proc to check/set different compatiblity levels to a database.

sp_dbcmptlevel ‘<db name>’ – to check the current compatibility
sp_dbcmptlevel ‘<db name>’, 100 – to set the compatibility to SQL Server 2008 

Changing the compatibility level while users are connected to the database can produce incorrect result sets for active queries. So set the database to single-user access mode and change the database compatibility to avoid any unexpected results.

 Read detailed info and importance of database compatibility @ http://msdn.microsoft.com/en-us/library/bb510680.aspx

I got to know this as some of our dynamic transfer build scripts were failed in the stage environment and the environments are having migrated databases from sql server 2005 to sql server 2008. As part of the migration database compatibility has not been changed to sql server 2008 i.e. 100. After setting 100 as compatibility level everything started working as expected.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s