Adjusting Model

Question

Adjusting Model

Steve Jones - SSC Editor

SSC Guru

Points: 736210
More actions
September 3, 2015 at 10:21 pm

#307397

Comments posted to this topic are about the item Adjusting Model

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

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

mark.fowler Grasshopper Points: 13 More actions · Answer 1

I always adjust the Model for the data base I am setting up, particular file locations, and I like to increase the starting size of files to at least 100 mb

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 2

I typically made the following changes:

1. Change the file growth settings from percentages to fixed growths of 512MB (or 1GB depending on what the server is used for - and turn on IFI at the OS-level)

2. Set a fixed starting size for the default data and log files

2. Ensure the page verification is set to CHECKSUM

The rest of the defaults seem good enough to me (for what I've always needed :-D)

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

John Bigler SSC-Addicted Points: 450 More actions · Answer 3

Yes, I almost always adjust the model db as well. Just like MyDoggieJessie does.

- John B. (Pittsburgh, PA)

Xavon SSCrazy Points: 2280 More actions · Answer 4

Never touched the model before, myself. But after reading that blog, I am seriously considering adding a tally table to it whenever possible.

christopher.gray Old Hand Points: 372 More actions · Answer 5

I adjust file locations, growth, and starting size. On development servers, I change the recovery model to SIMPLE. I also add a series of objects to model, mostly database-specific metadata queries like table valued functions to identify certificate-signed objects or role membership chains, etc.

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

As part of all instance installs I change the default initial size and growth settings to protect against lazy database creations by people or application installers.

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

Dan Guzman SSCommitted Points: 1506 More actions · Answer 7

I always change the model recovery model to SIMPLE for development and QA instances since the recovery plan is to restore from the latest FULL and DIFFERENTIAL backup.

David.Poole SSC Guru Points: 76000 More actions · Answer 8

Where there are utility procs such as those used to test for absent clustered indexes, primary keys, use of reserved words etc it makes sense to install these in the model database as any new database will automatically gain these objects.

LinkedIn Profile

Andrew..Peterson SSCertifiable Points: 6838 More actions · Answer 9

Yes, years ago when I was doing more DBA type work, I would adjust the model.

The more you are prepared, the less you need it.

TheDBA SSC Journeyman Points: 82 More actions · Answer 10

I absolutely change the model DB when setting up a new SQL instance. The most grievous problem I find on instances set up by obviously "reluctant" non-DBAs is with file growth, leaving it to the default 10% (which is VERY bad for new, smaller DB's especially, as this forces many unnecessary and very expensive growth operations). I change this to a set size (1GB data, 512MB log) and depending on the instance cap it, or don't. The recovery will change depending on the environment (e.g., full logging for production, simple for development). Finally, I'll modify the auto update stats (Y) and async update options (Y).

MMullane-222251 Old Hand Points: 379 More actions · Answer 11

Depends on the instance. Environments where one or more dbs are created for each customer, for example, I'll adjust model so that it works well for the application(s) involved. Sometimes I'll use a script after the db is created.

I'll usually change file locations, the recovery model gets set to Simple in the event someone creates a new db while I'm away and they don't know enough to do txn log backups and file growth gets set to a size rather than pct.

JeremyE SSCoach Points: 16199 More actions · Answer 12

I modify the initial size, autogrowth, and switch the recovery model to SIMPLE for dev/test/QA environments.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 13

george sibbald (9/4/2015)
As part of all instance installs I change the default initial size and growth settings to protect against lazy database creations by people or application installers.

Ditto

SQLGrinder SSC Journeyman Points: 95 More actions · Answer 14

After each SQL install I run a script to change numerous setting and setup the environment before and data touches the instance. With model database I set initial size, autogrowth, and set recovery model to SIMPLE.

Joseph D’Antoni did a presentation on Building Perfect Servers Every Time

Kevin Boles did a presentation on SQL Server Defaults SUCK!!