MSSQL new DB creation weirdness
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=21607
Printed Date: 29 March 2026 at 10:54pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: MSSQL new DB creation weirdness
Posted By: theSCIENTIST
Subject: MSSQL new DB creation weirdness
Date Posted: 14 October 2006 at 8:43am
|
When I create a new DB in SQL server 2000, I noticed that the new DB is a reflection of the model DB, but my model db is 5MB in size so all new DB will be 5MB even if theres no data on them. 1st question; can I set my model DB to be 100KB (or whats the lowest possible?) so all new DBs are also small from the start?
The model DB also has some tables and views defined on it, and my new DB comes with all those tables (all 20 of them) and views (2).
2nd question; do I need these (sys...) tables and views in my new DB? Also my new DB has out of the blue 30 stored procedures that the model DB don't have, do I need these or what is going on?
Thanks. ------------- :: http://www.mylittlehost.com/ - www.mylittlehost.com |
Replies:
Posted By: michael
Date Posted: 16 October 2006 at 4:42pm
|
Changing the ModelDB is more an advanced subject and I would be careful doing so only after you backed it up.
You can change the Model DB size, no problem but there is a bottom threashold as it must be able to hold the tables, views, functions and procedures itself. I would not delete any system views, functions or procedures off the model db unless you know what they are doing and are 100% certain you will never need them. It is just not worth stripping sutff for the sake of 10KB ------------- http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker |
Posted By: theSCIENTIST
Date Posted: 16 October 2006 at 5:35pm
|
Ok, I won't change any of the tables in model as I just noticed it holds quite alot of necessary info, as a matter of fact I won't change any of the model data, but I would like it to be of a reduced size as 5MB is too much.
So I went to db shrink, backed it up first, shrunk it, great, it is now 1.38MB just enough for the info it holds.
I then went to create a db from asp.net script, and it no longer creates it gives this error:
Error: Could not obtain exclusive lock on database 'MODEL'. Retry the operation later. CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.
Hummmm I said, right, went to EntMan and created a db there no problem, but from script I can't, restored model from backup, it's back at 5MB, and I can't still create db from script, what's going on?
Advise please. ------------- :: http://www.mylittlehost.com/ - www.mylittlehost.com |
Posted By: theSCIENTIST
Date Posted: 16 October 2006 at 5:49pm
|
UPDATE:
It now creates dbs even after I shrunk model again, I guess it just needed some time, I have now a couple more questions.
1. Why would a db need "Space available", my new dbs now have a 500KB of space available, and a size of 1.24MB just like model, they are set to grow by 10% so why is the space available thing on them? Should I add more space available?
2. In the options tab is have the following ticked in model which will be set on all new dbs: Auto update statistics - Torn page detection - Auto shrink - Auto create statistics ... Anything I should know here, or should disable? I read about this things, but there was no explanation settings that are beneficial to have on model so all new dbs inherit it.
Thanks guys. ------------- :: http://www.mylittlehost.com/ - www.mylittlehost.com |
Posted By: michael
Date Posted: 16 October 2006 at 7:43pm
|
Well you want to have space available, depending on what info the db is going to hold. If your space available is little it needs to expand the data file every time you insert data, which makes the whole process slow.
As you appear to be under some kind of weird space constraints, all you can do is trial-error. I have never dealt with such small file (requirements) As far as the options you mentioned go: Auto update statistics: For simple DB's you can disable that. Essentially, it can enhance performance on queries thus you can always create statistics manually if required. Torn page detection: I would leave that on
Auto Shrink I would turn off, see over time how often they need shrinking. ------------- http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker |
sql-server-performance.com wrote: