Home > Backup and Recovery, General Administration, Tuning and Optimization > Changing your default settings for any new database is just compulsory

Changing your default settings for any new database is just compulsory

What a statement, eh? All over a sudden and out of the blue. Never bothered to alter any default settings for a to-be-created database myself and haven’t heard others doing that on a large scale. However, after hitting this article by Glenn Berry, (which references couple of other related posts One | Two by Michelle Ufford and Kimberly Tripp) I realized this is serious. And I’m very serial about this! (“South Park” is sometimes retarded and disgusting, especially that first episode of 15th season, but in 9 out of 10 – it’s brilliant!)

In short, to summarize what the guys already said: excessive number of transaction log file extents, – Virtual Log Files, will lead to lengthy restore/recovery times. As simple as that. Besides, you want to save on execution time and server workload when having multiple Transaction Log File extents following one another during “heavy” DML ops. In addition to that, having multiple small VLFs in Transaction Log File will make it unnecessary big in size, which eventually will require its truncate and shrink. Too much hassle for such a silly reason as not-really-suitable default settings, which are very easy to change.

Hence, right after the new installation of SQL Server, go and change the model db datafile and transaction log file settings from default to… whatever you feel like after reading those two articles, but the main point is – CHANGE IT!

GUI-way:

Script-way:

USE [master];
ALTER DATABASE [MyNewDB] MODIFY FILE ( NAME = N'MyNewDB_Log', FILEGROWTH = 65536KB );

Advertisements
  1. June 2, 2011 at 14:05

    Thanks for the mention!

    • June 2, 2011 at 21:19

      The pleasure is all mine 🙂

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: