Disk space - SS2K8

  • Naive question from developer (not administrator!):

    Is there stored procedure(s) that return(s):

    1) overall disk space allocated to my server

    2) current used/available memory size?

    Thanks

  • valeryk2000 (3/5/2012)


    Naive question from developer (not administrator!):

    Is there stored procedure(s) that return(s):

    1) overall disk space allocated to my server

    2) current used/available memory size?

    Thanks

    1) Depends on what you want to monitor: database size or disk space?

    -- returns space used by database objects

    EXEC sp_spaceused

    -- returns free space for all drives

    EXEC master.sys.xp_fixeddrives

    2) This should do:

    -- used / available memory

    SELECT *

    FROM (

    SELECT counter_name, cntr_value

    FROM sys.dm_os_performance_counters

    WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)')

    ) AS s

    PIVOT (MAX(cntr_value) FOR counter_name IN ([Total Server Memory (KB)], [Target Server Memory (KB)])) AS p

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • Thank you.

    Both 'Total Server Memory (KB)' and 'Target Server Memory (KB)' gave the same result. Is it ok?

  • This means that your instance is using all the memory you reserved to it.

    Target server memory = Total amount of memory in KB that the server can consume.

    Total server memory = Amount of memory currently in use.

    Is it ok? Well, it depends, but you won't see it using these counters.

    You need to monitor some other counters that can help you identify memory pressure, such as "Page Life Expectancy".

    SELECT *

    FROM (

    SELECT cntr_value AS current_PLE

    FROM sys.dm_os_performance_counters

    WHERE object_name LIKE '%:Buffer Manager%'

    AND counter_name = 'Page life expectancy'

    ) A,

    (

    SELECT CAST(300 * cntr_value / (4.0 * 1024 * 1024) AS int) AS PLE_threshold

    FROM sys.dm_os_performance_counters

    WHERE counter_name = 'Total Server Memory (KB)'

    ) B

    As long as "current_PLE" remains above "PLE_threshold" you likely have no memory pressure.

    -- Gianluca Sartori

  • PLE is ok.

    Thanks a lot!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply