USE master;
GO
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'Page')
DROP DATABASE Page;
GO
CREATE DATABASE Page
ON PRIMARY
( NAME='Page_Data',
FILENAME = 'D:\Work\POC\DatabaseEngine\Data\Page_Data.mdf',
SIZE=3MB,
MAXSIZE=10MB,
FILEGROWTH=1MB)
LOG ON
( NAME='Page_Log',
FILENAME = 'D:\Work\POC\DatabaseEngine\Data\Page_Log.ldf',
SIZE=1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB);
GO
Before you execute the above script, comment the section to create the data file (in green) and see what happens when you execute the script. I get the following error
Msg 188, Level 15, State 1, Line 8
Cannot specify a log file in a CREATE DATABASE statement without also specifying at least one data file.
Lets uncomment the data file section and comment the log file section (in brown) now. When I execute it doesn't give me an error. But this doesn't mean that the database doesn't need a log file. When I check the location where the data file has been created I also have a log file there which was created implicitly.
Couple of more points to note here
- If you comment both the sections in the above script, a database would still be created as both data file and log file will be created implicitly in the default location.
- Primary filegroup is mandatory for creation of the database. With the primary filegroup you can have secondary filegroups as well.
- The first file in the primary filegroup should have the size greater than or equal to 3MB in order to accommodate the model database.
- The minimum size limitation for the secondary files in primary filegroup or files in secondary filegroups or transaction log file is 512KB.
From here on I will continue to talk more about the Data files in couple of my next blogs before I move to Log files.
A database file in SQL Server is fundamentally divided into a set of logical units called Page. All Disk I/O operations are performed at the Page level. Each Page is 8KB in size and is part of a bigger logical unit called Extent. Each Extent holds 8 contiguous Pages without overlap. Thus an extent holds 64KB space.
Now that we have created the database Page, let us see what information does the initial set of Pages hold in the Data file for this database. Before I show you the content of the these Pages let me put down some text about them
- Page 0 : (File Header Page) => It holds information like type of the page, size of file, filegroup to which it belongs etc.
- Page 1 : (Page Free Space) => As the name suggests this Page holds 1 byte for each Page in order to hold information like Allocated/ UnAllocated, %age Allocation, Extent Type.
- Page 2 : (Global Allocation Map) => Each bit in this page is allocated to one Extent, to indicate if the Extent is allocated or un-allocated.
- Page 3 : (Shared Global Allocation Map) => Each bit in this page is allocated to one Extent to indicate that either the Extent is a mixed and has atleast one Page free OR the Extent is a uniform extent OR the Extent is mixed but is full.
- Page 6 : (Differential Changed Map) => Each bit in this page is allocated to one Extent, to indicate that Extent has changed or not since the BACKUP DATABASE statement.
- Page 7 : (Bulk Changed Map) => Each bit in this Page is allocated to one Extent, to indicate if that Extent was modified by bulk logged operation after the last BACKUP LOG statement.
In my next blog I will take each of these Pages individually and explore what information they store using some of the powerful DBCC commands.
