Database Files in SQL Server 2005

Overview

In the Database world the main important factor is how well you create your database. This includes the different database files like data file which is used to store the data and transaction log file which is used to store all the transaction information.

In this article, I would like to give you an overview of Database files in SQL Server.

Database Files

In SQL Server 2005, a new user database is really a copy of the Model database. Everything in the Model database will show up in your newly created database. Once the copy of the database has been made, it expands to the requested size. When you create a database in SQL Server 2005, you must specify at least one file to store the data and hold your system tables and another file to hold the transaction log.

Databases can comprise up to three file types. Primary data files have a default extension of .mdf. If you create a database that spans multiple data files, then secondary data files are used, which have a default filename extension of .ndf. The transaction log is stored in one or more files, with a default .ldf extension. Additional transaction log files, however, don’t change their extensions. You should remember several important facts about your data and log files:

1. It is good to create the data and log files on a storage area network.

2. Only one database is allowed per data file, but a single database can span multiple data files.

3. Transaction logs must reside in their own file; they can also span multiple log files. i.e. you can have multiple transaction file file single database.

4. SQL Server fills the database files in a filegroup proportionally. This means if you have two data files, one with 200MB free and one with 400MB free, SQL Server will allocate one extent from the first file and two extents from the second file when writing data. In this way, you can eliminate hot spots and reduce contention in high-volume environments.

Transaction log files are not filled proportionally; instead, they fill each log file to capacity before continuing to the next log file.

When you are creating a database, please don’t specify the size of a transaction log, the transaction log will be resized to 25 percent of the size of your data files.

It's a good practice to place your transaction logs on separate physical hard drives. In this way, you can recover your data up to the second in the event of a hardware failure.


The big question now is why do we need secondary database files when I can store data in primary database files? Well this has certain advantages and disadvantages. The main disadvantage of multiple database files is administration. You need to remember these different files, their locations, and their use. The main advantage is that you can place these files on separate physical hard disks, avoiding the creation of hot spots and thereby improving performance. When you use database files, you can back up individual database files rather than the whole database in one session.

When you create a database, you are allocating hard disk space for both the data and the transaction log. You can store your data files using a variety of methods, depending on your hardware and software.