Setting Up Data And Log Files For SQL Server

When a database is created in SQL Server it creates two files by default: one is the data file with an extension of .mdf that stores the actual data, and the other is the log file with an extension of .ldf that stores the transaction log information used to recover data in disaster recovery scenarios. The mdf is the primary data file; in case of very large databases it might be useful to have secondary data files with an extension of .ndf in which case  the data will be stored in both mdf as well as ndf files.

The placement of mdf, ndf and ldf files on a particular drive is a matter of decision based on the size requirements of each file, and more importantly on the impact it will have on the overall performance of the database. As the log file is write-intensive it should be placed on a separate drive and the data files also  placed on separate drives of their own. Even secondary data files should be placed on a separate drives to optimize performance. Why? This is because, it increases parallelism while accessing the data and log files for read/write operations.

The log file is write-intensive, and the data file (depending on your application) is read/write intensive. Suppose you have multiple users simultaneously updating and requesting data. For data retrieval, the log file is not needed, and so the requests for data can be satisfied from the data file placed on its own drive. This would involve the movement of only the spindle of that drive on which the data file is kept. For data updates, the log file is heavily used, and so any write operations would involve the movement of the spindle of the drive on which the log file is placed. Now, if both log and data files are placed on the same drive, the amount of spindle movement would be heavy so as to satisfy both read and write operations. But, if you place the data and log files on separate disk drives, then the spindle movement for write operations won’t interfere with that of the read operations, since each drive’s spindle would be moving independently to satisfy read as well as write requests, thus improving the performance of data retrieval and data updates.

Ideally, the separation of data and log files on drives should be done while creating the database, but this can be done later also after the database is created and is in use. SQL Server is very flexible in that. The way to go about doing this is to use the two statements that SQL Server provides: CREATE DATABASE   and   ALTER DATABASE. Let’s look at both examples where database_name is the name of the database being created and/or altered, and os_path is the operating system file path.

While creating a database, you use the Create Database statement in the following manner:

CREATE DATABASE database_name ON

(NAME=Database_name_data,

 FILENAME = ‘os_path\database_name_data.mdf’),

(NAME=Database_name_log,

 FILENAME = ‘os_path\database_name_data.ldf’)

If you want to add secondary data files, the statement would be:

CREATE DATABASE database_name ON

(NAME=Database_name_data,

 FILENAME = ‘os_path\database_name_data.mdf’),

(NAME=Database_name_data,

 FILENAME = ‘os_path\database_name_data.ndf’),

 (NAME=Database_name_log,

 FILENAME = ‘os_path\database_name_data.ldf’)

If you already have a database and want to change the drive location of either the data or log file, then use the Alter Database statement in the following manner:

First, bring the database to an offline status by giving the following command:

ALTER DATABASE database_name SET OFFLINE

Next, change the location of either the data file or the log file with the following command for each file:

ALTER DATABASE database_name MODIFY FILE ( NAME = database_name_data, FILENAME = 'new_path\database_name_data.mdf' )

ALTER DATABASE database_name MODIFY FILE ( NAME = database_name_data, FILENAME = 'new_path\database_name_data.ndf' )

ALTER DATABASE database_name MODIFY FILE ( NAME = database_name_data, FILENAME = 'new_path\database_name_data.ldf' )

Finally,  bring the database back online by issuing the following command(but before giving this command make sure you copy the data and log files to their new locations):

ALTER DATABASE database_name SET ONLINE

After this you should verify the changed locations of the files by querying the sys.master_files catalog view:

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'database_name');

The above procedure is valid for both SQL Server 2008 and 2005.

Submitted by Amit Chaudhary