Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Determining Free Space Per SQL Server Database


By:   |   Read Comments (5)   |   Related Tips: 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.


Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

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.

SD


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

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 Back To Top

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 03, 2007 - 10:16:12 AM - admin Back To Top

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 01, 2007 - 2:02:52 AM - vaibhav Back To Top

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

 


Learn more about SQL Server tools