How to Find the total extents and pages used by the database in SQL Server
Use dbname
go
DBCC SHOWFILESTATS
Go
Above command will give you the extents used by the database.
As per the output total extents occupied by the database room is : 48
Used extents is : 22
As we know that each extent consists of 8 KB 8 Pages.
Therefore Used PAGES of database "Room" are : 22 * 8 = 176
Total PAGES of database "Room" are : 48 * 8 = 384
From extents we have found the number of pages a database is occupying.
Lets calculate the size of the database Total size/Used size from above details:
As each page size is 8 kb.
There for total pages occupied by the database "Room" is : 384 * 8 kb = 3072 kb
And used pages occupied by the database "Room" is : 176 * 8 kb= 1408 kb
Lets execute the above and the see the real time results, below
In the above execution results, we can see that MDF file size is 3072 kb which is exactly same as the size we found above from total pages occupied by the Room database.
So, on disk (MDF) file which is of size 3072 KB , we have 1408 KB free space in it.
Still more (48 -22) 26 pages are yet to get filled inside the MDF file.
If you restrict the size of MDF, when those 26 pages get filled you will receive error "unable to extent space".
Hope the above information helps
Thanks
