Understanding and managing disk space is crucial for the smooth operation of any SQL Server environment, especially when it comes to capacity planning. This blog post presents a simple T-SQL script that retrieves the free space for all drives in your SQL Server instance in megabytes (MB), gigabytes (GB), and terabytes (TB). By keeping track of your disk space usage, you can anticipate storage needs, prevent potential performance issues, and ensure there’s sufficient room for your growing databases. Implementing this T-SQL script in your regular server checks offers an efficient way to monitor storage and maintain the health of your server environment, while also enabling proactive management of storage capacity planning efforts. Interested in enhancing your server maintenance routine? Dive into the script and see how it can benefit your operation.
-- Create a temporary table to store drive information
CREATE TABLE #Temp_FixedDrives
(
DriveLetter CHAR(1), -- Column for drive letter of the disk
FreeSpaceMB INT -- Column for free space in megabytes
)
-- Insert free disk space information into the temporary table
INSERT INTO #Temp_FixedDrives
EXEC xp_fixeddrives;
-- Select drive information, including free space in MB, and calculate to get the value for GB and TB
SELECT
DriveLetter,
FreeSpaceMB,
CAST(FreeSpaceMB / 1024.0 AS DECIMAL(10, 2)) AS FreeSpaceGB, -- Convert MB to GB
CAST(FreeSpaceMB / 1048576.0 AS DECIMAL(10, 2)) AS FreeSpaceTB -- Convert MB to TB
FROM
#Temp_FixedDrives
-- Clean up by removing the temporary table
DROP TABLE #Temp_FixedDrives
GO
- Article from sqlauthority