Finding out Total Disk Space in TSQL

  • Jeff Moden

    SSC Guru

    Points: 997150

    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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • RGP

    SSCarpal Tunnel

    Points: 4381

    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: 234

    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.

    Tung Dang
    Senior SQL Server DBA
    Calpine Energy Solutions
    Brain Box SQL Mobile DBA

  • Michael L John

    One Orange Chip

    Points: 25947

    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/

  • tung858

    SSC Veteran

    Points: 234

    Try this for SQL Server 2008 R2 and above

    The sizes will convert to mb, gb, or tb depending on how large the drive is.

    The query only works with one server.  However if you want to see all drives on a server, just create a DBA or dummy database with data or log files in each drive.

    select distinct [driveletter] = a2.volume_mount_point
    , [drivesize] = case when a2.total_bytes/1024/1024/1024/1024 >= 1 then convert( varchar, convert(decimal(8,1),a2.total_bytes/1024.0/1024/1024/1024)) + ' TB'
    when a2.total_bytes/1024/1024/1024 >= 1 then convert( varchar, convert(decimal(8,1),a2.total_bytes/1024.0/1024/1024)) + ' GB'
    else cast(a2.total_bytes/1048576 as varchar) + ' MB' end
    , [spaceused%] = convert(decimal(4,1),(a2.total_bytes - a2.available_bytes) * 100.0 / a2.total_bytes)
    from sys.master_files a1
    cross apply sys.dm_os_volume_stats (a1.database_id, a1.file_id) a2
    order by a2.volume_mount_point

    • This reply was modified 3 weeks, 6 days ago by  tung858.
    • This reply was modified 3 weeks, 6 days ago by  tung858.
    • This reply was modified 3 weeks, 6 days ago by  tung858.
    • This reply was modified 3 weeks, 6 days ago by  tung858.

    Tung Dang
    Senior SQL Server DBA
    Calpine Energy Solutions
    Brain Box SQL Mobile DBA

Viewing 5 posts - 31 through 35 (of 35 total)

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