Web Wiz - Green Windows Web Hosting

  New Posts New Posts RSS Feed - MSSQL new DB creation weirdness
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

MSSQL new DB creation weirdness

 Post Reply Post Reply
Author
theSCIENTIST View Drop Down
Senior Member
Senior Member


Joined: 31 July 2003
Location: United Kingdom
Status: Offline
Points: 440
Post Options Post Options   Thanks (0) Thanks(0)   Quote theSCIENTIST Quote  Post ReplyReply Direct Link To This Post Topic: MSSQL new DB creation weirdness
    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.
Back to Top
michael View Drop Down
Senior Member
Senior Member
Avatar

Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
Post Options Post Options   Thanks (0) Thanks(0)   Quote michael Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
theSCIENTIST View Drop Down
Senior Member
Senior Member


Joined: 31 July 2003
Location: United Kingdom
Status: Offline
Points: 440
Post Options Post Options   Thanks (0) Thanks(0)   Quote theSCIENTIST Quote  Post ReplyReply Direct Link To This Post 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.


Edited by theSCIENTIST - 16 October 2006 at 5:36pm
Back to Top
theSCIENTIST View Drop Down
Senior Member
Senior Member


Joined: 31 July 2003
Location: United Kingdom
Status: Offline
Points: 440
Post Options Post Options   Thanks (0) Thanks(0)   Quote theSCIENTIST Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
michael View Drop Down
Senior Member
Senior Member
Avatar

Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
Post Options Post Options   Thanks (0) Thanks(0)   Quote michael Quote  Post ReplyReply Direct Link To This Post 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
Originally posted by sql-server-performance.com sql-server-performance.com wrote:


If you are worried about getting torn pages in your SQL Server databases, you can have SQL Server tell you if they occur. There is a database option called "torn page detection" that can be turned on and off at the database level. Each database must be set separately. Keep in mind that this option does not prevent torn pages; it only tells you if you have one. Once it discovers one, your database is marked as corrupt, and you have little choice but to restore your database with your latest backup.

Auto Shrink I would turn off, see over time how often they need shrinking.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.


Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.