July 11, 2013 at 6:33 am
Hello,
I need to find the free memory in the machine where a SQL server 2008 r2 is installed, and I need to do it via T SQL.
Are the table:
sys.dm_os_sys_info or
sys.dm_os_sys_memory
about this or not? I've found out different opinion in the internet and I've been told that with this query I can only find the RAM available:
select available_physical_memory_kb
from master.sys.dm_os_sys_memory
is this true?
how can I find the total space available in all unit C:/ with a query?
thanks a lot.
July 11, 2013 at 11:02 am
Probably, this is what you were looking for!!!
select
Name as 'Database'
, Filename as 'Location'
, convert(numeric(10,2),round(a.size/128.000,2)) as SizeMB
, convert(numeric(10,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as UsedSpaceMB
, convert(numeric(10,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
, convert(numeric(10,4),round(fileproperty(a.name,'SpaceUsed')/128.000,2)/1000) as UsedSpaceGB
, convert(numeric(10,4),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)/1000) as FreeSpaceGB
from dbo.sysfiles a
July 12, 2013 at 1:59 am
Using this function I have the attended result
xp_fixeddrives
with the query that you wrote I have different data, I will verify it.
thanks a lot
July 12, 2013 at 2:26 am
The thread is confusing. Are you looking for disk space or RAM?
xp_fixeddrives indeed gives you the available amount of free disk space (not the total space).
If you want to know the total amount of free RAM in the machine, I don't think there is a DMV that exposes this, nor is there any reason why there should be. There is little reason why SQL Server would track what else is going on in the machine. But if there is a command-line command to give you the information, you could invoke it through xp_cmdshell.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 12, 2013 at 12:00 pm
True..
XP_Fixeddrives will give actual free disk space on the server.. whereas script I gave above will give detail about size of the file and amount of space that is used by specified file... Sorry for the confusion..
July 12, 2013 at 5:44 pm
SELECT distinct volume_mount_point
,cast(available_bytes as decimal)/1073741824 as [Space_in_GB]
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply