SQL SERVER – Get a List of Fixed Hard Drive and Free Space on Server

When I am not blogging, I am typically working on SQL Server Optimization projects. Time and again, I only have access to SQL Server Management Studio that I can remotely connect to server but do not have access to Operating System, and it works just fine.

At one point in optimization project, I have to decide on index filegroup placement as well TempDB files (.ldf and .mdf) placement. It is commonly known that system gives enhanced performance when index and tempdb are on separate drives than where the main database is placed.

As I do not have access to OS I use the following Stored Procedure to figure out the number of fixed drives (hard drive) a system has along with free space on each of those drives. Based on this result, I decide my strategy of where to put my indexes and tempdb.

EXEC master..xp_fixeddrives

Reference : Pinal Dave (http://blog.sqlauthority.com)

37 thoughts on “SQL SERVER – Get a List of Fixed Hard Drive and Free Space on Server

  1. Hi Pinal,
    i’m in love all the articles you write.

    I’ve one question regarding View:
    its just a plain view:

    Select ID, ‘1’ as group, Name From T_Base;

    Output
    ———
    ID Group Co_Name
    1 1 ABC
    2 1 PQR
    1 1 ABC
    3 1 MNO
    1 1 ABC

    Now, i would like to update / increment the group value by 1 if the ID value gets repeated. I wanted to do everything in that particualr VIEW only.

    Expected Output:
    ———————–
    ID Group Co_Name
    1 1 ABC
    2 1 PQR
    1 2 ABC
    3 1 MNO
    1 3 ABC

    Any help would be appreciated.

    Thanks in Advance,
    Raj

    Like

  2. @Pinal Dave
    Thank You once again for this nice post. The post is really nice….

    @Atif.
    Is there any way we can check Hard Drive Free space in network drives if it’s there?
    Thanks in advance.

    Like

  3. and again, Pinal neverending story ;) with (not only) tips is going on ;)
    Thx, fot that! This one can be handy for some scripting..

    Like

  4. Raj,
    Is this what you are looking for?

    DECLARE @t TABLE (ID INT, Name CHAR(3))
    INSERT INTO @t (ID, Name) SELECT 1, ‘ABC’
    INSERT INTO @t (ID, Name) SELECT 2, ‘PQR’
    INSERT INTO @t (ID, Name) SELECT 1, ‘ABC’
    INSERT INTO @t (ID, Name) SELECT 3, ‘MNO’
    INSERT INTO @t (ID, Name) SELECT 1, ‘ABC’

    SELECT
    ID,
    ROW_NUMBER() OVER(
    PARTITION BY ID ORDER BY ID) AS [Group],
    Name
    FROM @t

    /*
    ID Group Name
    ———– ——————– —-
    1 1 ABC
    1 2 ABC
    1 3 ABC
    2 1 PQR
    3 1 MNO
    */

    Like

  5. Thank you very much Jacob for your reply. It resolved my problem.

    Now i have some different requirement. Could you please help me out with it as well /

    This is the query rewult which im getting as of now

    ======================
    Code Name Group
    ——————————–
    AX001 ABC 1
    AX002 XXX 1
    AX001 MNO 1
    AX001 ABC 1
    AX003 YYY 1
    AX001 PQR 1
    AX004 ZZZ 1
    AX003 YYY 1
    AX003 ZZZ 1
    AX002 ABC 1

    I need the result in the below format.. When Code & Name are same then it shud start with 1, if same code & diff Name then the Gropu value should increment by 1.

    Expected Output
    ======================
    Code Name Group
    ——————————–
    AX001 ABC 1
    AX001 ABC 1
    AX001 MNO 2
    AX001 PQR 3
    AX002 ABC 1
    AX002 XXX 2
    AX003 YYY 1
    AX003 YYY 1
    AX003 ZZZ 2
    AX004 ZZZ 1

    Can you please reply to me on my id. I sent you the Linkedin request as well.

    Thanks,
    Awaiting your feedback / reply ASAP (TIA)

    Like

  6. Raj,
    You can use the DENSE_RANK() function to achieve this. I am posting it here (even though you asked me to send by email) so that it can help other people as well.

    DECLARE @t TABLE (Code CHAR(5), Name CHAR(3))
    INSERT INTO @t (Code, Name) SELECT ‘AX001′,’ABC’
    INSERT INTO @t (Code, Name) SELECT ‘AX001′,’ABC’
    INSERT INTO @t (Code, Name) SELECT ‘AX001′,’MNO’
    INSERT INTO @t (Code, Name) SELECT ‘AX001′,’PQR’
    INSERT INTO @t (Code, Name) SELECT ‘AX002′,’ABC’
    INSERT INTO @t (Code, Name) SELECT ‘AX002′,’XXX’
    INSERT INTO @t (Code, Name) SELECT ‘AX003′,’YYY’
    INSERT INTO @t (Code, Name) SELECT ‘AX003′,’YYY’
    INSERT INTO @t (Code, Name) SELECT ‘AX003′,’ZZZ’
    INSERT INTO @t (Code, Name) SELECT ‘AX004′,’ZZZ’

    SELECT
    Code, Name,
    DENSE_RANK() OVER(PARTITION BY Code ORDER BY Name) AS[ Group]
    FROM @t

    /*
    Code Name Group
    —– —- ——————–
    AX001 ABC 1
    AX001 ABC 1
    AX001 MNO 2
    AX001 PQR 3
    AX002 ABC 1
    AX002 XXX 2
    AX003 YYY 1
    AX003 YYY 1
    AX003 ZZZ 2
    AX004 ZZZ 1
    */

    Like

  7. Dear Pinal & Other members,

    I want to find out full hard disk detail with space utilized (In MB) and percentage, space utilized in MB and % for a system.

    it’s really urgent so please co-operate.

    Thanks in Advance.

    Regards,
    Bipin Singh

    Like

      • Dear Madhivanan,

        It returns something like this,

        Volume in drive C has no label.
        Volume Serial Number is 1CB7-C6C4
        NULL
        Directory of C:\WINDOWS\system32
        NULL
        File Not Found
        NULL

        which is not my desired output. I discovered something like this which is satisfying my requirement.

        Script:

        Alter PROCEDURE Proc_diskspace
        AS
        SET NOCOUNT ON

        DECLARE @hr int
        DECLARE @fso int
        DECLARE @drive char(1)
        DECLARE @odrive int
        DECLARE @TotalSize varchar(20)
        DECLARE @MB bigint ; SET @MB = 1048576

        CREATE TABLE #drives (ServerName varchar(15),
        drive char(1) PRIMARY KEY,
        FreeSpace int NULL,
        TotalSize int NULL,
        FreespaceTimestamp DATETIME 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, ServerName = @@servername, FreespaceTimestamp = (GETDATE())
        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, drive, TotalSize as ‘Total(MB)’, FreeSpace as ‘Free(MB)’,
        CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as ‘Free(%)’,
        FreespaceTimestamp
        FROM #drives
        ORDER BY drive
        DROP TABLE #drives
        RETURN

        Exec Proc_diskspace
        ——————————————————————–

        It results like this,

        Servername C 38154 26468 69 2010-05-05 09:43:02.300

        Thanks,
        Bipin Singh

        Like

  8. Good to know the T-SQL way of doing it but this is where PowerShell comes into picture. It is quite easy!

    Get-WmiObject -Class Win32_LogicalDisk | Select -Property DeviceID, @{Name=’FreeSpaceMB';Expression={$_.FreeSpace/1MB} } | Format-Table -AutoSize

    This gives an output similar to:
    DeviceID FreeSpaceMB
    ——– ———–
    C: 258795.73046875
    D: 0
    F: 3000.8125
    H: 122916.5
    Q: 0

    Now, you can easily extend this to query a remote server by just adding the -ComputerName parameter. For example:

    Get-WmiObject -ComputerName Remoteserver -Class Win32_LogicalDisk | Select -Property DeviceID, @{Name=’FreeSpaceMB';Expression={$_.FreeSpace/1MB} } | Format-Table -AutoSize

    Simple!

    Like

  9. Pingback: SQL SERVER – Powershell – Get a List of Fixed Hard Drive and Free Space on Server « Journey to SQLAuthority

  10. Hi Pinal and All commentors,

    The values in sql output are different from the OS drives actual values.
    eg:D drive is showing 19313 MB in sql result.But OS drives shows 18.8 GB free..
    How can we rectify the difference?

    Like

  11. As a previous poster commented, mount points do not display correctly using the fixed drive stored procedure. As a work-around for my current situation, I found that I can create a sql agent job to run a CmdExec type of job which just does a “dir” of the drive and stores it to a file.

    Like

  12. Hi All,

    TRY THIS: but this will not give us the infomation about volumes.

    IF OBJECT_ID(‘#AvailableDiskSpace’) IS NULL
    BEGIN
    CREATE TABLE dbo.#AvailableDiskSpace
    (
    PK INT IDENTITY CONSTRAINT PK_AvailableDiskSpace PRIMARY KEY,
    DriveLetter CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    VolumeName VARCHAR(32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    PercentFree AS (CONVERT(DECIMAL(15, 4), AvailableMB) / CONVERT(DECIMAL(15, 4), TotalDiskSpaceMB) * 100),
    AvailableMB BIGINT NOT NULL,
    TotalDiskSpaceMB BIGINT NOT NULL,
    DTStamp DATETIME NOT NULL
    ) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX NCndx_DTStamp
    ON dbo.#AvailableDiskSpace (DTStamp)

    END
    — Vars
    DECLARE

    @AgeOutDate DATETIME, — To prune the [AvailableDiskSpace] table
    @BATCHDATE DATETIME, — Constant
    @DriveSize BIGINT, — Drive size in bytes
    @DriveLetter VARCHAR(2), — FSO drive letter
    @ErrorMessage VARCHAR(500), — Error message
    @FSO INT, — File System Object
    @AvailableMB INT, — Available space MB
    @NO BIT, — Constant
    @oDrive INT, — FSO drive
    @OLE_AUTOMATION_PROCEDURES INT, — Constant
    @PK TINYINT, — Work-table primary key
    @RawFree VARCHAR(20), — Available space bytes
    @RetVal INT, — Return value
    @SQL NVARCHAR(255), — Dynamic T-SQL
    @TwoToTheTwentieth INT, — Convert bytes to MB
    @ValueInUse SQL_VARIANT, — To test configuration settings
    @VolumeName VARCHAR(32), — FSO volume name
    @YES BIT — Constant

    — Constants
    SET @BATCHDATE = GETDATE()
    SET @OLE_AUTOMATION_PROCEDURES = 16388 — SELECT configuration_id FROM master.sys.configurations
    SET @NO = 0
    SET @TwoToTheTwentieth = POWER(2, 20)
    SET @YES = 1

    — Create temp tables for disk space info
    IF OBJECT_ID(‘tempdb.dbo.#Space’) IS NOT NULL DROP TABLE #Space
    CREATE TABLE #Space
    (
    PK TINYINT IDENTITY CONSTRAINT Space_PK PRIMARY KEY,
    DriveLetter VARCHAR(2) NOT NULL,
    VolumeName VARCHAR(32) NULL,
    DriveSize BIGINT CONSTRAINT DF_Space_DriveSize DEFAULT(0),
    AvailableMB BIGINT NOT NULL,
    Processed BIT CONSTRAINT DF_Space_Processed DEFAULT (0)
    )

    — We need OLE automation enabled to succeed.

    BEGIN TRY
    — Enable advanced options
    EXEC sp_configure ‘show advanced option’, ‘1’;
    RECONFIGURE;

    — Ensure OLE Automation enabled
    EXEC sp_configure ‘OLE Automation Procedures’, @YES;
    RECONFIGURE

    — Test…
    SELECT @ValueInUse = Value_In_Use
    FROM master.sys.configurations
    WHERE configuration_id = @OLE_AUTOMATION_PROCEDURES

    IF CONVERT(INT, @ValueInUse) @YES
    BEGIN
    SET @ErrorMessage = CHAR(13) + ‘Although we attempted to enable the ”OLE Automation Procedures” server option, this option is still disabled.’ + CHAR(13)
    + ‘This script depends upon this extended stored procedure”s availability. Please investigate.’
    + CHAR(13) + ‘NOTE: ”OLE Automation Procedures” execution requires ”CONTROL SERVER” permission (See SQL Server Books Online for more information).’

    RAISERROR(@ErrorMessage, 16, 1)
    END

    END TRY

    BEGIN CATCH
    SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_MESSAGE() AS ErrorMessage
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_PROCEDURE() AS ErrorProcedure

    GOTO Cleanup
    END CATCH

    — 1) Remove records older than user-provided number of days

    BEGIN TRY

    — By default, I’d like to keep 2 years of data
    SET @AgeOutDate = DATEADD(dd, (730 * -1), GETDATE())

    DELETE dbo.#AvailableDiskSpace
    WHERE DTStamp < @AgeOutDate

    END TRY

    BEGIN CATCH
    SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_MESSAGE() AS ErrorMessage
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_PROCEDURE() AS ErrorProcedure

    GOTO Cleanup
    END CATCH

    — 2) Get free space on drives

    BEGIN TRY
    — Populate temp table; this system proc gives us space in MB
    INSERT INTO #Space (DriveLetter, AvailableMB)
    EXEC master.dbo.xp_fixeddrives

    — 3) Loop through drives and get various attributes using OLE automation

    WHILE EXISTS (SELECT TOP 1 1 FROM #Space WHERE Processed = @No)

    BEGIN

    SELECT TOP 1
    @PK = PK,
    @DriveLetter = DriveLetter,
    @AvailableMB = AvailableMB,
    @DriveSize = DriveSize
    FROM #Space
    WHERE Processed = @No

    BEGIN TRY
    EXEC master.dbo.sp_OACreate 'Scripting.FileSystemObject', @FSO OUT
    END TRY

    BEGIN CATCH
    SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_MESSAGE() AS ErrorMessage
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_PROCEDURE() AS ErrorProcedure

    GOTO Cleanup
    END CATCH

    BEGIN TRY

    — Get disk info
    SET @DriveLetter = @DriveLetter + ':\'
    EXEC master.dbo.sp_OAMethod @FSO, 'GetDrive', @oDrive OUT, @DriveLetter
    EXEC master.dbo.sp_OAMethod @oDrive, 'TotalSize', @DriveSize OUT
    EXEC master.dbo.sp_OAMethod @oDrive, 'AvailableSpace', @RawFree OUT
    EXEC master.dbo.sp_OAGetProperty @oDrive, 'VolumeName', @VolumeName OUT

    END TRY

    BEGIN CATCH
    SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_MESSAGE() AS ErrorMessage
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_PROCEDURE() AS ErrorProcedure

    GOTO Cleanup
    END CATCH

    BEGIN TRY
    — Update table
    UPDATE #Space
    SET
    VolumeName =
    CASE LOWER(@DriveLetter)
    WHEN 'c:' THEN 'System'
    ELSE @VolumeName
    END,
    — Convert bytes to megabytes
    DriveSize = CONVERT(BIGINT, @DriveSize) / @TwoToTheTwentieth,
    — Convert bytes to megabytes
    AvailableMB = CONVERT(BIGINT, @RawFree) / @TwoToTheTwentieth
    WHERE PK = @PK;

    END TRY

    BEGIN CATCH
    SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_MESSAGE() AS ErrorMessage
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_PROCEDURE() AS ErrorProcedure

    GOTO Cleanup
    END CATCH

    BEGIN TRY

    — Destroy oDrive
    EXEC master.dbo.sp_OADestroy @oDrive

    END TRY

    BEGIN CATCH
    SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_MESSAGE() AS ErrorMessage
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_PROCEDURE() AS ErrorProcedure

    GOTO Cleanup
    END CATCH

    UPDATE #Space
    SET Processed = @YES
    WHERE PK = @PK

    END

    BEGIN TRY

    — Destroy FSO
    EXEC master.dbo.sp_OADestroy @FSO

    END TRY

    BEGIN CATCH
    SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_MESSAGE() AS ErrorMessage
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_PROCEDURE() AS ErrorProcedure

    GOTO Cleanup
    END CATCH

    — 4) Load results in table

    — Return disk-space info
    INSERT dbo.#AvailableDiskSpace
    (
    DriveLetter,
    VolumeName,
    AvailableMB,
    TotalDiskSpaceMB,
    DTStamp
    )

    SELECT
    DriveLetter DriveLetter,
    CASE
    WHEN LEN(RTRIM(LTRIM(VolumeName))) = 0 THEN 'Unknown'
    ELSE VolumeName
    END VolumeName,
    AvailableMB AvailableMB,
    DriveSize TotalDiskSpaceMB,
    @BATCHDATE LoadDate
    FROM #Space

    END TRY

    BEGIN CATCH
    SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_MESSAGE() AS ErrorMessage
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_PROCEDURE() AS ErrorProcedure

    GOTO Cleanup
    END CATCH

    Cleanup:

    — Ensure xp_cmdshell's off
    EXEC sp_configure 'OLE Automation Procedures', @NO;
    RECONFIGURE

    Select * From #AvailableDiskSpace
    drop table #AvailableDiskSpace
    drop table #Space

    Like

  13. I am using XP_fixeddrive for space monitoring of all the drives on a db server. But I want to exclude one drive from monitoring..How can I do so using xp_fixedrive? Suggestion would be highly appreciated.

    Like

  14. Hi PInal

    I have scenario relating to Tempdb .. in my server(test) i have 50 gb drive and 15 gb is free when i am running my SISS package to extract data from different source my tempdb size is reaching to 14 gb but the moment i stop job and re-start sql server services tempdb comes to 8 mb ..

    I have another server(dev) where c drive is 40 gb and 14 gb is free but there is d drive 700 gb but i checked tempdb files are still in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf

    Now the SISS package take 4 hr to get completed in dev but its taking 3 days in test can tempdb size increasing is reason for it .. if so how we can fix it

    Like

  15. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | Journey to SQL Authority with Pinal Dave

  16. I want to automate scripts for space check to be done daily basis.. Could anyone pls guide me how to create scripts for automating in SSMS 2012

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s