Microsoft SQL Server databases contain two primary file types required for proper functioning of a OS. They are:
- Data files and
- Log files
Data Files
A data file contains data and objects. Based upon the requirements and usage, a Microsoft SQL Server database is further classified into two types of data files:
- Primary data file
Each database will have only one primary data file where all the tables, indexes, stored procedures (database objects) and views are stored.
The recommended file extension for this file is .mdf.
- Secondary data file
The secondary data file is used when a database exceeds the maximum allocated size for a single file on Windows and helps the database to continue to grow.
The recommended file extension for this file is .ndf.
Log File
A log file contains information of all the transactions on a database which is required to recover an entire database. There will be only one log file per database where DB transactions are written to the log file even before they are written to the data file.
The recommended file extension for this file is .ldf, which is Transactional Log file.
Microsoft SQL Server does not enforce the .mdf, .ndf, and .ldf file name extensions.
Internal Structure of Data File:
- Storage space allocated to a database is divided into pages that are contiguously numbered from 0 to N.
- When Microsoft SQL Server starts expanding the database file from its default size, the newly created pages are numbered starting from the (last highest page number in the file)+1.
- Similarly, when SQL server shrinks the file it removes pages descendingly (starting with the highest page number) from the database file.
- A page is the basic unit of I/O for SQL Server operations and each page is a 8 KB chunk.
- Disk I/O operations (read/write) are performed at the page level. A data file is a large array of pages.
SQL Server uses multiple pages to store different data within a database. Some of them are: Data, Index, IAM, SGAM, GAM and TextMix.
Page Addressing:
- Every page in SQL Server has the same size that is 8192 bytes and each page has a unique address.
- A page is part of a single database file. The file ID makes the first part of the unique address.
- Within each file, pages are continuously numbered starting from 0. To uniquely identify a page, the page address is usually written in the below format:
<file number>:<page number>
(i.e) 1:0 identifies the first page in file 1 which goes on as 1:1, 1:2 etc.,
As we mentioned, many page types are used by a database; Among them data page is the most important one which is used to store records (rows in a table) to a database.
Internal Structure of Data Page:
A data page consists of three sections.
- Page Header
- Page Body
- Slot Array or Row offset
The total size of a page (8192 bytes) is divided among these three parts.
- Page Header(0-95 bytes)
The first 96 bytes of the page is a page header which contains the page information.
(i.e) Page ID, No of records in the page and IDs of previous-next pages.
- Page Body
Page body is the area where actual data is stored which is followed by the free space and slot array.
- Slot Array
At the very end of the page is a section known as the record offset array, or slot array, which is an array of two-byte values that SQL Server reads in reverse from the very end of the page.
Each slot in the record offset array points to a byte in the page where a record begins.
As such, the very last record on the page logically may very well be the first record physically.
Typically, you’ll find that the first slot of the record offset array stored in the very last two bytes of the page pointing to the first record stored at the page.
A schematic diagram of data pages looks like as below:
You can see from the above diagram the first 96 (0-95) bytes of the page is a header and followed by them, the records are stored.
Each record is of 22 bytes of size in the above diagram and the pointer to the records are stored at slot array starting from the end of the page.
Vembu BDR helps you get your Microsoft SQL Server backed up quickly and safely with our image backups and application aware features.
Simplify your restore process with our recovery tool for SQL backups: Vembu explorer.
Do try Vembu BDR v3.0 today and let us know what you think!
Follow our Twitter and Facebook feeds for new releases, updates, insightful posts and more.
