June 1, 2007 at 11:10 am
I am trying to research "best practices" on setting / changing size/space allocated for msdb and model db for new SQl server installation.
Can someone help me please?
kaolga
June 1, 2007 at 1:32 pm
I leave model as it is, I've never seen it grow. Remember, it is just a template to be used to create other databases.
MSDB...one of my servers with the most jobs has this database at just around 110 MB.
-SQLBill
June 1, 2007 at 2:28 pm
hi, I understand that model is just a template, but wouldn't make more sense to set space allocated to some value, so that the new databases created from this template assume the settings automatically?
June 1, 2007 at 4:47 pm
You could, but how do you know what space will be needed by the new database(s)? Let's say you set Model for 100 MB. Then you create five databases that are only 10 MB each and not expected to grow much larger. That's 50 MB of databases, but 500 MB of space reserved. You've wasted a lot of space. I prefer to CREATE the database at a small size and either let AutoGrow take care of the growth or manually increase the initial size.
-SQLBill
June 2, 2007 at 10:06 am
ok, so should we say that after inatalling SQl server we should not worry about resizing msdb and model bd? leve them with their default size?
thank you
June 4, 2007 at 12:22 pm
Definitely leave model alone. Hpwever the answer to resizing is the #1 answer in the Top 10 Things you do not want to hear from your DBA ... It depends ... there are many posts available in the forums that talk about sizing/autogrow/etc of user databases. All of this information applies to msdb as well. I have msdb databases ranging in size from 200 Mb to 1 Gb. It just depends on how many databases, jobs/tasks, backups (full/differential/transactional) and the history that you want to keep.
Since you are trying to standardize an initial server build I'll toss in a script I run on every new server to do what you are talking about.
---
--- alter_system_dbs.sql - 06-28-2006 Rudyx
---
--- to be executed on all new SQL Server Installations
---
use master
go
---
alter database master modify file
(name = master,
size = 25MB,
maxsize = 51MB,
filegrowth = 25MB)
go
checkpoint
go
alter database master modify file
(name = mastlog,
size = 25MB,
maxsize = 51MB,
filegrowth = 25MB)
go
checkpoint
go
---
alter database msdb modify file
(name = MSDBData,
size = 200MB,
maxsize = 10001MB,
filegrowth = 200MB)
go
checkpoint
go
alter database msdb modify file
(name = MSDBLog,
size = 25MB,
maxsize = 51MB,
filegrowth = 25MB)
go
checkpoint
go
---
alter database tempdb modify file
(name = tempdev,
size = 8192MB,
maxsize = 10241MB,
filegrowth = 2048MB)
go
checkpoint
go
alter database tempdb modify file
(name = templog,
size = 512MB,
maxsize = 1025MB,
filegrowth = 256MB)
go
checkpoint
go
---
--- end of alter_system_dbs.sql
---
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply