Get file sizes of database files and free space on disk
This script can be used from a
Registered Server Group to obtain space information about all servers. Results are returned in one result-set allowing easy copy/pasting into Excel where pivot tables can be created to summarise the data.
Data returned:
[CurrentHost]
Machine that's hosting the database instance
[ClusterNodes]
If the database is clustered returns all the nodes in the cluster
[DB]
The database name
[FileType]
Either LOG or ROW indicates if it's a log file or data file.
[Name]
Name of the database file
[VolumeOrDrive]
Either the drive( if SQL Server version 2008 or lower) or the mounted volume if SQL 2008 R2 or higher.
[FileName]
Full file path
[File Size (MB)]
Size of the database file in MB
[Space Used In File (MB)]
Amount of space used within the database file in MB
[Available Space In File (MB)]
Amount of free space within the database file in MB
[Drive Free Space (MB)]
Amount of free space available on the drive or volume in MB.
-- =============================================
-- Author:Jonathan Roberts
-- Create date: 2015-07-23
-- Description:Script to display the database data and log file sizes
-- and available space on the volume.
-- Can be run on a registered server group to get data for all database servers
-- =============================================
GO
DECLARE @ServerVersion varchar(100)
SET @ServerVersion = CONVERT(varchar,SERVERPROPERTY('productversion'))
SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.',@ServerVersion, 4)-1)
--PRINT @ServerVersion
DECLARE @command nvarchar(2000)
IF OBJECT_ID('tempdb..#FileData','U') IS NOT NULL
BEGIN
PRINT 'Dropping #FileData'
DROP TABLE tempdb..#FileData
END
CREATE TABLE tempdb..#FileData
(
[CurrentHost] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[ClusterNodes] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[DB] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[FileType] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[Name] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[VolumeOrDrive] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[FileName] varchar(250) COLLATE Latin1_General_CI_AS NULL,
[File Size (MB)] decimal(15,2) NULL,
[Space Used In File (MB)] decimal(15,2) NULL,
[Available Space In File (MB)] decimal(15,2) NULL,
[Drive Free Space (MB)] decimal(15,2) NULL
)
IF CONVERT(float, @ServerVersion) < 10.5 BEGIN --–2000, 2005, 2008
IF OBJECT_ID('tempdb..#xp_fixeddrives','U') IS NOT NULL
BEGIN
PRINT 'Dropping table #xp_fixeddrives'
DROP TABLE #xp_fixeddrives;
END
CREATE TABLE #xp_fixeddrives
(
Drive varchar(250),
MBFree int
)
INSERT INTO #xp_fixeddrives
(
Drive,
MBFree
)
EXEC master..xp_fixeddrives
SET @command = '
USE [?]
INSERT INTO #FileData
(
[CurrentHost],
[ClusterNodes],
[DB],
[FileType],
[Name],
[VolumeOrDrive],
[FileName],
[File Size (MB)],
[Space Used In File (MB)],
[Available Space In File (MB)],
[Drive Free Space (MB)]
)
SELECT CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS AS [CurrentHost],
CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, '''' ), '''')) COLLATE Latin1_General_CI_AS AS [CluserNodes],
CONVERT(varchar(250), DB_NAME()) COLLATE Latin1_General_CI_AS [DB],
CONVERT(varchar(250), df.type_desc) COLLATE Latin1_General_CI_AS [FileType],
CONVERT(varchar(250), f.Name) COLLATE Latin1_General_CI_AS [Name],
CONVERT(varchar(250), LEFT(f.FileName, 3)) COLLATE Latin1_General_CI_AS [VolumeOrDrive],
CONVERT(varchar(250), f.FileName) COLLATE Latin1_General_CI_AS [FileName],
CONVERT(Decimal(15,2), ROUND(f.Size/128.000, 2)) [File Size (MB)],
CONVERT(Decimal(15,2), ROUND(FILEPROPERTY(f.Name,''SpaceUsed'')/128.000,2)) [Space Used In File (MB)],
CONVERT(Decimal(15,2), ROUND((f.Size-FILEPROPERTY(f.Name,''SpaceUsed''))/128.000,2)) [Available Space In File (MB)],
CONVERT(Decimal(15,2), d.MBFree) [Drive Free Space (MB)]
FROM dbo.sysfiles f WITH (NOLOCK)
INNER JOIN sys.database_files df ON df.file_id = f.fileid
LEFT JOIN tempdb..#xp_fixeddrives d
ON LEFT(f.FileName, 1) COLLATE Latin1_General_CI_AS = d.Drive COLLATE Latin1_General_CI_AS;'
END
ELSE -- SQL 2008R2+ (function sys.dm_os_volume_stats is available)
BEGIN
SET @command = 'USE [?]
INSERT INTO #FileData
(
[CurrentHost],
[ClusterNodes],
[DB],
[FileType],
[Name],
[VolumeOrDrive],
[FileName],
[File Size (MB)],
[Space Used In File (MB)],
[Available Space In File (MB)],
[Drive Free Space (MB)]
)
SELECT CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS AS [CurrentHost],
CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, '''' ), '''')) COLLATE Latin1_General_CI_AS AS [CluserNodes],
CONVERT(varchar(250), DB_NAME(v.database_id)) COLLATE Latin1_General_CI_AS [DB],
CONVERT(varchar(250), df.type_desc) COLLATE Latin1_General_CI_AS [FileType],
CONVERT(varchar(250), f.name) COLLATE Latin1_General_CI_AS [Name],
CONVERT(varchar(250), v.volume_mount_point) COLLATE Latin1_General_CI_AS [VolumeOrDrive],
CONVERT(varchar(250), f.[Filename]) COLLATE Latin1_General_CI_AS [Filename],
CONVERT(Decimal(15,2), ROUND(f.Size/128.000,2)) [File Size (MB)],
CONVERT(Decimal(15,2), ROUND(FILEPROPERTY(f.Name,''SpaceUsed'')/128.000,2)) [Space Used In File (MB)],
CONVERT(Decimal(15,2), ROUND((f.Size-FILEPROPERTY(f.Name,''SpaceUsed''))/128.000,2)) [Available Space In File (MB)],
CONVERT(Decimal(15,2), v.available_bytes/1048576.0) [Drive Free Space (MB)]
FROM sys.sysfiles f WITH (NOLOCK)
INNER JOIN sys.database_files df ON df.file_id = f.fileid
CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.fileid) v;'
END -- END IF
EXEC sp_MSforeachdb @command
SELECT *
FROM #FileData
DROP TABLE tempdb..#FileData
GO 
