Fragments are the fundamental unit in which databases are allocated. Each database can have a maximum of 32 fragments. The CREATE DATABASE statement will generate 1 or 2 fragments (depending on the syntax used), and each use of the ALTER DATABASE statement after that will generate an additional fragment. Each fragment is contained in only one device; fragments do not span devices. Each database can have multiple fragments on one device and can have fragments on multiple devices.
For example, assume that a system has two user devices, dev1 with 20 MB and dev2 with 10 MB. The following statement creates a sample database with 2 fragments, one of 10 MB on dev1 holding data and one of 10 MB on dev2 holding the log. There is still 10 MB free on dev1.
CREATE DATABASE sample ON dev1 = 10 LOG ON dev2 = 10
If the sample database then starts running out of data space, use ALTER DATABASE to generate an additional fragment:
ALTER DATABASE sample ON dev1 = 10
After ALTER DATABASE has completed, sample has 3 fragments; dev1 contains the original data fragment of 10 MB plus the last data fragment of 10 MB and dev2 contains the log fragment generated by CREATE DATABASE. Each database fragment has a line in the master.dbo.sysusages table.
When you create a database fragment on a device that does not have enough space to hold the entire fragment, SQL Server maps as many allocation units onto the device as will fit in the space available. For example, to allocate a device dev1 with 4 MB, you specify SIZE = 2148 in the DISK INIT statement. If the DISK INIT statement is specified as SIZE = 2140, then there are only 3.98 MB on dev1.
When you execute the following statement, SQL Server allocates as many allocation units to the device as it can, so the short database ends up with only 3.5 MB:
CREATE DATABASE short ON dev1 = 4
