Finding out Total Disk Space in TSQL

  • Raymond Entena

    Old Hand

    Points: 358

    anyone know any workaround in TSQL who can give this result set?

    Drive letter | Total Disk Space (MB) | Free Disk Space (MB)

    -----------------------------------------------------------

    C 150000 100000

    D 160000 130000

    Q 100000 90000

    S 300000 200000

    T 300000 280000

    i can get the drive letters and free disk space in MB from "xp_fixeddrives" but i don't know how to get the total disk space in MB using TSQL...

    thanks in advance...

  • hb21l6

    SSCommitted

    Points: 1537

    Ive not tried it, but try this

    http://www.lazydba.com/sql/1__16047.html

    Dave

  • SanjayAttray

    SSChampion

    Points: 13157

    SET NOCOUNT ON

    DECLARE @hr int

    DECLARE @fso int

    DECLARE @drive char(1)

    DECLARE @odrive int

    DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576

    CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,

    TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC

    master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate

    'Scripting.FileSystemObject',@fso OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo

    @fso

    DECLARE dcur CURSOR LOCAL FAST_FORWARD

    FOR SELECT drive from #drives ORDER by drive

    OPEN dcur FETCH NEXT FROM dcur INTO @drive

    WHILE @@FETCH_STATUS=0

    BEGIN

    EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive

    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =

    sp_OAGetProperty

    @odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo

    @odrive UPDATE #drives SET TotalSize=@TotalSize/@MB WHERE

    drive=@drive FETCH NEXT FROM dcur INTO @drive

    End

    Close dcur

    DEALLOCATE dcur

    EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

    SELECT

    drive, TotalSize as 'Total(MB)', FreeSpace as 'Free(MB)' FROM #drives

    ORDER BY drive

    DROP TABLE #drives

    GO

    SQL DBA.

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    You can get this information with the PSINFO utility available on the link below.

    http://technet.microsoft.com/en-us/sysinternals/bb896649.aspx

    Sample command and output:

    psinfo \\MyServerName -D disk

    PsInfo v1.73 - Local and remote system information viewer

    Copyright (C) 2001-2005 Mark Russinovich

    Sysinternals - http://www.sysinternals.com

    System information for \\MyServerName:

    Volume Type Format Label Size Free Free

    A: Removable 0.0%

    C: Fixed NTFS MyServer_C 19.99 GB 14.92 GB 74.7%

    D: Fixed NTFS MyServer_D 12.00 GB 9.41 GB 78.5%

    Z: CD-ROM 0.0%

  • free_mascot

    One Orange Chip

    Points: 27168

    Hello Raymond,

    Here is the simple command which will give you desired result.

    xp_fixeddrives

    Hope this is what are you looking for.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Jeff Moden

    SSC Guru

    Points: 993661

    Heh... you didn't read the OP's request... he wants the total disk size as well. xp_FixedDrives does not provide that information. 😉

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

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    Jeff Moden (1/10/2008)


    Heh... you didn't read the OP's request... he wants the total disk size as well. xp_FixedDrives does not provide that information. 😉

    Not to mention that the OP said he used xp_fixeddrives, and it didn't give him all the info he needed.

  • Raymond Entena

    Old Hand

    Points: 358

    Thanks a bunch guys! Really appreciate this although I have created a workaround... I used FSUTIL command line and did some tweaking to get the result I need and I also compared it with the result from xp_fixedrives..

    Below is the code.. I have one problem though... When I tried converting

    the Bytes to Mbytes at the last SELECT statement, I got this error.

    "Server: Msg 8114, Level 16, State 5, Line 42

    [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to bigint."

    Help please! :):):)

    ------------------------------------------------------------------

    SET NOCOUNT ON

    DECLARE @varSQL varchar(1000), @varDrive varchar(10)

    CREATE TABLE #tmpDriveSpaceInfo

    (drive varchar(10),

    xpFixedDrive_FreeSpace_MB bigint,

    FSutil_FreeSpace_Bytes integer,

    FSutil_Space_Bytes integer,

    FSutil_AvailSpace_Bytes integer

    )

    CREATE TABLE #tmpFSutilDriveSpaceInfo

    (drive varchar(10),

    info varchar(50)

    )

    INSERT INTO #tmpDriveSpaceInfo (drive, xpFixedDrive_FreeSpace_MB)

    EXEC master..xp_fixeddrives

    DECLARE CUR_DriveLooper CURSOR FOR SELECT drive FROM #tmpDriveSpaceInfo

    OPEN CUR_DriveLooper

    FETCH NEXT FROM CUR_DriveLooper INTO @varDrive

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @varSQL = 'EXEC master..XP_CMDSHELL ' + ''''+ 'fsutil volume diskfree ' + @varDrive + ':' + ''''

    INSERT INTO #tmpFSutilDriveSpaceInfo (info)

    EXEC(@varSQL)

    UPDATE #tmpFSutilDriveSpaceInfo SET drive = @varDrive WHERE drive IS NULL

    FETCH NEXT FROM CUR_DriveLooper INTO @varDrive

    END

    DELETE FROM #tmpFSutilDriveSpaceInfo WHERE info IS NULL

    SELECT drive,

    ltrim(rtrim(left(info,29))) as InfoType,

    ltrim(rtrim(substring (info, charindex (':',info) + 2, 20))) as Size_Bytes

    INTO #tmpFSutilDriveSpaceInfo_Fixed

    FROM #tmpFSutilDriveSpaceInfo

    SELECT a.drive,

    a.xpFixedDrive_FreeSpace_MB,

    (SELECT cast(Size_Bytes as bigint) FROM #tmpFSutilDriveSpaceInfo_Fixed WHERE drive = a.drive and InfoType = 'Total # of free bytes')/1048576 AS FSutil_FreeSpace_MB,

    (SELECT cast(Size_Bytes as bigint) FROM #tmpFSutilDriveSpaceInfo_Fixed WHERE drive = a.drive and InfoType = 'Total # of bytes')/1048576 AS FSutil_TotalSpace_MB,

    (SELECT cast(Size_Bytes as bigint) FROM #tmpFSutilDriveSpaceInfo_Fixed WHERE drive = a.drive and InfoType = 'Total # of avail free bytes')/1048576 AS FSutil_AvailableSpace_MB

    FROM #tmpDriveSpaceInfo a

    CLOSE CUR_DriveLooper

    DEALLOCATE CUR_DriveLooper

    DROP TABLE #tmpFSutilDriveSpaceInfo

    DROP TABLE #tmpDriveSpaceInfo

    DROP TABLE #tmpFSutilDriveSpaceInfo_Fixed

    ------------------------------------------------------------------

  • osama hussein

    SSC Journeyman

    Points: 82

    for SQL 2005 or 08 you need to enable the OLE Automation sps

    here is the full query with enable and then disable the OLEs

    /*******************************************************/

    /* Enabling Ole Automation Procedures */

    sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    sp_configure 'Ole Automation Procedures', 1;

    GO

    RECONFIGURE;

    GO

    /*******************************************************/

    SET NOCOUNT ON

    DECLARE @hr int

    DECLARE @fso int

    DECLARE @drive char(1)

    DECLARE @odrive int

    DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576

    CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,

    TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC

    master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate

    'Scripting.FileSystemObject',@fso OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo

    @fso

    DECLARE dcur CURSOR LOCAL FAST_FORWARD

    FOR SELECT drive from #drives ORDER by drive

    OPEN dcur FETCH NEXT FROM dcur INTO @drive

    WHILE @@FETCH_STATUS=0

    BEGIN

    EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive

    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =

    sp_OAGetProperty

    @odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo

    @odrive UPDATE #drives SET TotalSize=@TotalSize/@MB WHERE

    drive=@drive FETCH NEXT FROM dcur INTO @drive

    End

    Close dcur

    DEALLOCATE dcur

    EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

    SELECT @@Servername

    SELECT

    drive, TotalSize as 'Total(MB)', FreeSpace as 'Free(MB)' FROM #drives

    ORDER BY drive

    DROP TABLE #drives

    GO

    /*******************************************************/

    /* Disabling Ole Automation Procedures */

    sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    sp_configure 'Ole Automation Procedures', 0;

    GO

    RECONFIGURE;

    /*******************************************************/

    GO

  • Mike - CI

    SSCarpal Tunnel

    Points: 4146

    Just as another option if you work at a shop that does not allow Ole Automation to be turned on (some consider it a security risk), you can do the same thing with Powershell without having automation turned on (for that matter you don't need to have SQL on the server at all....if you ever need to keep track of this on non-SQL boxes).

    There are lots of really good articles posted by Chad Miller, and with them you can put together all kinds of great information using powershell.

    http://www.sqlservercentral.com/Authors/Articles/Chad_Miller/421/

  • BalasundaramRangasamy

    SSC Veteran

    Points: 208

    Nice Query Raymond. Thanks

  • BalasundaramRangasamy

    SSC Veteran

    Points: 208

    Nice Query Raymond. Thanks

  • steve.gouin

    SSC Rookie

    Points: 49

    Raymond,

    the problem is that there are invalid charaters in the string to convert. Just replace the following code :

    SELECT drive,

    ltrim(rtrim(left(info,29))) as InfoType,

    ltrim(rtrim(substring (info, charindex (':',info) + 2, 20))) as Size_Bytes

    INTO #tmpFSutilDriveSpaceInfo_Fixed

    FROM #tmpFSutilDriveSpaceInfo

    with this one :

    SELECT drive,

    ltrim(rtrim(left(info,29))) as InfoType,

    replace(replace(replace(substring (info, charindex (':',info) + 2, 20),' ',''),char(13),''),char(10),'') as Size_Bytes

    INTO #tmpFSutilDriveSpaceInfo_Fixed

    FROM #tmpFSutilDriveSpaceInfo

    and everything should works fine.

  • harpreets.singh

    SSC Journeyman

    Points: 75

    Hi Guys,

    I tired to use powershell to get drive space info.

    Check this blog http://myharpreet.blogspot.com/2012/01/t-sql-to-get-drive-space-info.html

    Harpreet

  • Jeff Moden

    SSC Guru

    Points: 993661

    harpreets.singh (2/15/2012)


    I tired to use powershell to get drive space info.

    Did it work? 🙂

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

Viewing 15 posts - 1 through 15 (of 34 total)

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