SQL Server Data Components – How Data is Stored

SQL Server Database is a combination of different data files and components as listed below. Here we will see how data is stored in database and we will discuss it’s components.

Basically there are three types of files which completes a database.

1. Primary data file (also called Master Data File or .mdf)

2. Secondary data files (also called Non-primary Data File or .ndf)

3. Transaction Log file (also called Log Data File or .ldf)

Primary Data File

When you create a database, at that time primary data file is created. This primary data file is used during start-up of the database and is linked to other files. One primary data file is associated with only one database which contains database objects like tables, stored procedures, functions, views and indexes etc. Default extension for primary data file is .mdf but if you want you can choose your own file extension also during database creation step because .mdf extension is not compulsory but it is recommended for primary data file.

Secondary Data File

Secondary data file is similar to master data file which also contains database objects like tables, stored procedures functions, views and indexes etc. but this data file is optional and it is created by users when one data file is full. One can also create secondary data file to boost the performance by spreading data files across different disk drives. It is also called non-primary data file and recommended file extension is .ndf but you can choose your own file extension also.

Transaction Log File

Transaction log file is required to recover the database at the time of failure and to minimise the data loss. With every database, one log file is associated which keeps record of all transactions. Log files are very important and used in high availability and disaster recovery scenarios like log shipping, replication and database mirroring. When you perform any data manipulation operations, data pages are fetched into memory and it is modified as per query requests. In this operation some dirty pages are created which are written to transaction log and during background process which is called checksum, the dirty pages are written to database files.

Other data files

Whatever data is available in database, it is written on the file pages of 8KB size. This 8KB is shared between header information and data. These pages are sequentially arranged and to process these pages, database engine requires page number and file ID associated with each page. Each page keeps related single database objects only. When 8 contiguous pages are groups together it is called extent. This extent is used to manage and organise data pages in database.

CLICK HERE to watch live practical.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

You may also like...

8 Responses

  1. SqlSimon says:

    Whilst I haven’t read the full article, it has one definite correction needed: In the log file paragraph, the word checksum is incorrect and the word should be check point. It is a check point operation that is performed, triggered by a log backup, amongst other triggers, which writes dirty pages from memory to disk and commits all completed transactions from the log file into the database data files. let me know of any corrections needed to my explanation 🙂 thank you

    • Thank you Simon, I have replied to you on facebook group, here checksum is a backround process so during that process dirty pages are written to database files. Rest whatever you written is absolutely right. 🙂

    • Hi Simon,

      I need to correct your comment that checkpoint operation does not commit the transactions, it just make sure that the dirty pages are written into the disk.

      Best Regards
      Hamid J. Fard

  2. Hi,

    I believe that is not the checksum operation, it is checkpoint process which checks whether all the dirty pages in memory are written into the disk but Checkpoint process does not write any dirty page into the disk, thats LazyWriter’s job to do, LazyWriter does manage the memory at same time, for example it can remove unused data pages from memory.

    One database object can have more than one data page, for example tables, indexes and so on.

    Best Regards
    Hamid J. Fard

    • Thank you Hamid for reading and highlighting this point. Actually in this sentence I wanted to convey as “during background process which is called checksum, the dirty pages are written to database files”. Thank you.

  3. Dyson says:

    DB calls made by your application using SQL Server data access components. Monitor performs per component tracing of SQL statement execution, commits, rollbacks, etc.

Leave a Reply

Your email address will not be published.