I would like some advice on how to use a query to get the total size of each disk drive.

Question

When I try to get the total size of each disk drive using sp_OAGetProperty in mssql, I cannot get a value greater than 17,592,186,040,320 for large drives. I would like to ask for advice on a solution that can be checked using a query.

And I don't want to use xp_cmdshell.

Accepted Answer

Don't use the OA procedures in new code. They are all deprecated.

Generally you can use xp_fixeddrives to grt drive info but if you are on 2017 or later, you can query sys.dm_os_enumerate_fixed_drives

Answer

Hi @준영 이 ,Based on the message you provided, here are some steps you can follow to check the size of disk drive if your server version is SQL Server 2008 R2 SP1 and later:

The following example returns the total space and available space (in bytes) for all database files in the instance of SQL Server.
```
   
   
```
The following example returns the total space and available space (in bytes) for the database files in the current database.
```
   
   
```

The following script returns the total space, used space and free space in Gigabyte for the used disk drive.

   WITH T1 AS (
   SELECT DISTINCT
   REPLACE(vs.volume_mount_point,':\','') AS Drive_Name,
   CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS Total_Space_GB ,
   CAST(vs.available_bytes / 1024.0 / 1024 / 1024  AS NUMERIC(18,2)) AS Free_Space_GB
   FROM    sys.master_files AS f
   CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
   )
   SELECT
   Drive_Name,
   Total_Space_GB,
   Total_Space_GB-Free_Space_GB AS Used_Space_GB,
   Free_Space_GB,
   CAST(Free_Space_GB*100/Total_Space_GB AS NUMERIC(18,2)) AS Free_Space_Percent
   FROM T1

You can use ‘EXEC master.dbo.xp_fixeddrives’ to get the free size of each disk drive.
Hope these articles can help you understand the functions well. sys.dm_os_volume_stats (Transact-SQL) - SQL Server | Microsoft Learn , sys.master_files (Transact-SQL) - SQL Server | Microsoft Learn, sys.database_files (Transact-SQL) - SQL Server | Microsoft Learn

Best regards,

Lucy Chen

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Answer

Hi @준영 이 ,

Based on the message you provided, here are some steps you can follow to check the size of disk drive if your server version is SQL Server 2008 R2 SP1 and later:

The following example returns the total space and available space (in bytes) for all database files in the instance of SQL Server.

   SELECT f.database_id, f.file_id, volume_mount_point, total_bytes, available_bytes  
   FROM sys.master_files AS f  
   CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);

The following example returns the total space and available space (in bytes) for the database files in the current database.

   SELECT database_id, f.file_id, volume_mount_point, total_bytes, available_bytes  
   FROM sys.database_files AS f  
   CROSS APPLY sys.dm_os_volume_stats(DB_ID(f.name), f.file_id);