Monday, February 3, 2020

A quick look at all database files -- location, size and freespace

This is a very fast one.  Just something I ran today to quickly review all database file details on the server.  Well, not necessarily all details, but the ones that I needed --- location, size and freespace for each.  Fast and easy --> 

IF OBJECT_ID('tempdb..##tempfiles') IS NOT NULL
DROP TABLE ##tempfiles;

    DatabaseName sysname,
    FileName sysname,
    PhysicalFileName nvarchar(500),
    FileSize decimal (18,2),
    FreeSpace decimal (18,2)
EXEC sp_msforeachdb '
USE [?];
INSERT ##temp (DatabaseName, FileName, PhysicalFileName, FileSize, FreeSpace)
SELECT DB_NAME(), Name, physical_name, CAST(CAST(ROUND(CAST(size AS DECIMAL) * 8.0/1024.0,2) AS DECIMAL(18,2)) AS NVARCHAR) Size,
 CAST(CAST(ROUND(CAST(size AS DECIMAL) * 8.0/1024.0,2) AS DECIMAL(18,2)) - CAST(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 AS DECIMAL(18,2)) AS NVARCHAR) FreeSpace
FROM sys.database_files
       DatabaseName, FileName, PhysicalFileName, FileSize, FreeSpace
FROM ##temp

DROP TABLE ##temp;

Sample output:

You either MUST ignore the C drive location of those, files, or please just remember that it is a bad idea to locate any of your databases on the C drive -- system or otherwise.

They are just there for a fast sample on this code.  Check it out and let me know what you think.

Happy SQL.

No comments:

Post a Comment