How do I determine Free & Total Space for disk drives, using T-SQL?

  • Hello,

    I am working in an environment where disk space frequently runs out.

    I need to create a report that will tell FREE DISK SPACE, as well as TOTAL DISK SPACE, for every disk on the system.

    xp_fixeddrives returns FREE disk space, but not TOTAL disk space.

    xp_cmdshell will not work because this is a high security place.

    sp_OACreate, sp_OAMethod, etc. will not work for the same security reasons.

    How can I find TOTAL disk space?

    Thanks much.

  • Unfortunately the WMI counters accessible in SQL do not include this, but your best bet would be to poll the WMI counters and dump them into a table.

  • can you point to why the space runs out so frequently?

    maybe you just need to perform maintenance on the db.

  • I would suggest that you periodically run a collection agent as a BAT job on the server, but outside of SQL Server that collects this information and then use SQLCMD to insert into your tables (or an Import job to bring it in).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • princess.lipscomb (8/25/2008)


    can you point to why the space runs out so frequently?

    maybe you just need to perform maintenance on the db.

    Disk space is set to be low...to conserve resources until they are needed.

    At the same time, we have growing data stores, as we do a lot of historial logging & trend analysis.

    We also have new applications, which are doing more logging & trend analysis.

    Lastly, I have about 25 servers to keep track of, and I'd like one sweeping report that gathers & stores free/total disk space, for all server drives, & stores it all into a table so I can query it & produce trends.

    So the "Total Space" bit is pretty important.

  • Declare @BytesUsed Varchar(1000),

    @BytesFree Varchar(1000),

    @TotalBytes BIGINT,

    @IDENTITY INT,

    @drive Char(1),

    @sql Varchar(1000)

    SET NOCOUNT ON

    Create table ##DiskSpace ( Drive Char(1), TotalSpace Bigint, FreeSpace Bigint,

    PercentageFree as (FreeSpace*100 / TotalSpace ) )

    Create table #Fixeddrives ( Drive Char(1), FreeSpace Bigint)

    create table ##Dir ( ID INT IDENTITY , DriveSize Varchar(2000))

    Insert into #Fixeddrives exec master.dbo.xp_fixeddrives

    --select * from #Fixeddrives

    insert into ##DiskSpace ( Drive , FreeSpace)

    select Drive , FreeSpace from #Fixeddrives

    -- select * from ##DiskSpace

    DECLARE Drive_cursor CURSOR FOR

    SELECT Drive from ##DiskSpace

    OPEN Drive_cursor

    FETCH NEXT FROM Drive_cursor INTO @drive

    WHILE @@FETCH_STATUS = 0

    BEGIN

    select @sql = 'insert into ##Dir exec master.dbo.xp_cmdshell ''dir '+ @drive+':\ /S /-C'''

    exec(@sql)

    SELECT @IDENTITY = @@IDENTITY

    delete from ##Dir where ID < @IDENTITY - 4

    select @BytesUsed = substring (drivesize, charIndex ('File(s)', drivesize, 0)+ 9 , 1000)

    from ##Dir where drivesize like '%File(s)%'

    while patindex('%[^0-9]%', @BytesUsed) > 0

    begin

    set @BytesUsed = stuff( @BytesUsed, patindex('%[^0-9]%', @BytesUsed), 1, '' )

    end

    select @BytesFree = substring (drivesize, charIndex ('Dir(s)', drivesize, 0)+ 9 , 1000)

    from ##Dir where drivesize like '%Dir(s)%'

    while patindex('%[^0-9]%', @BytesFree) > 0

    begin

    set @BytesFree = stuff( @BytesFree, patindex('%[^0-9]%', @BytesFree), 1, '' )

    end

    select @TotalBytes = Convert(bigint, @BytesUsed)+ Convert(bigint, @BytesFree)

    select @TotalBytes = (@TotalBytes/ 1024)/1024 -- Coverting to MB....

    -- select @TotalBytes

    Update ##DiskSpace set TotalSpace = @TotalBytes

    WHERE Drive = @drive

    TRUNCATE TABLE ##Dir

    FETCH NEXT FROM Drive_cursor INTO @drive

    END

    CLOSE Drive_cursor

    DEALLOCATE Drive_cursor

    select * from ##DiskSpace

    HTH

    MJ

  • rbarryyoung (8/25/2008)


    I would suggest that you periodically run a collection agent as a BAT job on the server, but outside of SQL Server that collects this information and then use SQLCMD to insert into your tables (or an Import job to bring it in).

    Okay, you caught my eye. I have questions. 🙂

    - What is a collection agent?

    - What is a BAT job - a DOS batch file?

    - SQLCMD....I think I heard of this years ago. Is that like bcp, reading a text file and bulk-inserting into a raw table?

  • Jason Wisdom (8/25/2008)


    Okay, you caught my eye. I have questions. 🙂

    - What is a collection agent?

    It is just something that you write that can run independently of SQL Server.

    - What is a BAT job - a DOS batch file?

    Yes. Powershell would be even better, if you have it. WHS (Windows Hosting Script) is fine too (that's for VBScript/Javascript).

    - SQLCMD....I think I heard of this years ago. Is that like bcp, reading a text file and bulk-inserting into a raw table?

    No. SQLCMD is the SQL 2005 command line equivalent of a query window. It replaces OSQL from SQL 2000.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • You know, I had forgotten about this, but you can also setup a Perfmon process to log to SQL Server.

    If you set it up to run at system startup and log Logical Disk stats to SQL Server every, say 5 minutes (300 seconds), that should give you what you want.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • have you tried sp__diskspace?:unsure:

  • CREATE PROCEDURE sp_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 = host_name(), 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

    GO

    =====

    this is a stored proc that was published on this site a few weeks back. lol this is also where i remember seeing the stored proc name "sp_disk__space". try hopefully it will work for you:smooooth:

  • all the solutions given use:

    xp_cmdshell, sp_OACreate, sp_OAMethod

    which you state is not an option.

    what about clr procedures? you can access performance counters this way:

    PerformanceCounterCategory pcc = new PerformanceCounterCategory("LogicalDisk", SERVER);

    PerformanceCounter pcPercentFree = new PerformanceCounter("LogicalDisk", "% Free Space", INSTANCE, SERVER);

    PerformanceCounter pcFreeMbytes = new PerformanceCounter("LogicalDisk", "Free Megabytes", INSTANCE, SERVER);

    float percentfree = pcPercentFree.NextValue();

    float freespace = pcFreeMbytes.NextValue();

    float capacity = (freespace * 100) / percentfree;

    float usedspace = capacity - freespace;

  • rbarryyoung (8/25/2008)


    You know, I had forgotten about this, but you can also setup a Perfmon process to log to SQL Server.

    If you set it up to run at system startup and log Logical Disk stats to SQL Server every, say 5 minutes (300 seconds), that should give you what you want.

    Let me know if you want to know how to set this up.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Let me know if you want to know how to set this up.

    Actually, yes that would be incredible -

    I use Performance Monitor very often, and I usually have 20 counters running.

    So far it has always been at the real-time level.

    How would I insert its values into a database table?

  • Okay:

    First you have to make sure that you have a System DSN that points to the Database where you want to save the performance data. You can set this up unde the Data Source applet in the Control Panel, under Administration Tools.

    Then do the following:

    1. Start Performance Monitor.

    2. Select "Counter Logs" under "Performance Logs and Alerts"

    3. From the Action Menu, click "New Log Settings..."

    4. Enter a name like "Disk Stats"

    5. Click "Add Objects..." on the Dialog that pops-up

    6. On the Add Objects dialog, Select the Server Name (use local if you are actually on the server, which is better), then select the "Logical Disk" performance object and click Add, then Close..

    7. Change the interval to 3600 seconds (once an hour).

    8. Change the Run As to either the local Admin or your username and set the password.

    9. Go to the Log Files tab, change the Log File Type to SQL Database

    10. Click the Configure... button and select the System DSN that points to your Server & Database.

    11. Go to the Schedule tab and set it up to run immediately and to stop Manually.

    12. click OK.

    The log should start now. If it does not, this is almost always due to a problem with the "Run As.." authorization. Event Viewer can be helpful here.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

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

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