An alternative to sp_spaceused

The built-in procedure sp_spaceused can provide some useful information, especially when an object name is specified; great for showing the finer details of that object.

For example the MSreplication_options table:

exec sp_spaceused 'MSreplication_options'

The information for the number of rows and especially index size can be useful when copying the data or determining space requirements when rebuilding indexes, respectively.

And when an object is not specified, information about the entire database is shown, providing some good information. For example:

exec sp_spaceused

Personally, I like to see more file information and get more granular details about a database and its size compared to the traditional, sp_spaceused.

Yes, Microsoft has made some improvements to sp_spaceused in recent versions of SQL Server, however, I like to see the white space and a little more detail about each of the database files belonging to a database, not just the overall available space.

Knowing where my allocated space resides will help me plan for growth. So without further adieu here is the script.

SET NOCOUNT ON;
SELECT DB_NAME() AS DBName,
 [name] AS [FileName],
 file_id AS ID,
 state_desc AS FileStatus,
 type_desc AS FileType,
 size / 128.0 AS FileSizeMB,
 FILEPROPERTY(name, 'SpaceUsed') / 128.0 AS SpaceUsedMB,
 size/128.0 - CAST(FILEPROPERTY([name], 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB,
 physical_name,
 CASE is_read_only
  WHEN 0 THEN 'FALSE'
  WHEN 1 THEN 'TRUE'
 END as is_read_only 
FROM sys.database_files;

And here is some sample output of what this script will show you:

Now, if you wanted to add more detail to what is already shown, you could look at the contents of sp_spaceused by doing the following…

exec sp_helptext 'sp_spaceused'

…to get some further tidbits from sys.partitions, sys.allocation_units, and sys.internal_tables, but I will leave that as an exercise for the reader. 🙂

Enjoy the script and hopefully it assists you in your day-to-day DBA functions as it does myself.

Edited January 12, 2016

I’ve modified the script to include 2 additional columns, one for used space and one for free space of each file as a percentage, which, at-a-glance allows one to see any problem areas quicker. The new script is as follows:

SET NOCOUNT ON; 
SELECT DB_NAME() AS DBName,
 [name] AS [FileName],
 file_id AS ID,
 state_desc AS FileStatus,
 type_desc AS FileType,
 size / 128.0 AS FileSizeMB,
 FILEPROPERTY(name, 'SpaceUsed') / 128.0 AS SpaceUsedMB,
 size/128.0 - CAST(FILEPROPERTY([name], 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB,
 cast((FILEPROPERTY(name, 'SpaceUsed')/128.0) / (size/128.0)*100 as decimal(18,1)) as UsedSpacePercent,
 cast((size/128.0 - CAST(FILEPROPERTY([name], 'SpaceUsed') as INT)/128.0) / (size/128.0)*100 as decimal(18,1)) as FreeSpacePercent,
 physical_name,
 CASE is_read_only
  WHEN 0 THEN 'FALSE'
  WHEN 1 THEN 'TRUE'
 END as is_read_only
FROM sys.database_files;

Edited January 13, 2016

I’ve further modified the script above to make it a little more readable giving the same results. This should be the final edit 🙂

SET NOCOUNT ON;
WITH fileSizes AS 
(
 SELECT file_id,
 CAST(size / 128.0 AS DECIMAL(18,2)) AS FileSizeMB,
 CAST(FILEPROPERTY(name, 'SpaceUsed') / 128.0 AS DECIMAL(18,2)) AS SpaceUsedMB,
 CAST((size / 128.0) - (FILEPROPERTY(name, 'SpaceUsed') / 128.0) AS DECIMAL(18,2)) AS FreeSpaceMB FROM sys.database_files
)
SELECT DB_NAME() AS DBName,
 [name] AS [FileName],
 fs.file_id AS ID,
 state_desc AS FileStatus,
 type_desc AS FileType,
 fs.FileSizeMB,
 fs.SpaceUsedMB,
 fs.FreeSpaceMB,
 CAST(fs.SpaceUsedMB / fs.FileSizeMB * 100.0 AS DECIMAL(18,1)) AS UsedSpacePercent,
 CAST(fs.FreeSpaceMB / fs.FileSizeMB * 100.0 AS DECIMAL(18,1)) AS FreeSpacePercent,
 physical_name,
 CASE is_read_only 
  WHEN 0 THEN 'FALSE'
  WHEN 1 THEN 'TRUE'
 END AS is_read_only 
FROM sys.database_files sdf 
JOIN fileSizes fs ON sdf.file_id = fs.file_id;