Click here to Skip to main content
13,348,514 members (79,770 online)
Click here to Skip to main content
Add your own
alternative version


4 bookmarked
Posted 17 Dec 2012

How to Retrieve the File Size Used by a SQL Server Database on Hard Disk

, 17 Dec 2012
Rate this:
Please Sign up or sign in to vote.
How to retrieve the size of data files of all databases present in SQL Server and the actual amount used in these files.

We all work with databases and while working with databases, we all have most probably one version or the another of SQL Server. 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;

This will give you an output something similar to the below:

How to retrieve the file size used by all databases in SQL Server

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 TotalExtent of the master database is 64 x 64=4096 KB and the UsedExtent is 46 x 64 = 2944KB.

This gives us an idea of 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.

Hope you enjoyed reading this post! Cheers!


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Nitesh Luharuka
Team Leader Director @ Rebin Infotech
India India
An Independent IT Professional and Consultant on MS Technologies like .Net | MVC | Silverlight | Sharepoint | MS Project Server along with open source CMS Systems like DotNetNuke etc.

I am an enthusiastic technocrat who love to debug problems and solve them. I love writing articles on my website in my spare time. Please visit my Website for more details and subscribe to get technology related tips/tricks.

You may also be interested in...


Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.180111.1 | Last Updated 17 Dec 2012
Article Copyright 2012 by Nitesh Luharuka
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid