SQL Server Database Architecture

SQL Server Database Architecture :

Data File : 

a.       Data file contains actual data , meta data and services .

b.      You can create data files up to 32,767 .

c.       Maximum size of a data file is :16 TB .

d.      We can view the details of data file by usingSP_HELPDB
              ex:    sp_helpdb  <database name> .

e.      To view free space present in data file you can use

       DBCC SHOWFILESTATS .

      Ex :  Use <DBName>

               GO

              DBCC SHOWFILESTATS

Calculation Method :

   ((Total extents – Used extents )*64 ) /1024 for total free space in MB .

f.        SQL Server supports 2 types of data files

1.       Primay Data File (*.mdf) .

2.        Secondary Data File (*.ndf) .

1 1. Primary Data File :

Primary data file is also called as master data file or meta data file .

It is an entry point for a particular database .

It contains about

                   System defined objects

                   Permissions granted on various objects

                    Details of other files .

Only one primary data file per database .

Note: .MDF Extension is as per Microsoft naming standard but you can give your own name also instead of mdf .

2. Secondary Data File :

   Consists of data and user defined objects .

   No limit for no of .ndf files in a db .

.       Transaction Log File (*.Ldf ) :

 a . Initially Every manipulation is recorded in T.Log file .

 b . It consists of both committed as well as uncommitted transactions .

 c . Once check point occurs all the committed transactions are taken             into       data file .

 d . Max size of T Log file is : 2 TB .

 e .  To view the Percentage log used

                  DBCC SQLPERF(LOGSPACE)

  f . To view the transaction present in log file

                   DBCC LOG(dbname)

                           (or)

                   DBCC LOG(dbname,flag) 1-4 flag range

  g . Consists of no of pages .

  h . Every transaction is recorded with a unique no called LSN .

Advantages :

  a. To reduce I/O transaction on data file .

  b. To Undo the transactions .

  c .To recover the data up to point of failure .

  d. To implement T.Log shipping , Transactional replication .