SQL server database size, location and volume info

For one of my clients, I needed to know and document not only database names, size and locations but also disk volume information as well.

Now there is an easier way to get the same info using a DMV.

https://sqlpal.blogspot.com/2018/04/get-sql-server-database-size-location.html


Here is another way to retrieve same information using OLE functions.

-- SQL SERVER VERSION 2005 & UP

/*
Caveat: This relies on OLE functions, if that option is not enalbed (which is default) it will try to enable it
*/

SET NOCOUNT ON

-- if OLE automation is not enabled, execute the following code to enable it
-- Enabling Ole Automation Procedures
exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
exec sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO


SET NOCOUNT ON

DECLARE @SQL VARCHAR(8000)
DECLARE @hr int, @fso int, @drive char(1), @odrive int, @TotalSize varchar(20), @FreeSize varchar(20), @VolumeName varchar(4000), @MB Numeric
SET @MB = 1048576


IF OBJECT_ID('TEMPDB..#t1_DBSpaceInfo') IS NOT NULL
   DROP TABLE #t1_DBSpaceInfo


IF OBJECT_ID('TEMPDB..#drives') IS NOT NULL
   DROP TABLE #drives
CREATE TABLE #drives (drive char(1) PRIMARY KEY, VolumeName varchar(4000), FreeSpace int NULL, TotalSize int NULL)

INSERT #drives(drive,FreeSpace) EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD FOR SELECT drive from #drives ORDER by drive
OPEN dcur FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN

EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
EXEC @hr = sp_OAGetProperty @odrive,'VolumeName', @VolumeName OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive

UPDATE #drives SET TotalSize=@TotalSize/@MB, VolumeName = @VolumeName WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive

End
Close dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

SELECT
ID=IDENTITY(int,1,1) ,
SERVERPROPERTY('MachineName') SQLServer,
SERVERPROPERTY('InstanceName') InstanceName,

DB_NAME(database_id) as DBName,
name as LogicalFileName,
CASE (type_desc) When 'LOG' Then 'Log' When 'ROWS' Then 'Data' ELSE type_desc END AS FileType,
VolumeName,
LEFT(UPPER(physical_name),1) AS Drive,
databasepropertyex(DB_NAME(database_id), 'IsFulltextEnabled') IsFulltextEnabled,
databasepropertyex(DB_NAME(database_id), 'IsPublished') IsPublished,
cast(CAST((size*8.0/1024.00)AS numeric(10,2)) as INT) AS [SizeinMB],
TotalSize / 1024 ActualVolSizeGB,
FreeSpace / 1024 ActualVolFreeGB,

CAST(FILEPROPERTY(name,'spaceused')/128.00 as numeric(10,2)) as [UsedSpaceMB],
Growth,
data_space_id,
physical_name as filename

INTO #t1_DBSpaceInfo
FROM sys.master_files mf
INNER JOIN #drives dr on LEFT(UPPER(mf.physical_name),1) = UPPER(dr.drive)
ORDER BY LEFT(UPPER(mf.physical_name),1), DB_NAME(database_id)

set @SQL = '
SELECT
SQLServer,
InstanceName,
DBName,
LogicalFileName LogicalName,
filename PhysicalFileName,
FileType,
VolumeName,
Drive,
IsFulltextEnabled,
IsPublished,
[SizeinMB],
case when ID = (SELECT MAX(ID) FROM #t1_DBSpaceInfo WHERE DRIVE = t1.DRIVE) then
  CAST((SELECT SUM(SizeInMB) from #t1_DBSpaceInfo where DRIVE = t1.DRIVE) AS VARCHAR(50)) else '''' END VolumeSize,


case when ID = (SELECT MAX(ID) FROM #t1_DBSpaceInfo WHERE DRIVE = t1.DRIVE) then CAST(ActualVolSizeGB as varchar(50)) else '''' END ActualVolSizeGB ,
case when ID = (SELECT MAX(ID) FROM #t1_DBSpaceInfo WHERE DRIVE = t1.DRIVE) then CAST(ActualVolFreeGB as varchar(50)) else '''' END ActualVolFreeGB

 FROM #t1_DBSpaceInfo t1
ORDER BY ID'

PRINT @SQL
EXEC(@SQL)

IF OBJECT_ID('TEMPDB..#drives') IS NOT NULL
   DROP TABLE #drives

IF OBJECT_ID('TEMPDB..#t1_DBSpaceInfo') IS NOT NULL
   DROP TABLE #t1_DBSpaceInfo