All geek questions in one place

Determine available disk size using SQL query

I need to determine the available disk space where my database lives. I know about the xp_fixeddrives procedure, but how do I get information for a particular drive, where is my database?

+4
source share
3 answers

Something like that?

 declare @DatabaseName sysname set @DatabaseName = 'master' declare @Drive table(DriveName char, FreeSpaceInMegabytes int) insert @Drive execute xp_fixeddrives select mas.type_desc FileType, mas.name FileName, mas.physical_name PhysicalFileName, mas.size * 8 / 1024 FileSizeInMegabytes, drv.DriveName, drv.FreeSpaceInMegabytes from sys.master_files mas left join @Drive drv on left(mas.physical_name, 1) = drv.DriveName where database_id = db_id(@DatabaseName)

Set @DatabaseName accordingly.

+6
source

You did not specify your version of SQL Server. Starting with SQL Server 2005, you can get a lot of information from sys.database_files and associate this with xp_fixeddrives output.

+1
source 
  SELECT Drive , TotalSpaceGB , FreeSpaceGB , PctFree , PctFreeExact FROM (SELECT DISTINCT SUBSTRING(dovs.volume_mount_point, 1, 10) AS Drive , CONVERT(INT, dovs.total_bytes / 1024.0 / 1024.0 / 1024.0) AS TotalSpaceGB , CONVERT(INT, dovs.available_bytes / 1048576.0) / 1024 AS FreeSpaceGB , CAST(ROUND(( CONVERT(FLOAT, dovs.available_bytes / 1048576.0) / CONVERT(FLOAT, dovs.total_bytes / 1024.0 / 1024.0) * 100 ), 2) AS NVARCHAR(50)) + '%' AS PctFree , CONVERT(FLOAT, dovs.available_bytes / 1048576.0) / CONVERT(FLOAT, dovs.total_bytes / 1024.0 / 1024.0) * 100 AS PctFreeExact FROM sys.master_files AS mf CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS dovs) AS DE
0
source

Source: https://habr.com/ru/post/1300963/

More articles:


All Articles