May 13, 2015 at 6:47 am
I am using this query. It gives me Total bytes and Free bytes in MB. I want to include free % in same query, But I am not able to do so. It is giving me either 0 or different values. Can anyone help here
I am trying the commented part for free %.
SELECT @@SERVERNAME as 'Servername',
(((SUM(total_bytes))/1024)/1024)/1024 as 'Total_space(In MB)' ,
(((SUM(available_bytes))/1024)/1024)/1024 as 'Available_space (in MB)',
/* ( ( (SUM(total_bytes)) / (SUM(available_bytes)) ) *100) as 'Free SPace in %' */
FROM master.sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
where f.file_id=1 and f.database_id > 4 and Volume_mount_point like '%S%'
Sagar Sonawane
** Every DBA has his day!!:cool:
May 13, 2015 at 7:47 am
I'm guessing it's due to the underlying columns being of INT datatype
SELECTServerName = @@SERVERNAME,
TotalSpaceMB = SUM(CA1.total_bytes),
AvailableSpaceMB = SUM(CA1.available_bytes),
--Integer Maths will return 0
SUM(CA1.available_bytes) / SUM(CA1.total_bytes),
--Decimal
SUM(0E + CA1.available_bytes) / SUM(0E + CA1.total_bytes),
SUM(0E + CA1.available_bytes) / SUM(0E + CA1.total_bytes) * 100
FROMmaster.sys.master_files AS MF
CROSSAPPLYmaster.sys.dm_os_volume_stats(MF.database_id, MF.file_id) AS CA1;
May 13, 2015 at 7:52 am
See the difference
DECLARE
@total INT = 5
,@free INT = 4
SELECT @free/@total*100 AS INTEGER_ARITHM
, (0.+@free)/@total*100 AS NUMERIC_ARITHM
You also may convert it explicitly using exactly precsion you need.
May 13, 2015 at 8:55 am
Thank you Very Much. It worked.
Sagar Sonawane
** Every DBA has his day!!:cool:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply