The maximum size for SQL Server 2008 databases is 524,272 TB.
Each database can contain up to 32,767 file groups, and the maximum size of each file is 16TB.
The maximum size of Log files is 2 TB. The minimum size for a log file is 512 KB.
A maximum of 32,767 databases can be created on a server.
SQL Server data and log files can be placed on either FAT or NTFS file systems, but cannot be placed on compressed file systems.
Database Files
A database file is made up of a number of 8 KB pages, and a Disk I/O operation will read or write a whole 8 KB page at a time. Pages are managed in extents, which consist of eight contiguous pages. At the beginning of each page is a 96-byte header header record that contains metadata about the page, including the page number, and the amount of free space in the page.
A database can consist of three types of file:
Primary file
The primary data file is the starting point of the database and points to any other files in the database. You use the master database to find the locations of all the files in a database, but they are also recorded in the primary file for each database so that the master database can be rebuilt using data from the primary user database files. If a database is small, then all the user data will be held in the primary file.
The recommended file name extension for primary data files is .mdf.
Secondary files
If the primary file is not large enough to hold all the data in the database, then secondary files are required. They are basically used to hold the data that does not fit in the primary data file. Some databases will not need secondary data files, while others may be large enough to need several secondary data files. You may also want to define secondary files on separate disk drives to spread the IO workload across multiple disks.
The recommended file name extension for secondary data files is .ndf.
Transaction logs
These files hold records of updates to the data files, and can be used to recover the database after an error. Every database has at least one transaction log file, and some databases may have more than one.
The recommended file name extension for log files is .ldf.
The SQL Server initially writes its updates to a buffer, both database updates and log updates. These are not written out to disk until the application issues a checkpoint. The term 'dirty page' refers to a page in the buffer that has been updated, but not yet written out to disk. It is essential that SQL Server writes the log file updates out to disk before the data updates, as otherwise uncommitted transactions could not be rolled back. This is called a Write-ahead log or WAL.
The .mdf, .ndf, and .ldf file naming standards are not enforced, but if you use them, then anyone can identify the file type from the extension. These are the names of the physical files on disk, called the os_file_names. You should not confuse these with the logical_file_names, the name used by applications.
File sizes and growth
When you define an SQL Server file, you can specify that it should grow automatically if it fills up its initial space allocation. You say how much space you want added to the file, and each time the file fills, it increases its size by that space amount. If you have multiple files in a filegroup, they will not autogrow until all the files are full. Growth then occurs using a round-robin algorithm.
You can also specify a maximum size for each file, otherwise the file can continue to grow until the disk is full. If you use autogrow, then you do not need to spend so much administration time monitoring the state of the database files and increasing them manually when they start to fill up.
Database Filegroups
It is possible to group database files together in filegroups to simplify management. It may be possible to improve performance by storing tables and indexes on separate disks, to spread the IO workload and prevent head contention. If you define a separate filegroup for every disk you can simplify the management of the files.
Log files are never members of a filegroup. Log space is managed separately from data space.
The primary filegroup is the default. It contains the primary data file and any other files that are not specifically assigned to another filegroup. System tables are allocated in the primary filegroup.
You can place specific files into User-defined filegroups by using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.
You can specify a default user_defined_filegroup for each database, so pages which are not given a specific filegroup are assigned to it, instead of the primary file group. If you do not specify a default filegroup, then the primary filegroup is the default.
It is possible to make all the files in a user_file_group read only, and it is also possible to detach a file group from one server, then reattach it to another server. This means that it is possible to copy an SQL database to CD, then distribute it to remote clients, who would be able to read the data using their own SQL server.
You do not need to use file groups at all, especially if you are running a smaller system. In this case all files are included in the primary filegroup.
Clustered Tables, Heaps, and Indexes
Data pages in Server 2005 tables can be organised in two different ways, as indexed tables called 'clustered tables', or an non-indexed tables called 'heaps'. Clustered tables are always stored in the index order, whereas Heaps are not ordered.
