Sql08: New Data on our server. Think and avoid defaults

With SQL Server you can fall very easily into what is usually said about Microsoft products "Next, Next and ready." We will not deny it, Microsoft gets to have people with little idea go ahead and it is a merit. But let the issue.

If you start a new application and we must create the data structure, do not let the developers alone and not to use the MS Management Studio. Normally, as the database is concerned, they are created taking into account several things:

  1. Proper adjustment of the data types for each column
  2. Foreign Keys and Indexes
  3. Default size of log files
  4. Fillfactor in rates

The first two points are good practices that are usually comment but little else can we do as stewards of the database, but make sure that takes place and help if needed.Also the subject index is something that can be raised later. But the last two points are responsible.

We should know the type of application, the use to be (rd / ESCR) and estimate the volume of data growth in order to contribute our grain of sand. Initially we can adjust the size of the files in the database (properties database). If we hope to assemble a database that will grow very fast will increase the initial size if needed and adjust the growth of the registry files (. Ldf) and row data (. Mdf).

If, however, is small we could leave the defaults. Setting this value to avoid spreading the data to disk (are the two pictures below). Another issue to take into account and also having an impact is to play with the values ​​of fillfactor rates, also based on the percentage of read / write and data volume.

Archives database

Configuring the automatic growth.

That among other things we'll talk.It is also interesting that if you have low disk space for data and no longer applies only to the emptying SHRINKFILE logs, before a crisis situation we can play with the priorities and set limits on the growth machine to certain databases which grow excessive ...