Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How can I find the Total free memory in the windows machine where a sqlserver instances are installed? Expand / Collapse
Author
Message
Posted Thursday, July 11, 2013 6:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 16, 2014 9:50 AM
Points: 7, Visits: 65
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.
Post #1472546
Posted Thursday, July 11, 2013 11:02 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:45 PM
Points: 670, Visits: 233
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



-RP
Post #1472710
Posted Friday, July 12, 2013 1:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 16, 2014 9:50 AM
Points: 7, Visits: 65
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
Post #1472892
Posted Friday, July 12, 2013 2:26 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 807, Visits: 725
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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1472902
Posted Friday, July 12, 2013 12:00 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:45 PM
Points: 670, Visits: 233
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..


-RP
Post #1473190
Posted Friday, July 12, 2013 5:44 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:46 AM
Points: 282, Visits: 889

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)

from http://connect.microsoft.com/SQLServer/feedback/details/642004/denali-xp-fixeddrives-does-not-display-mounted-volume-information-on-a-cluster
Post #1473266
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse