Finding out Total Disk Space in TSQL

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 721419

    Nice script, but I'd argue this isn't using T-SQL. It's using a shell command, which isn't allowed in many places.

  • harpreets.singh

    SSC Journeyman

    Points: 75

    Hi Jeff,

    Try it and let me know if it works.

    I have tested this script on several servers.

    regards,

    Harpreet

  • Jeff Moden

    SSC Guru

    Points: 997316

    harpreets.singh (2/15/2012)


    Hi Jeff,

    Try it and let me know if it works.

    I have tested this script on several servers.

    regards,

    Harpreet

    BWAAA-HAAA!!!! Not the way it works. Since you're the one that recommended it, you first tell me that you have tested it and that it works. Then I give it a try and verify.

    The only reason why I'm asking this is because your original comment was that you tried to do something. You didn't say if the script you pointed to actually solved your problem and I don't actually have the time tor read every article that someone recommends on an offhanded basis. 😉

    --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)

  • SQLDuck

    Old Hand

    Points: 368

    Thanks for sharing this, Sanjay. This script solves a problem that's been bothering me for a while!

  • prasenjit221

    Grasshopper

    Points: 15

    Thank you Raymond..Your code helped me build mine.Hope this helps:

    SET NOCOUNT ON

    IF OBJECT_ID(N'tempdb..#drive_space') IS NOT NULL DROP TABLE #drive_space

    CREATE TABLE #drive_space

    (name varchar(5)

    , available_mb int)

    INSERT #drive_space(name,available_mb)

    EXEC master..xp_fixeddrives

    DECLARE

    @drive VARCHAR(5)

    , @cmd VARCHAR(1000)

    , @pos SMALLINT

    IF OBJECT_ID(N'tempdb..#cmd_space') IS NOT NULL DROP TABLE #cmd_space

    CREATE TABLE #cmd_space

    (total_b VARCHAR(1000)

    , drive VARCHAR(5))

    DECLARE drive_name CURSOR FOR

    SELECT name FROM #drive_space

    OPEN drive_name

    FETCH NEXT FROM drive_name INTO @drive

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @cmd='master..xp_cmdshell ' + ''''+ 'fsutil volume diskfree ' + @drive + ':| find '+ '"Total # of bytes"'+''''

    INSERT #cmd_space(total_b) EXEC (@cmd)

    UPDATE #cmd_space SET drive=@drive WHERE drive IS NULL

    FETCH NEXT FROM drive_name INTO @drive

    END

    CLOSE drive_name

    DEALLOCATE drive_name

    DELETE FROM #cmd_space WHERE total_b IS NULL

    SELECT @pos=charindex(':',total_b) FROM #cmd_space

    SELECT b.drive as DRIVE

    , CONVERT(BIGINT,(RIGHT(b.total_b,(LEN(b.total_b)-@pos))))/1073741824 AS TOTAL_DRIVE_SPACE_GB

    , a.available_mb/1024 AS AVAILABLE_SPACE_GB

    FROM #drive_space a WITH (NOLOCK)

    INNER JOIN #cmd_space b WITH (NOLOCK)

    ON a.name=b.drive

    DROP TABLE #cmd_space

    DROP TABLE #drive_space

  • JimAtWork

    SSC Enthusiast

    Points: 125

    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


    Kindest Regards,

    JimAtWork

  • mauriciorpp

    Default port

    Points: 1472

    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 the most elegant solution, and only works for 2008R2+. but it gets the job done.

    SELECT distinct(volume_mount_point), total_bytes/1048576 as Size_in_MB, available_bytes/1048576 as Free_in_MB

    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

    from: http://msdn.microsoft.com/en-us/library/hh223223.aspx and some Pinal Dave advices.

    read more at: http://thelonelydba.wordpress.com/2014/09/03/ms-sql-find-total-server-disk-space-in-t-sql

    hope this helps!

  • Jeff Moden

    SSC Guru

    Points: 997316

    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

    Sorry for the late reply but, Yes... DOS batch code with calls to WMI.

    --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)

  • psharma_233

    SSC Enthusiast

    Points: 162

    Can someone help to get the total space, free space and used space with the servername of the disk.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 721419

    Does the query posted above by mauriciorpp not help?

    What is the servername of the disk?

  • psharma_233

    SSC Enthusiast

    Points: 162

    Hi Steve,

    Its giving me the below error:

    Invalid object name 'sys.dm_os_volume_stats

  • Michael L John

    One Orange Chip

    Points: 25962

    Jeff Moden (9/3/2014)


    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

    Sorry for the late reply but, Yes... DOS batch code with calls to WMI.

    Attached is an old vb script that checks free space percentage. It uses Win32_PerfFormattedData_PerfDisk_LogicalDisk WMI class. You probably want to use the Win32_LogicalDisk class.

    This was used to send back free space to a centralized monitoring system. The comments should point you in the right direction.

    You will need to change the extension to .vbs

    Hope this helps.

    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/

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 721419

    Ah, sorry, didn't realize this was a SQL 2000 instance.

    You could use something like prasenjit221's code. I don't have a SQL 2000 instance handy to test, but that looks compatible. Depending on OS, I might use Powershell or VBscript instead and have those languages insert the data into a table that you can query. That's because I've found I don't usually need this in real time. I might have a job that updates this once a day or once an hour.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 721419

    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?

  • himanshu827 21014

    SSC Journeyman

    Points: 96

    use psinfo tools ...

    1) install it on your local box or laptop on C:\

    2) now in CMD line

    c:> psinfo \\serveryouneedinformation -d

    😉 simple ...

    https://technet.microsoft.com/en-us/sysinternals/bb897550.aspx

    Thanks .

Viewing 15 posts - 16 through 30 (of 35 total)

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