Get file sizes of database files and free space on disk
-
Comments posted to this topic are about the item Get file sizes of database files and free space on disk
-
Well done. One word of warning. sys.xp_fixeddrives cannot read mounted volumes and accurately report the size. If you are running SQL 2008 R2 SP1 or above, take a look at sys.dm_os_volume_stats as a replacement.
SELECT DISTINCT
volume_mount_point
, CAST( ( total_bytes / 1073741824. ) as numeric(18,4)) AS [Total_GB]
, CAST( ( available_bytes / 1073741824. ) as numeric(18,4)) AS [Available_GB]
, CAST(
( CAST( ( available_bytes / 1073741824. ) as numeric(18,4)) /
CAST( ( total_bytes / 1073741824. ) as numeric(18,4))
) * 100.0000
as numeric(18,4)) AS [PercentFree]
FROM sys.master_files AS f CROSS APPLY
sys.dm_os_volume_stats(f.database_id, f.FILE_ID)
ORDER BY volume_mount_point;
vs.
EXECUTE sys.xp_fixeddrives; -
Hi Christopher
The script only calls xp_fixeddrives if the version of SQL server is below 2008, When the version is 2008 or higher it uses dm_os_volume_stats and reports on the mounted volume.
-
Jonathan AC Roberts (7/24/2015)
Hi ChristopherThe script only calls xp_fixeddrives if the version of SQL server is below 2008, When the version is 2008 or higher it uses dm_os_volume_stats and reports on the mounted volume.
Xp_fixeddrives will not provide information for mounted volumes you need to account for this to make the size reports accurate in a pre sql 2008 environment.
Mounted volumes were fully supported in 2000 and 2005 so your script may report space incorrectly
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Perry Whittle (8/14/2015)
Jonathan AC Roberts (7/24/2015)
Hi ChristopherThe script only calls xp_fixeddrives if the version of SQL server is below 2008, When the version is 2008 or higher it uses dm_os_volume_stats and reports on the mounted volume.
Xp_fixeddrives will not provide information for mounted volumes you need to account for this to make the size reports accurate in a pre sql 2008 environment.
Mounted volumes were fully supported in 2000 and 2005 so your script may report space incorrectly
Yes, dm_os_volume_stats is only available in SQL Server 2008 and higher. I'm not sure how I can get the volume space information from versions less than 2008 so I used the Xp_fixeddrives to get the space on the drive. Fortunately in our environment we only use mounted volumes on SQL Server 2008 and higher all our SQL 2005 database just use drives.
Have you any ideas on how to get volume information from pre SQL 2008 databases?
-
Jonathan,
Great script. Thanks for sharing. One small problem though is this line:
IF CONVERT(int, @ServerVersion) < 10
The issue is that SQL Server 2008 does NOT support sys.dm_os_volume_stats which was not introduced until 2008 R2.
If you execute the script on a SQL Server 2008 (Non R2) you will get this error:
Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.dm_os_volume_stats'.Msg 208, Level 16, State 1, Line 2
Changing this line:SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.',@ServerVersion)-1)
To this: SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.',@ServerVersion,4)-1)
AND changing this line: IF CONVERT(int, @ServerVersion) < 10
to this: IF @ServerVersion < 10.5 BEGIN --–2000, 2005
SEEMS to work (I haven't completely finished testing).
Thanks again.
Lee
-
Lee Linares (8/17/2015)
Jonathan,Great script. Thanks for sharing. One small problem though is this line:
IF CONVERT(int, @ServerVersion) < 10
The issue is that SQL Server 2008 does NOT support sys.dm_os_volume_stats which was not introduced until 2008 R2.
If you execute the script on a SQL Server 2008 (Non R2) you will get this error:
Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.dm_os_volume_stats'.Msg 208, Level 16, State 1, Line 2
Changing this line:SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.',@ServerVersion)-1)
To this: SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.',@ServerVersion,4)-1)
AND changing this line: IF CONVERT(int, @ServerVersion) < 10
to this: IF @ServerVersion < 10.5 BEGIN --–2000, 2005
SEEMS to work (I haven't completely finished testing).
Thanks again.
Lee
Hi Lee,
Thanks for that! I've made the changes you suggested to the script and submitted the updated version for republication.
-
Thanks for the helpful script.
-
Thanks for the script.
-
An excellent script and one I use a lot however having just inherited a system with a FILESTREAM database I'm wondering the best way to get this FILESTREAM type included in the results
-
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
I have found this query which seems almost perfect for requirement can someone help me with adding two more columns one for db file autogrowth and other for total disk size..
Thank you...
-
Which version of sql server are you using as there are now a set of DMV’s that expose this info, a lot easier!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Hello Perry,
I want to fetch all these below mentioned columns, and we have versions from sql server 2008 to sql server 2016.
Database Name
Type
File Name
File Group Name
File_Location
FileSize_MB
UsedSpace_MB
FreeSpace_MB
FreeSpace %
Auto Growth
volume_mount_point
Total Volume Size_ GB
Free Space_GB
% Free
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply