Post reply

Finding out Total Disk Space in TSQL

  • February 15, 2012 at 12:35 pm

    #1447499

    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.

  • February 15, 2012 at 1:05 pm

    #1447513

    Hi Jeff,

    Try it and let me know if it works.

    I have tested this script on several servers.

    regards,

    Harpreet

  • February 15, 2012 at 3:40 pm

    #1447595

    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.

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

  • July 9, 2012 at 10:10 am

    #1510426

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

  • October 14, 2012 at 8:49 pm

    #1548984

    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

  • February 3, 2014 at 9:04 am

    #1685539

    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

  • September 3, 2014 at 1:36 pm

    #1742989

    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!

  • September 3, 2014 at 3:47 pm

    #1743031

    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.

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

  • January 8, 2015 at 8:26 am

    #1768793

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

  • January 8, 2015 at 10:24 am

    #1768838

    Does the query posted above by mauriciorpp not help?

    What is the servername of the disk?

  • January 15, 2015 at 7:40 am

    #1770460

    Hi Steve,

    Its giving me the below error:

    Invalid object name 'sys.dm_os_volume_stats

  • January 15, 2015 at 8:11 am

    #1770475

    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/

  • January 15, 2015 at 8:11 am

    #1770476

    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.

  • January 15, 2015 at 8:37 am

    #1770488

    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?

  • May 20, 2015 at 10:22 am

    #1799375

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

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