Determining Free Space Per SQL Server Database

By: Jeremy Kadlec | Comments (5) | Related: 1 | 2 | 3 | 4 | 5 | More > Database Administration

Problem

On all of my databases, I cap the growth of the data and log files. I have not done my due diligence and performed capacity planning (Capacity Planning for SQL Server 2000 Database Storage), so I know I need to take those steps. I have been recently caught with a full database and I had to rush to expand it to keep the application up and running. Independent of the capacity planning process, I want to be able to monitor the database size and free space. Do you have a script that I can run to perform the checks on my SQL Server 2005 databases?

Solution

For SQL Server 2005 the key system table is master.sys.master_files. Let's just jump right into a script to see if this will do the trick:

Determining Free Space Per SQL Server 2005 Database

SELECT DB_NAME(database_id) AS DatabaseName,
CAST([Name] AS varchar(20)) AS NameofFile,
CAST(physical_name AS varchar(100)) AS PhysicalFile,
type_desc AS FileType,
((size * 8)/1024) AS FileSize,
MaxFileSize = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'
WHEN max_size = 0 THEN 'NO_GROWTH'
WHEN max_size <> -1 OR max_size <> 0 THEN CAST(((max_size * 8) / 1024) AS varchar(15))
ELSE 'Unknown'
END,
SpaceRemainingMB = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'
WHEN max_size <> -1 OR max_size = 268435456 THEN CAST((((max_size - size) * 8) / 1024) AS varchar(10))
ELSE 'Unknown'
END,
Growth = CASE WHEN growth = 0 THEN 'FIXED_SIZE'
WHEN growth > 0 THEN ((growth * 8)/1024)
ELSE 'Unknown'
END,
GrowthType = CASE WHEN is_percent_growth = 1 THEN 'PERCENTAGE'
WHEN is_percent_growth = 0 THEN 'MBs'
ELSE 'Unknown'
END
FROM master.sys.master_files
WHERE state = 0
AND type_desc IN ('LOG', 'ROWS')
ORDER BY database_id, file_id

In this script the master.sys.master_files view is queried and the following data is returned:

DatabaseName - Name of the database by using the DB_NAME() function on the database_id column.
NameofFile - First 20 characters of the name column.
PhysicalFile - First 100 characters of the physical_name column.
FileType - The values 'LOG' or 'ROWS' from the type_desc column. The full text indexes are ignored in the query.
FileSize - Multiply the size column which is the number of 8K pages by 8 and then divide by 1024 to convert the number to megabytes.
MaxFileSize
- When the max_size column equals '-1' or '268435456' the growth is considered unlimited. The '-1' value is for database files and the '268435456' value is for the transaction logs. The '268435456' value is 2 terabytes which is the largest value for a transaction log.
- When the max_size column equals '0' the file will not grow.
- When neither of these conditions apply, then multiply the max_size column which is the number of 8K pages by 8 and then divide by 1024 to convert the number to megabytes.
SpaceRemainingMB
- When the max_size column equals '-1' or '268435456' the growth is considered unlimited. The '-1' value is for database files and the '268435456' value is for the transaction logs. The '268435456' value is 2 terabytes which is the largest value for a transaction log.
- When the above condition does not apply, then multiply the max_size column which is the number of 8K pages by 8 and then divide by 1024 to convert the number to megabytes.
Growth
- When the growth value equals '0' then the growth is a fixed size.
- When the above condition does not apply, then multiply the growth column which is the number of 8K pages by 8 and then divide by 1024 to convert the number to megabytes.
GrowthType
- When the is_percent_growth column is equal to 1 then the file growth is a specific percentage.
- When the is_percent_growth column is equal to 0 then the file growth is a specific number of megabytes.

Next Steps

Running out of free space in your databases is not a good feeling. So take a look at this script and consider it as an option to monitor your databases.
Performing capacity planning is still a necessary process. Whether you use a mathematical formula to complete the process or your local knowledge, it is an exercise that needs to be executed each quarter.
If you have another piece of technology that performs SQL Server monitoring across the enterprise or you have created you own home grown processes, consider this code or another option to be sure you do not run out of free space in the future.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Sunday, October 18, 2015 - 4:48:32 PM - Sam David

Hello,

I enjoyed reading your article it is very well written. However, I need your help with e following.

I am an accidental SQL Server DBA and looking for a script(s) that I can run to help me perform the "SQL Server Health Check". HW and SW configuration check for daily, monthly or quarterly maintenance.

1. Memory, CPU Storage

2. Database Size, Tables, Indexes, etc.

Thank You. Have a nice day.

Tuesday, October 21, 2008 - 6:51:32 AM - jmearle

I figured this out. The problem was that you have to cast the int derived in the when clause to a varchar

Here is the complete working script

=================================================================
SELECT DB_NAME(database_id) AS DatabaseName,
    CAST([Name] AS varchar(20)) AS NameofFile,
    CAST(physical_name AS varchar(100)) AS PhysicalFile,
    type_desc AS FileType,
    ((size * 8)/1024) AS FileSize,
MaxFileSize = CASE
    WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'
    WHEN max_size = 0 THEN 'NO_GROWTH'
    WHEN max_size <> -1 OR max_size <> 0 THEN CAST(((max_size * 8) / 1024) AS varchar(15))
    ELSE 'Unknown'
END,
SpaceRemainingMB = CASE
    WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'
    WHEN max_size <> -1 OR max_size = 268435456 THEN CAST((((max_size - size) * 8) / 1024) AS varchar(10))
    ELSE 'Unknown'
END,
Growth = CASE
    WHEN growth = 0 THEN 'FIXED_SIZE'
    WHEN growth > 0 THEN CAST(((growth * 8)/1024) As varchar(10))
    ELSE 'Unknown'
END,
GrowthType = CASE
    WHEN is_percent_growth = 1 THEN 'PERCENTAGE'
    WHEN is_percent_growth = 0 THEN 'MBs'
    ELSE 'Unknown'
END
FROM master.sys.master_files
    WHERE state = 0
    AND type_desc IN ('LOG', 'ROWS')
ORDER BY database_id, file_id
=================================================================

Tuesday, October 21, 2008 - 6:36:47 AM - jmearle

When executing this script under SQL Server 2005 64 bit I get the following error.

It doesn't make sense because the Fixed_Size when clause is just like the when clauses above it.

Any ideas how to fix this?

==================================================
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'FIXED_SIZE' to data type int.
===================================================================

Saturday, November 3, 2007 - 10:16:12 AM - admin

Vaibhav,

Thank you for your feedback on the tip. You are correct DBCC SQLPERF('LogSpace') does provide the space utilization for the transaction logs at a point in time. The code from this tip is intended to serve more as a capacity planning script to determine the disk space allocated and disk space for potential growth for data and transaction log files, as opposed to the actual amount of the transaction log that is currently in use.

Thank you,
The MSSQLTips.com Team

Thursday, November 1, 2007 - 2:02:52 AM - vaibhav

U can check dis through DBCC SQLPERF(LOGSPACE) to see log space used by each databases