By: Ken Simmons | Updated: 2020-01-08 | Comments (6) | Related: > Monitoring
Problem
As a DBA for several servers, I get a lot of calls about disk space. The first
thing I do is look for data files I can shrink to free up some space on the disk.
Some of the servers I support contain several databases and it can take a while
to figure out which data file will give me the most immediate relief.
Solution
The first thing you need is the drive information. You can get this from the extended stored procedure xp_fixeddrives, which returns the drive letter and how much space is free on the drive. The next thing you need to know is how much space is available in each data file. When you pull up the information in SSMS, Microsoft uses two different DBCC calls; one for the data files and one for the log files. Instead of using separate DBCC calls, you can call the fileproperty function and pass the name of the data file and the SpaceUsed parameters. This has to be run from the context of the database in which the data files are located, so you can use the sp_msforeachdb stored procedure to return the results for all databases.
Now that you have the drive information and the space used, you can join it to the sys.databases and sys.master_files tables to return one view of all the data. It is now possible to quickly determine the files that have the most free space and where they are located.
USE master
GO
CREATE TABLE #TMPFIXEDDRIVES ( DRIVE CHAR(1), MBFREE INT)
INSERT INTO #TMPFIXEDDRIVES
EXEC xp_FIXEDDRIVES
CREATE TABLE #TMPSPACEUSED ( DBNAME VARCHAR(50), FILENME VARCHAR(50), SPACEUSED FLOAT)
INSERT INTO #TMPSPACEUSED
EXEC( 'sp_msforeachdb''use [?]; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')
SELECT C.DRIVE,
CASE
WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
END AS DISKSPACEFREE,
A.NAME AS DATABASENAME,
B.NAME AS FILENAME,
CASE B.TYPE
WHEN 0 THEN 'DATA'
ELSE TYPE_DESC
END AS FILETYPE,
CASE
WHEN (B.SIZE * 8 / 1024.0) > 1000
THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
END AS FILESIZE,
CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE,
B.PHYSICAL_NAME
FROM SYS.DATABASES A
JOIN SYS.MASTER_FILES B ON A.DATABASE_ID = B.DATABASE_ID
JOIN #TMPFIXEDDRIVES C ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE
JOIN #TMPSPACEUSED D ON A.NAME = D.DBNAME AND B.NAME = D.FILENME
ORDER BY DISKSPACEFREE,
SPACEFREE DESC
DROP TABLE #TMPFIXEDDRIVES
DROP TABLE #TMPSPACEUSED
After copying and pasting the above into a SQL Server query window and running all of the code the result set will look something like below. Here we see the FileSize and the SpaceFree (returned in MB) for each file. In the first row below, the FileSize is 81.94MB and 70.56MB of this file is unused spaced.
Next Steps
- With a little modification, you can schedule the script to shrink data files containing over X amount of free space on a drive with less than X amount of disk space.
- In a perfect world, DBA's would just be able to ask for more drive space and not have to worry about shrinking files. It is more efficient to give the database the room it needs instead of having to shrink them all the time. Until then, I guess I will keep using this script.
About the author
Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2020-01-08
