The Model Database

Question

The Model Database

free_mascot

One Orange Chip

Points: 27164
More actions
February 17, 2014 at 8:12 pm

#283800

Comments posted to this topic are about the item The Model Database
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 2

The following operations cannot be performed on the model database:
...
Setting the primary filegroup to READ_ONLY.

Damn you MSDN!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Neil Burton SSC-Insane Points: 23069 More actions · Answer 3

Koen Verbeeck (2/17/2014)
The following operations cannot be performed on the model database:
...
Setting the primary filegroup to READ_ONLY.
Damn you MSDN!

+1!!!!!!

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 4

pmadhavapeddi22

SSCertifiable

Points: 5306

February 18, 2014 at 1:16 am

#1689762

I also got it wrong after reading MSDN :hehe:.

Hany Helmy SSChampion Points: 13921 More actions · Answer 5

Koen Verbeeck (2/17/2014)
The following operations cannot be performed on the model database:
...
Setting the primary filegroup to READ_ONLY.
Damn you MSDN!

+ 1

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 6

Just a simple & basic info to share: "if you changed the model database to Read-Only mode; then any newly created database will be in Read-Only mode by default".

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 7

Hany Helmy (2/18/2014)
Just a simple & basic info to share: "if you changed the model database to Read-Only mode; then any newly created database will be in Read-Only mode by default".

That must be useful. Empty databases on read-only mode.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Koen Verbeeck (2/18/2014)
Hany Helmy (2/18/2014)
Just a simple & basic info to share: "if you changed the model database to Read-Only mode; then any newly created database will be in Read-Only mode by default".
That must be useful. Empty databases on read-only mode.

Very useful - a database guaranteed to contain no corrupt data :hehe: as long as no-one with enough privileges changes its state.

Tom

paul.knibbs SSCoach Points: 15320 More actions · Answer 9

Hold on a second here. As far as I'm aware, the model database is used to create tempdb--so if model is actually read-only, wouldn't that then make tempdb read-only, and thus cause some really interesting problems? (Oh, and I also got it wrong due to reading the thing in MSDN...).

James Lean SSCertifiable Points: 5757 More actions · Answer 10

Even if you put the model db in read-only mode, any new databases you create will NOT inherit that property.

I assume this holds true for tempdb but I haven't tested it.

**EDIT** My assumption seems to be correct - after server restart, model remained read-only but tempdb was read-write.

-----
JL

Ed Wagner SSC Guru Points: 287026 More actions · Answer 11

BWFC (2/18/2014)
Koen Verbeeck (2/17/2014)
The following operations cannot be performed on the model database:
...
Setting the primary filegroup to READ_ONLY.
Damn you MSDN!
+1!!!!!!

+1. MSDN is wrong again. I thought I remembered reading something about this, but it's a ways back in the memory banks, so I looked it up. Since I was wrong, I just had to try it and it is allowed.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

kapfundestanley SSCertifiable Points: 5637 More actions · Answer 12

Nice information,but how do you define "newly created"?

“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

george sibbald SSC Guru Points: 104210 More actions · Answer 13

paul.knibbs (2/18/2014)
Hold on a second here. As far as I'm aware, the model database is used to create tempdb--so if model is actually read-only, wouldn't that then make tempdb read-only, and thus cause some really interesting problems? (Oh, and I also got it wrong due to reading the thing in MSDN...).

a new database only inherits ALL the properties of model if you use the GUI to create it and take the defaults. Creating a new database via TSQL with no parameters only the initial size of the .mdf file is inherited from model.

---------------------------------------------------------------------

James Lean SSCertifiable Points: 5757 More actions · Answer 14

Actually, MSDN is NOT wrong. You cannot make the primary filegroup read-only. This is actually a restriction on ANY database.

You can make the whole database read-only however.

So, this works:

ALTER DATABASE model

SET READ_ONLY

But this doesn't work:

ALTER DATABASE model

MODIFY FILEGROUP [PRIMARY] READ_ONLY

And, as I said above, even if you make model read-only, new databases will be created read-write.

-----
JL

The Model Database

Cookies on SQLServerCentral