Upgraded to SQL Server 2016. Can I change compatibility levels of the master db?

• Mike Scalise

  SSCrazy Eights

  Points: 8145

  More actions

  March 31, 2021 at 12:39 am

  #3863818

  Hi,

  I upgraded a few SQL Server instances from 2014 to 2016 a while back and I've recently noticed that the 'master' system database is still at compatibility level 120 (2014). Can I change it to 130 (2016) to match all of my other system and user databases?

  I've been doing some googling and can't find information about my specific scenario.

  Also, more generally, I find when people talk about changing compatibility levels of user databases, they say to put them in single_user mode first, make the change, then set it back to multi_user mode. Would that still apply for a system database like master?

  I actually thought the act of simply changing the compatibility level was fairly innocuous. Sure, there may be features or changes that come from the newly-set compat level, but I thought it could be done without much impact. I just want to make sure I understand the facts.

  So again,

  1) Do you think I can change it from 120 to 130 on master to match the other system and user databases?

  and

  2) If yes, what steps would you take aside from just setting it in options (or with T-SQL?)?

  Thanks in advance!

  Mike Scalise, PMP
  https://www.michaelscalise.com

• Johan Bijnens

  SSC Guru

  Points: 135259

  More actions

  March 31, 2021 at 9:19 am

  #3863909

  yes, you can.

  ALTER DATABASE [master] SET COMPATIBILITY_LEVEL = 130
  

  Do you have proprietary objects deployed in master db ?

  if yes, can you check they are still working as expected?

  Keep in mind you can also revert to dblevel SQL2014 if needed.

  ALTER DATABASE [master] SET COMPATIBILITY_LEVEL = 120
  

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• Mike Scalise

  SSCrazy Eights

  Points: 8145

  More actions

  April 1, 2021 at 3:37 pm

  #3864459

  Thanks, Johan! I appreciate the response!

  Mike Scalise, PMP
  https://www.michaelscalise.com