Finding out Total Disk Space in TSQL

  • Jeff Moden

    SSC Guru

    Points: 994293

    Steve Jones - SSC Editor (1/15/2015)


    JimAtWork (2/3/2014)


    I have the same challenge and I have to query thousands of servers many of which do not allow the use of OA_ and also do not allow the use of Powershell for security reasons. Any work-arounds for that? TIA

    Not to hijack, but Powershell runs all over the place in MS products, so it can't be stopped. Is this just for user scripts?

    OR... reduce the number of servers. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • RGP

    SSCarpal Tunnel

    Points: 4290

    for sql 2008 onwards, free space on drive + percentage

    SELECT distinct(volume_mount_point),

    total_bytes/1048576 as Size_in_MB,

    available_bytes/1048576 as Free_in_MB,

    (select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as FreePercentage

    FROM sys.master_files AS f CROSS APPLY

    sys.dm_os_volume_stats(f.database_id, f.file_id)

    group by volume_mount_point, total_bytes/1048576,

    available_bytes/1048576 order by 1

  • tung858

    SSC Veteran

    Points: 212

    the easiest method is to use...

    "select...from sys.master_files mf cross apply sys.dm_os_volume_stats (mf.database_id, mf_file_id) vs"

    then CREATE a read-only DUMMY DATABASE that has data files across all drives. Even the local C drive if you want disk stats on all.

  • Michael L John

    One Orange Chip

    Points: 25717

    tung858 - Friday, February 15, 2019 11:47 AM

    the easiest method is to use...

    "select...from sys.master_files mf cross apply sys.dm_os_volume_stats (mf.database_id, mf_file_id) vs"

    then CREATE a read-only DUMMY DATABASE that has data files across all drives. Even the local C drive if you want disk stats on all.

    Cool.  How will this work across multiple servers?  Is the size column in bytes?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

Viewing 4 posts - 31 through 34 (of 34 total)

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