Database is nothing but it is a
logical collection of data, Data will be stored in two different sets of
files DATA (.mdf and .ndf) files and LOG (.ldf) files which are database
resources.
Let’s see
when we are creating a database.
This stores the actual data, we can create multiple data
files, and each data file will be 4 MB by default when we create a database.
Max size of each data files is said to be 16 TB. And by default log size is 1
MB. By default it will increase 1MB and log file 10%. We can set the size of it
also.
I have increase the size of the files
We can have
multiple ndf files and log files in a database but only one, we can create mdf.
Adding ndf
files in the database
For adding
the ndf files click on the add
Here I am adding 2 ndf files.
Click ok. Now the database has been created with 1 mdf, 1
ldf and 2 ndf files.
We can create the database using below sql script also
CREATE DATABASE
[Test]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Test', FILENAME = N'c:\Program
Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Test.mdf' , SIZE = 10240KB , FILEGROWTH = 10%),
( NAME = N'Test_ndf1', FILENAME = N'c:\Program
Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Test_ndf1.ndf'
, SIZE = 10240KB , FILEGROWTH = 10%),
( NAME = N'Test_ndf2', FILENAME = N'c:\Program
Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Test_ndf2.ndf'
, SIZE = 10240KB , FILEGROWTH = 10%)
LOG ON
( NAME = N'Test_log', FILENAME = N'c:\Program Files\Microsoft
SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Test_log.ldf' , SIZE = 5120KB , FILEGROWTH = 10%)
|
Database has
been created.
All file are stores by
default on c:\Program Files\Microsoft SQL
Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\
We can change it also.
Four files
are created
Data File:
A database
will have 2 different data files
Ø
MDF (Master Data File)
Ø
NDF (User Data File)
MDF (Master Data File)
Ø
This is a default file we get when a database is
created.
Ø
We cannot delete this file and also we cannot create
one more MDF file, there will be one and only MDF file per database.
Ø
Without this file database will not be
available.
Ø
This stores user data along with some METADATA
(Data about Data/ Definition of data) about the database configurations like
collation, recovery model, user’s information, roles…
Ø
File extension of this is .mdf
NDF: (User Data File)
Ø This
is a secondary data file; we can create multiple ndf files with any extension.
Ø It
is not necessary to have a secondary data file. Therefore some databases may
not have any secondary data file. But it is also possible to have multiple
secondary data files for a single database.
Ø Data
files can be grouped with filegroups
Ø It’s
also possible to store the secondary data file in a separate physical drive
than the one which primary data file is stored.
This file store all the transactional
(insert, update create and delete. Except select) logs in sequential order,
each transaction will be given a unique number call LSN (Log Sequential
Number). We can create multiple log files per database. Log files cannot be
grouped under file groups. The maximum size of each log file is 2 TB.