x

compatibility level of master database

Ik have a SQL server 2005 database with a master database with a compatibility level of SQLServer 2000 (80).

Why would someone do this? Can I change this without a risk?

Any suggestions would be helpfulle, because I can't imagine why this has been done.

Rob

more ▼

asked Mar 08, 2010 at 11:44 AM in Default

Rob 2 gravatar image

Rob 2
57 3 3 3

Looking forward to answers. I wouldn't have thought this was even possible.
Mar 08, 2010 at 12:03 PM Grant Fritchey ♦♦
+1 on question and Grant's comment. Like... WTF??
Mar 08, 2010 at 12:53 PM Matt Whitfield ♦♦
+1 Grants comment
Mar 08, 2010 at 01:21 PM CirqueDeSQLeil
(comments are locked)
10|1200 characters needed characters left

6 answers: sort oldest

Someone may have implemented a proc in master that doesn't work in 2005. I know a few third-party applications thar put code in master.

So... you can change it, but you definitely run a risk of breaking something.

more ▼

answered Mar 08, 2010 at 06:32 PM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

(comments are locked)
10|1200 characters needed characters left

I think the best answer for you right now would be to try and replicate the environment. Setup a server and set a master database to compat 80. Change it back to 90 and then test for any issues.

I'm with Grant and Matt on how this could even have happened - or why.

Just tested it personally (also had some other Ninja's test and they concur) - set it back immediately.

more ▼

answered Mar 08, 2010 at 01:02 PM

CirqueDeSQLeil gravatar image

CirqueDeSQLeil
4k 10 11 15

(comments are locked)
10|1200 characters needed characters left

Some light reading from the CSS engineers..

here

more ▼

answered Mar 08, 2010 at 01:52 PM

sp_lock gravatar image

sp_lock
9.1k 24 27 31

(comments are locked)
10|1200 characters needed characters left

I'll post it, but I won't take credit for it. According to Gail Shaw, this is no big deal. Someone was messing around or screwed up, but you can just fix it.

"Someone being stupid. I'd say set it back to 90 immediately."

For what it's worth. I'm fairly certain she's right (she usually is).

more ▼

answered Mar 08, 2010 at 02:54 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98k 19 21 74

+1 @ Grant Shaw :)
Mar 08, 2010 at 06:00 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

I found out that the master AND the msdb are being used as user databases!

more ▼

answered Mar 09, 2010 at 04:02 AM

Rob 2 gravatar image

Rob 2
57 3 3 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x8

asked: Mar 08, 2010 at 11:44 AM

Seen: 2141 times

Last Updated: Mar 08, 2010 at 11:44 AM