Master Database Compatibility Level

By SQLRx Admin | Miscellaneous

Oct 28

If you performed an in place upgrade of your SQL Server, your master database may be at a lower compatibility level than all of the other system databases.  This was done by conscious decision from Microsoft in an attempt to make for a smooth upgrade and ensure that existing applications can still function post upgrade.  This has the negative side effect of not being able to use operators, keywords and other functionality when the database, in whose context you are executing these queries, is not at the right compatibility level.

For instance, if you want to examine currently executing queries using SQL 2005 with your master database in 80 compatibility …

USE master

GO

SELECT s2.dbid,s2.objectid,SUBSTRING (s2.text, s1.statement_start_offset/2 + 1,

CASE WHEN s1.statement_end_offset = -1 THEN 8192

ELSE s1.statement_end_offset/2 – s1.statement_start_offset/2 END + 1) as QueryText

FROM sys.dm_exec_requests s1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

GO

… you will receive the following error…

Msg 321, Level 15, State 1, Line 5

“sql_handle” is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

As a workaround, you can execute the same query against tempdb, model or msdb and it will work with no errors.  It is permissible to change the compatibility level of the master database should you find it in a low compatibility level.

–SQL 2005

EXEC sp_dbcmptlevel ‘master’ , 90

GO

–SQL 2008

EXEC sp_dbcmptlevel ‘master’ , 100

GO

http://msdn.microsoft.com/en-us/library/ms178653.aspx

http://technet.microsoft.com/en-us/library/bb933942.aspx

About the Author

>