May 13, 2015 at 5:59 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 15, 2015 at 1:28 am
try using exec master.dbo.xp_fixeddrives,
when you get all disk on given server, you can use sp_OAGetProperty for get TotalSize and FreeSpace.
@tSize = sp_OAGetProperty @disc,'TotalSize', @resTS output
@fSize = sp_OAGetProperty @disc,'FreeSpace', @resFS output
set @resTS = (CONVERT(BIGint,@resTS) / 1048576 )
set @resFS = (CONVERT(BIGint,@resFS ) / 1048576 )
% = cast( ((cast(@resFS as decimal(18,2)) / cast(@resTS as decimal(18,2))) * 100) as decimal(18,2))
May 16, 2015 at 4:50 pm
Sgar... (5/13/2015)
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 hereI 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%'
This query has an incorrect syntax, guess you left some bits out.
😎
Can you elaborate on what you are referring to as "free %"? Is it disk space, allocated file space, table space or up in space?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply