Well, besides the obvious trouble of not getting enough mirror time for myself, of course…no, this is about the model database for SQL Server.
The other day I had the need to create a new database. I also had the need to create it for a specific size. What I didn’t have a need for is to be greeted with an error message:
Msg 1803, Level 16, State 1, Line 1
The CREATE DATABASE statement failed.Â
The primary file must be at least 5 MB to accommodate a copy of the model database.
Um…OK…so apparently I can’t have a database created that is SMALLER than the size of the model database, but it is perfectly acceptable to create one that is LARGER. Why is that, exactly?
Well, lets see what MSDN says about the model database, shall we:
When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database. The rest of the new database is then filled with empty pages.
If you modify the model database, all databases created afterward will inherit those changes. For example, you could set permissions or database options, or add objects such as tables, functions, or stored procedures.
OK, so the first part of the database is created by copying in contents of the model, and the rest is filled with empty pages. That means I have no problems if I try to create a database that is larger than the size of model. Got that. And I understand that I could use model as a place to create objects that would be propagated to all newly created databases afterwards (and how many of you out there ACTUALLY use model for this?)
Read that first statement from MSDN again. And again. Go back for another time. Got it? OK, here is my question:
If I issue a CREATE DATABASE statement, and I have specified a size that is less than the size of the model database, then why can’t SQL Server create a new database that contains ONLY THE OBJECTS in model, and stop at the point where it wants to fill the rest of the new database with empty pages? They could even raise an informational message afterwards saying “Hey, we know you wanted something smaller than model, but the smallest we could do for you is this size because model contains a crapload of objects”.
If I were to resize model to 100GB, then every subsequent new database would need to be that size, no less, even though 99.9% of the model database is full of empty pages.
Sorry, but that doesn’t make sense to me. We should have the ability to tell SQL Server that we only want the objects from model, and not all the extra empty space. Right now it is all-or-nothing, and I don’t understand any technical reason for why it must remain that way.
I’m not sure I want to file a Connect item on this one quite yet, but I would like to talk it out with a handful of people to see what options would be feasible for a future release.
Although I suspect that the answer is going to be ‘Window Azure SQL Database’.
Well, it copies the model database files, so I could understand it failing if there wasn’t enough disk space to copy the file. However, it should be fairly easy to implement a fix for this. It could still copy the model files, but if the specified size is smaller than the model file size, it could do a DBCC ShrinkFile … With TruncateOnly to shrink the file to the requested size (or as close as possible until it ran out of free space). Seems like an easy solution for them to implement.
Tom–
I disagree–I use this feature in model to make sure other DBAs don’t create 1 MB databases that grow in 1 MB increments.
But having the option to control, or easily override (with a switch) would be nice, as some apps have hard coded DB create scripts–I’m looking at you Blackberry.
Joe,
Exactly, there should be a way to override the behavior.
I would be interested to see how much space is currently being used by the objects in a default model database. Is it really 5MB worth of objects?
To me this does make a bit of sense. There may not be empty pages in the model database, and to have to validate that on every call to CREATE DATABASE would be a major performance drain. Whereas a file size check is very performant and easily done.
Dale,
I understand the performance issues but would still like the option.
My bet is that it’s doing something like a restore so there needs to be enough space in the created file to get all the pages down. However, it does seem like it should be possible to just copy allocated pages and not worry about the rest.
Go check out your tempdb – that’ll answer this for you a bit. i.e., the REASON it works like this because SQL Server is just making a dumb/blind copy of the model database when it spins up.
But, you already knew that 😉
So, yes, I totally agree with you. There need to be some decent changes in this arena:
A) We should be able to tell SQL SEerver that new DBs only inherit structures, perms, etc (or combinations there-of – some sort of CREATE DATABASE… WITH MODEL_OPTIONS (x,y,z) or soemthing. YES, it would make things a bit more complex, but that’s truly what we all want.
B) We need to be able to tell SQL Server that when it spins up the TEMPDB, it uses MODEL_OPTION (FRIGGIN-NOTHIN’) or whatever – so that tempdb doesn’t have your objects,perms, etc in-tow.
This really needs to be changed.
Michael,
I like the way you think, those are great suggestions.
1. In SQL 2008R2 & all the WinNT versions I can recall, Model has been 3MB. Perhaps the OS/2 versions with their 2K Page size might’ve been 2MB I can’t recall.
So I’m unclear what you did to your Model DB to make it bigger.
2. The system tables & indexes in Model are approx 1MB. If you shrink your Model.mdf, you might be able to reduce it to 2MB. I don’t think you can make the log file < 1MB. So that might get it back to 2MB. Is that sufficient for you?
3. I believe space saved by deleting any system tables is not worth the pain of an unpredictable set of bugs it may cause.
4. If the issue is 100's of tiny DB's consider putting them all in a single & using schemas to separate them.
5. Where data is that small & disk space is so tight, perhaps SQLCE, XML or a binary dump of a memory structure is more suitable architecture than SQL Server.
Hi David, thanks for the comment.
I manually increased the size of model, as many DBAs do in shops everywhere. I did so as a test to see if I could create a database smaller than model. I couldn’t.
I understand I could shrink files, either the model files, or my database files after having created the database. That is not what this is about. My point is that I shouldn’t have to take these steps. For example, if a DBA has set their model to be 1GB by default, which we know if 99.8GB empty space, I should be able to create a new database without the empty space.
The issue isn’t about too’s of tiny databases. It is about one database, that I want to create, that is smaller than model.
The issue isn’t disk space, the issue is architecture and how SQL uses model but doesn’t allow for me to create something smaller. I should have the option to ONLY have a database created with ONLY the necessary 2MB (or whatever it is) of metadata and objects and IGNORE any extra empty space.
Thanks!
@David,
One of my clients has a multi-tenant architecture – a single server houses 30+ different agencies. Each ‘agency’ is a medical organization – with all sorts of confidential information. Ergo, each database needs to be distinct.
However, new clients/agencies spin-up every six months or so – and need 300+ tables and a few hundred sprocs, roles, etc.
Dumping all of these structures into model database is exactly how you’re supposed to use the model db – meaning that all new dev changes/etc are synced or fired off against the model db – so that when a new agency is created, it’s as simple as ‘CREATE DATABASE ‘ and they’re up and running with the latest and greatest.
However, this makes the model db 250MB in size (to hold all lookups and other ‘basic’ stuff).
So, in cases like this, it’s a bit of a pain that you can’t make new databases smaller than the model – but of course, HOW could you? You can’t make 250MB of stuff take up less space.
Likewise, it’s lame that my tempdb has a copy of ALL of these tables and lookups in it. YUP. I’ve used the model database EXACTLY as defined, but now I’ve got ‘turd’ tables and other artifacts in the tempdb.
Hence the reason I’d REALLY like to see an option where tempdb wouldn’t be populated (as an option) with what’s in the model db, and where you could also not create new databases from the copy of all the stuff in the model as well.
And yeah, that sounds weird… but makes perfect sense if you think about it too.