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_pathdatabase_name_data.mdf’),

(NAME=Database_name_log,

 FILENAME = ‘os_pathdatabase_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_pathdatabase_name_data.mdf’),

(NAME=Database_name_data,

 FILENAME = ‘os_pathdatabase_name_data.ndf’),

 (NAME=Database_name_log,

 FILENAME = ‘os_pathdatabase_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_pathdatabase_name_data.mdf' )
ALTER DATABASE database_name MODIFY FILE ( NAME = database_name_data, FILENAME = 'new_pathdatabase_name_data.ndf' )
ALTER DATABASE database_name MODIFY FILE ( NAME = database_name_data, FILENAME = 'new_pathdatabase_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




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |