To  find the database files, file groups and file  growth  in sql server:

--select * from sys.sysaltfiles

SELECT DB_NAME(database_id) AS DatabaseName,

CAST([Name] AS varchar(20)) AS NameofFile,

CAST(physical_name AS varchar(100)) AS PhysicalFile,

type_desc AS FileType,

((size * 8)/1024) AS FileSize,

MaxFileSize = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'

WHEN max_size = 0 THEN 'NO_GROWTH'

WHEN max_size <> -1 OR max_size <> 0 THEN CAST(((max_size * 8) / 1024) AS varchar(15))

ELSE 'Unknown'

END,

SpaceRemainingMB = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'

WHEN max_size <> -1 OR max_size = 268435456 THEN CAST((((max_size - size) * 8) / 1024) AS varchar(10))

ELSE 'Unknown'

END,

Growth = CASE WHEN growth = 0 THEN 'FIXED_SIZE'

WHEN growth > 0 THEN ((growth * 8)/1024)

ELSE 'Unknown'

END,

GrowthType = CASE WHEN is_percent_growth = 1 THEN 'PERCENTAGE'

WHEN is_percent_growth = 0 THEN 'MBs'

ELSE 'Unknown'

END

FROM master.sys.master_files

WHERE state = 0

AND type_desc IN ('ROWS','LOG')

ORDER BY database_id, file_id.

To find the databases  log files  space usage:

Dbcc sqlperf(logspace)

To find the data files space usage:

create table #dbsize
(Dbname sysname,
--dbstatus varchar(50),
--Recovery_Model varchar(40) default ('NA'),
file_Size_MB VArchar(3000),Space_Used_MB Varchar(2000),used_percentage Varchar(2000),Free_Space_MB Varchar(2000))

insert into #dbsize(Dbname,
--dbstatus,Recovery_Model,
file_Size_MB,Space_Used_MB,Free_Space_MB,used_percentage)
exec sp_msforeachdb
'use [?];
  select DB_NAME() AS DbName,
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
--sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 *100 as used,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB ,
(sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 )*100/((sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0)+(SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0))AS used_percentage from sys.database_files  where type=0 group by type'

select * from #dbsize

drop table #dbsize

Types of Physical database files and file groups of SQL Server databases

Database Files:

SQL Server  databases have three types of files:

· Primary data files:

The primary data file is the starting point of the database and points to the Page1 other files in the database.

Every database has one primary data file.

The recommended file name extension for primary data files is .mdf.

· Secondary data files:

Secondary data files are the data files.

A database can have one or more data files.

Secondary data files are other than primary data file.

The recommended file name extension for secondary data files is .ndf.

· Log files

Log files hold all the log information that is used to recover the database.

There must be at least one log file for each database, although there can be

more than one.

 The locations of all the files in a database are recorded in the primary file of the database and in the master database.

The Database Engine uses the file location information from the master database most of the time.

Logical and Physical File Names

They are:

1.logical_file_name2.Physical file name (os_file_name)

Logical_file_name:

The logical_file_name is the name used to refer to the physical file in all Transact-SQL statements.

Physical file name (os_file_name)

The os_file_name is the name of the physical file including the directory path.

SQL Server data and log files can be put on either FAT or NTFS file systems. NTFS is recommended for the security aspects of NTFS.

When multiple instances of SQL Server are run on a single computer, each instance receives a different default directory to hold the files for the databases created in the instance.

EX:

How SQL Server writes data to disk

The transaction log is a serial record of all modifications that have occurred in the database. SQL Server writes changes to the log before it writes changes to the actual data file.

SQL Server Arch Diagram

SQL Server maintains a buffer cache into which it reads data pages when data must be retrieved. Data modifications are not made directly to disk, but are made to the copy of the page in the buffer cache. The modification is not written to disk until a checkpoint occurs in the database, or the modification must be written to disk so the buffer can be used to hold a new page. Writing a modified data page from the buffer cache to disk is called flushing the page. A page modified in the cache, but not yet written to disk, is called a dirty page.

 At the time a modification is made to a page in the buffer, a log record is built in the log cache that records the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. If the dirty page is flushed before the log record is written, the dirty page creates a modification on the disk that cannot be rolled back if the server fails before the log record is written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written. Log records are written to disk when the transactions are committed.

When users change data, SQL Server doesn't write that change directly to the data. Rather, SQL Server locates the appropriate data and then loads it into a special area of RAM called the data cache. Changes are made in RAM. Then, SQL Server copies changes waiting in RAM to the transaction log. Only then does SQL Server write changes to the actual data file.

This is called a write-ahead log because SQL Server writes changes to the log before it writes changes to the actual data file.

SQL Server Architecture:

1.Physical database architecture:

2.Relational Database Engine Architecture

1.Physical database architecture Contains:

•Page & Extents

•Physical Database Files & File Groups

•Space Allocation & Reuse

•Table & Index Architecture

•Transaction Log Architecture

Physical Database Architecture
Page & Extents

•Page fundamental unit of storage.

•Page size is 8 KB (128 Pages per MB).

•96 byes for Page Header,8060 for Actual Data & Offset &  rest 36 for SLOT ARRAY.

•For VARCHAR, NVARCHAR, VARBINARY or SQL_VARIANT data types when total row size of all fixed & variable columns in a table exceeds 8060 bytes (Insert or Update) , SQL server dynamically move one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit. 

•When a column is moved to ROW_OVERFLOW_DATA allocation unit, a 24 byte pointer on the Original page in the IN_ROW_DATA allocation unit is maintained. If a subsequent operations reduce the row size, SQL Server dynamically move the column back to original data page.

Types of Pages:

EXtents:

•Extents, basic unit in which space is managed.

•Extent is of 8 Physical pages (8 KB page x 8 pages=64 KB). Total 16 Extents per MB.

•To efficiently utilize space, SQL Server does not allocate whole extent to a table. SQL has 2 type of extents:

•Uniform Extents, owned by single object, all 8 pages in the extent can only be used by owning object.

•Mixed Extents, are shared by up to 8 objects. Each of 8 pages in the extent can be owned by different objects.

An new table or index is generally allocated pages from Mixed Extents. When the table or index grows to the point that it has 8 pages, it then switches to use Uniform Extents for subsequent allocations.If we create an index on existing table that has enough rows to generate 8 pages in the index,all allocations to the index are in Uniform Extents.