We all work with databases and while working
with databases, we all have most probably SQL Server one version or another.
Today, in this post we will see how to retrieve the size of data files of all
databases present in SQL Server and the actual amount used in these files. As
you know, when you create a database, there are 2 files created on hard disk
with extensions as below -
- .mdf - This is the data file where all
data is stored.
- .ldf - This is the log file used by SQL
Server.
SQL Server provides some DBCC statement that
will help us write this query. We will use “DBCC SHOWFILESTATS” statement to
build this query. To get an idea on what this DBCC statement returns, try
running the below query in SQL Server Management Studio and you shall see the
effect -
- USE master
- DBCC SHOWFILESTATS
This will give you an output something similar
to the below.
![SQL server.jpg]()
The output returns the following columns for
our use -
- TotalExtents - Identifies the
total file size on Hard disk
- UsedExtents - Identifies the
actual file size in use on Hard disk
- Name - Identifies the name of the
database
- FileName - Identifies the location
of the physical file on Hard disk
From MSDN, Extents are the basic unit in which
space is managed. An extent is eight physically contiguous pages, or 64 KB. This
means that each extent equals to 64 Kilobytes of memory. Applying the
calculation on the output, we get the Total Extent of the master database is 64
x 64=4096 KB and the Used Extent is 46 x 64 = 2944KB
This gives us an idea on which database is
using how much size on the hard disk.
Note - To be able to run this query, you
should have administrator privileges on SQL Server.