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

  • Have you ever tried the free tool "SQL DBA Dashboard"? You might find the information there.

    Download it from here http://www.sqlserverexamples.com/v2/Products/tabid/76/Default.aspx

  • DTS Package for Reporting Low Disk Space

    This VBscript code will check for low disk space on the local SQL Server. Schedule the package to run once a day, and let the job report to you by e-mail when a drive runs low on free space.

    The script will check all disks except for the C:\ drive and will trigger an alert to be sent when the available disk space on any drive falls below 20% remaining. The code uses the WMI object library which comes with Windows 2000 and XP. WMI can also be installed on NT.

    Author: Lennart Gerdvall

    Function Main()

    REM WHAT IT DOES FOR YOU:

    REM This code will check for low disk space on the local SQL Server.

    REM Schedule the package to run once a day and let the job report to

    REM you by e-mail when it fails. The code below checks all disks but

    REM the C-disk and fails when disk space is below 20% remaining.

    REM HOW TO INSTALL IT:

    REM Create a DTS package with the VB cript code below.

    REM Set up a connection to the local server with Windows Authenication.

    REM The Windows user running the package must be admin on the machine being checked.

    REM OTHER REQUIREMENTS:

    REM The package requires Windows Management Instrumentation (WMI), which is

    REM included in Windows 2000 and later. If you have Windows 95/98/NT 4.0, install

    REM the WMI core library (wmicore.exe).

    REM More information - http://msdn.microsoft.com/downloads/sdks/wmi/default.asp

    DIM DiskSet

    DIM myDisk, test

    myDisk = ""

    test = 0

    Set DiskSet = GetObject("winmgmts:{impersonationLevel=Delegate}").ExecQuery("select FreeSpace,Size,Name from Win32_LogicalDisk where DriveType=3")

    For Each Disk In DiskSet

    if disk.name <> "C:" then

    test = (Disk.FreeSpace / Disk.Size)

    If test <= 0.20 then

    myDisk = myDisk + Disk.Name

    End If

    end if

    Next

    If myDisk <> "" Then

    REM Yes, there are disks with LOW SPACE

    Main = DTSTaskExecResult_Failure

    Else

    REM No, there no are disks with LOW SPACE

    Main = DTSTaskExecResult_Success

    End If

    End Function

  • Very good...I tested and it works...thanks for your inputs...:cool:

  • very good---i tested and it works....thanks for your inputs....:D

  • send me dts package that you develop on my input,

    i will give it to my friends..........

  • rbarryyoung (8/26/2008)


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

    What is a System DSN... Are you talking ODBC?

    I would like for it to be a little more fluid than that. I don't want to create 30 ODBC connections on a server.

    I have a SQL Server table, containing all of the server addresses. Can I do something that is based on that?

  • That is where it logs To: you can monitor all 30 servers and log them all to the same place if 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]

  • How do I create an ODBC connection again? 🙂

  • It's under control panel; Administrative tools; Data Sources.

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

  • I set the ODBC connection up.

    I went to run it and got an error.

    Need to run; will try it later.

    Thanks a lot for helping.

  • Jason Wisdom (9/5/2008)


    I set the ODBC connection up.

    I went to run it and got an error.

    Need to run; will try it later.

    Thanks a lot for helping.

    If you want to enable CLR on your instance - and that's a big IF - I have a solution that might suit your needs.

    C# code:

    using System;

    using System.Data.SqlClient;

    using System.Diagnostics;

    using System.IO;

    using Microsoft.SqlServer.Server;

    namespace SQLCLR

    {

    public sealed partial class FileIO_PermSet_UNSAFE

    {

    [Microsoft.SqlServer.Server.SqlFunction]

    public static Int64 GetShareAvailableFreeSpace(string share)

    {

    using (SqlConnection conn = new SqlConnection("context connection = true"))

    {

    try

    {

    string arg = @"use B: " + share;

    Process p = Process.Start("net.exe", arg);

    p.WaitForExit();

    DriveInfo di = new DriveInfo("B");

    return di.AvailableFreeSpace;

    }

    catch (Exception e)

    {

    SendError(e);

    return -1;

    }

    finally

    {

    // Delete mapped drive

    Process p = Process.Start("net.exe", @"use B: /DELETE");

    p.WaitForExit();

    }

    }

    }

    private static void SendError(Exception e)

    {

    SqlContext.Pipe.Send("CLR Error :");

    SqlContext.Pipe.Send(e.Message);

    }

    }

    }

    Generate the dll in Visual Studio.

    Then create the assembly etc. like this:

    -- Script to install CLR stored procedure for File-System Operations

    -- Drop existing sproc and assembly if any.

    USE dbName

    GO

    -- Drop pre-existing objects if any:

    IF EXISTS (SELECT * FROM sys.objects WHERE [name] = 'CLR_udf_GetDriveFreeSpace')

    DROP FUNCTION dbo.CLR_udf_GetDriveFreeSpace;

    GO

    -- Drop assembly:

    IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'FileIO_PermSet_UNSAFE')

    DROP ASSEMBLY FileIO_PermSet_UNSAFE;

    GO

    use [master]

    go

    -- Drop login mapped to asymmetric key:

    IF EXISTS (SELECT * FROM sys.server_principals WHERE [name] = 'FileIO_PermSet_UNSAFE_Login')

    DROP LOGIN FileIO_PermSet_UNSAFE_Login;

    GO

    -- Drop asymmetric key:

    IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE [name] = 'FileIO_PermSet_UNSAFE_Key')

    DROP ASYMMETRIC KEY FileIO_PermSet_UNSAFE_Key;

    GO

    -- Create asymmetric-key login and asymmetric key

    -- (enter correct path of dll - must be local to server instance on which you are doing this install):

    CREATE ASYMMETRIC KEY FileIO_PermSet_UNSAFE_Key FROM EXECUTABLE FILE = 'd:\SQLCLR\FileIO_PermSet_UNSAFE.dll';

    CREATE LOGIN FileIO_PermSet_UNSAFE_Login FROM ASYMMETRIC KEY FileIO_PermSet_UNSAFE_Key

    GRANT UNSAFE ASSEMBLY TO FileIO_PermSet_UNSAFE_Login

    GO

    USE dbName

    GO

    -- Create the assembly:

    CREATE ASSEMBLY FileIO_PermSet_UNSAFE

    FROM 'D:\SQLCLR\FileIO_PermSet_UNSAFE.dll' -- Enter correct path of dll (must be local to server instance on which you are doing this install)

    WITH permission_set = UNSAFE ;

    GO

    -- Create the database objects:

    --CLR_udf_GetDriveFreeSpace

    CREATE FUNCTION dbo.CLR_udf_GetShareFreeSpace

    (

    @Share NVARCHAR(255)

    )

    RETURNS BIGINT

    AS EXTERNAL NAME FileIO_PermSet_UNSAFE.[SQLCLR.FileIO_PermSet_UNSAFE].GetShareAvailableFreeSpace;

    GO

    USE [master];

    GO

    Then use like this:

    select dbName.dbo.CLR_udf_GetShareFreeSpace('\\...\...\ ')

    The advantage of this solution is that, after the initial implementation, getting the information you want requires just this 1 line of code.

    The disadvantage is that, by necessity, the assembly is on the UNSAFE permission set, meaning that you need to ensure only processes that are "fully trusted" make use of it; in our shop, only DBAs can use it.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • For this type of process, I use a powershell script that runs on my central management server. The powershell script opens a connection to the database on that server, reads from the list of defined SQL Servers and then queries each SQL Server for various information including space utilization.

    Here is an example script:

    param(

    $inputServers

    )

    $errorActionPreference = "SilentlyContinue"

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.SMO") | Out-Null

    "$(Get-Date): Starting script";

    $dbadb = (New-Object "Microsoft.SqlServer.Management.Smo.Server" "**cmserver**").Databases["**cmdatabase**"];

    $ignoreDatabases = "tempdb", "model", "pubs", "Northwind", "Quest", "AdventureWorks", "AdventureWorksDW";

    if ($inputServers -eq $empty) {

    $servers = $dbadb.ExecuteWithResults("Select ServerName

    From dbo.SQLServerInfo

    Where SystemStatus = 'Active'").Tables[0]

    } else {

    $inputServers = ($inputServers | % {"'$($_)',"});

    $servers = $dbadb.ExecuteWithResults("Select ServerName

    From dbo.SQLServerInfo

    Where SystemStatus = 'Active'

    And ServerName In ($inputServers'NoServerMatch')").Tables[0]

    }

    & { $servers | % {

    Trap [System.UnauthorizedAccessException] {

    continue;

    }

    Write-Output "$(Get-Date): Processing Server...$($_.Servername)";

    $sql = New-Object ('Microsoft.sqlserver.management.smo.server') $_.ServerName

    $sqlNetName = $sql.Information.NetName;

    if ($sqlNetName -eq $null) {

    Write-Output "Error connecting to SQL Server: $($_.ServerName)";

    return;

    }

    $os = Get-WMIObject -class "Win32_OperatingSystem" -namespace "root\CIMV2" -computername $sqlNetName

    $comp = Get-WMIObject -class "Win32_ComputerSystem" -namespace "root\CIMV2" -computername $sqlNetName

    # Start building the query to update this server

    $query = "

    -- Updating Server information...

    Update dbo.SQLServerInfo

    Set Product = 'SQL Server $(switch ($sql.Information.Version.Major) {10 {2008} 9 {2005} 8 {2000} default {7}})'

    ,Edition = '$($sql.Information.Edition)'

    ,ProductLevel = '$($sql.Information.ProductLevel)'

    ,VersionString = '$($sql.Information.VersionString)'

    ,Caption = coalesce(nullif('$($os.Caption)', ''), Caption)

    ,CSDVersion = coalesce(nullif('$($os.CSDVersion)', ''), CSDVersion)

    ,Model = coalesce(nullif('$($comp.Manufacturer + ' ' + $comp.Model)', ''), Model)

    ,PhysicalMemory = '$($sql.Information.PhysicalMemory)'

    ,Processors = $($sql.Information.Processors)

    ,IsClustered = '$($sql.Information.IsClustered)'

    Where ServerName = '$($sql.Name)';`n

    -- Delete previous Database Information for this server

    Delete From dbo.DatabaseInfo Where ServerName = '$($sql.Name)'`n";

    if ($sql.Status -eq "Offline, Shutdown, AutoClosed") {

    Write-Output "`tDatabase $($_.Name) is '$($_.Status)' - cannot update information."; return;

    }

    $sql.Databases | % { if (!($ignoreDatabases -contains $_.Name)) {

    trap [Microsoft.SqlServer.Management.Common.ExecutionFailureException] {

    Write-Output $("Exception: $($_.Exception.Message)");

    Write-Output $("Base Exception: $($_.Exception.GetBaseException().Message)");

    return;

    }

    if ($_.Status -eq "Offline, AutoClosed") {

    Write-Output "`tDatabase $($_.Name) is '$($_.Status)' - cannot update information."; return;

    }

    $logSize = 0; $_.LogFiles | %{$logSize += ($_.Size / 1024)};

    $dbSize = 0; $_.FileGroups | %{$_.Files | %{$dbSize += ($_.Size / 1024)}};

    if ($dbSize -eq 0) {$dbSize = $_.Size};

    $query += "

    -- Insert Database Information

    Insert Into dbo.DatabaseInfo

    (ServerName

    ,DatabaseName

    ,DatabaseId

    ,Owner

    ,CreateDate

    ,CompatibilityLevel

    ,LastBackupDate

    ,RecoveryModel

    ,DatabaseSize

    ,LogFileSize)

    Values ('$($sql.Name)'

    ,'$($_.Name)'

    ,$(if ($_.Id) {$_.Id} else {999})

    ,'$(if ($_.Owner) {$_.Owner} else {'Unknown'})'

    ,'$($_.CreateDate)'

    ,'$($_.CompatibilityLevel)'

    ,'$($_.LastBackupDate.ToString('s') -replace '0001-01-01', '1900-01-01')'

    ,'$($_.DatabaseOptions.RecoveryModel)'

    ,$dbSize

    ,$logSize);

    -- Insert Data Space usage

    Delete From dbo.DataSpaceUsage

    Where ServerName = '$($sql.Name)'

    And DatabaseName = '$($_.Name)'

    And IndexedDTTM = dateadd(day, datediff(day, 0, getdate()), 0);

    Insert Into dbo.DataSpaceUsage

    (ServerName

    ,DatabaseName

    ,DatabaseSize

    ,DataSpaceUsage

    ,IndexSpaceUsage)

    Values ('$($sql.Name)'

    ,'$($_.Name)'

    ,$($dbSize * 1024)

    ,$(if ($_.DataSpaceUsage) {$_.DataSpaceUsage} else {0})

    ,$(if ($_.IndexSpaceUsage) {$_.IndexSpaceUsage} else {0}));`n"

    # Get backup history for this database

    $backupHistory = $sql.Databases["msdb"].ExecuteWithResults("

    Select database_name

    ,type As BackupType

    ,backup_size

    ,backup_start_date

    ,backup_finish_date

    From msdb.dbo.backupset

    Where database_name = '$($_.Name)'

    And backup_start_date >= (select min(backup_start_date)

    from (select top 5 backup_start_date

    from msdb.dbo.backupset

    where database_name = '$($_.Name)' and type = 'D'

    Order By backup_start_date desc) h)").Tables[0]

    $backupHistory | % {

    $query += "

    -- Insert Backup History

    Insert Into dbo.BackupHistory

    (ServerName

    ,DatabaseName

    ,BackupType

    ,BackupSize

    ,StartDate

    ,FinishDate)

    Values ('$($sql.Name)'

    ,'$($_.database_name)'

    ,'$($_.BackupType)'

    ,$($_.backup_size / 1024)

    ,'$($_.backup_start_date)'

    ,'$($_.backup_finish_date)');`n"

    }

    }

    }

    $query += "

    -- Purge data space usage

    Delete From dbo.DataSpaceUsage

    Where CollectedDTTM <= dateadd(year, -1, getdate());`n"

    $query > ".\Logs\GetSQLServerInfo_$($sql.Name -replace '\\', '_').sql.log";

    &{$dbadb.ExecuteNonQuery($query)} #comment out this line to build the sql script only

    trap [Microsoft.SqlServer.Management.Smo.FailedOperationException] {

    Write-Output $("Exception: $($_.Exception.Message)");

    Write-Output $("Base Exception: $($_.Exception.GetBaseException().Message)");

    continue;

    }

    }

    "$(Get-Date): Script Completed";

    }

    In this script, I am not getting specific drive space information - but you can easily add that by pulling the information for the WMI counters. This script is run daily and I have several reports built in Reporting Services so we can see the growth trend and a backup history report. I only pull the last five full backups for the report - but you can easily change that also.

    Note: **cmserver** and **cmdatabase** need to be changed to your own server and database. This also requires the tables be built, but I think you can see how to build them from the script.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Create a SP to and schedule it as a job, I use this and works very fast..

    CREATE PROCEDURE dbo.usp_DriveStats

    @drive char(1) = null,

    @ShowOutput int = 1,

    @TotalBytes bigint = null OUTPUT ,

    @FreeBytes bigint = null OUTPUT,

    @UsedBytes bigint = null OUTPUT,

    @PctFree float = null OUTPUT,

    @PctUsed float = null OUTPUT

    AS

    BEGIN

    SET NOCOUNT ON

    declare @thisDrive char(1)

    declare @driveStats table (

    Drive char(1),

    TotalBytes bigint,

    FreeBytes bigint,

    UsedBytes bigint,

    TotalMB int,

    FreeMB int,

    UsedMB int,

    PctUsed float(2),

    PctFree float(2)

    )

    create table #t_drives

    (drive char(1),

    freesizemb varchar(100) )

    insert into #t_drives

    exec master.dbo.xp_fixeddrives

    --if( @drive is not null )

    --begin

    --delete from #t_drives

    --where UPPER(drive) != UPPER(@drive)

    --end

    if( @TotalBytes is not null

    or @UsedBytes is not null

    or @PctFree is not null

    or @PctUsed is not null )

    begin

    set @ShowOutput = 0

    end

    --select * from #t_drives

    declare c_drives cursor for

    select drive from #t_drives

    open c_drives

    fetch next from c_drives into @thisDrive

    while( @@fetch_status = 0 )

    begin

    declare @totalSize varchar(200),

    @freeSpace varchar(200),

    @i_totalSize bigint,

    @i_freeSpace bigint

    exec usp_GetOAProperty

    'Scripting.FileSystemObject',

    'GetDrive',

    @thisDrive,

    'TotalSize',

    @totalSize output

    exec usp_GetOAProperty

    'Scripting.FileSystemObject',

    'GetDrive',

    @thisDrive,

    'FreeSpace',

    @freeSpace output

    --print 't=' + @totalSize

    --print 'f=' + @freeSpace

    declare @pct float,

    @pctStr varchar(100)

    select

    @i_totalSize = cast(@totalSize as bigint),

    @i_freeSpace = cast(@freeSpace as bigint),

    @pct = (cast(@freeSpace as float) / cast(@totalSize as float)) * 100.0

    -- set @pctStr = cast(@pct as varchar(100))

    -- print 'pct=' + @pctStr

    insert into @driveStats

    (Drive, TotalBytes, FreeBytes, UsedBytes, PctUsed, PctFree)

    values

    (@thisDrive, @i_totalSize, @i_freeSpace, (@i_totalSize - @i_freeSpace), (100 - @pct), @pct)

    fetch next from c_drives into @thisDrive

    end

    close c_drives

    deallocate c_drives

    -- select * from #t_drives

    drop table #t_drives

    update @driveStats

    set TotalMB = (TotalBytes / 1024 / 1024),

    UsedMB = (UsedBytes / 1024 / 1024),

    FreeMB = (FreeBytes / 1024 / 1024)

    if( @drive is not null )

    begin

    select

    @TotalBytes= TotalBytes,

    @FreeBytes= FreeBytes,

    @UsedBytes= UsedBytes,

    @PctFree= PctFree,

    @PctUsed= PctUsed

    from @driveStats

    where Drive = @drive

    end

    if( @ShowOutput != 0 )

    select * from @driveStats

    -- create a static list

    if( object_id('StaticDriveStats') is not null )

    begin

    -- print 'dropping table'

    drop table StaticDriveStats

    end

    select * into DB_Jobs.dbo.StaticDriveStats from @driveStats

    END

    GO

    %%%%% USP_GETOAPROPERTY%%%%%%%%%%%

    create proc usp_GetOAProperty

    @ScriptObject varchar(200),

    @InitMethod varchar(200) = null,

    @InitValue varchar(200),

    @PropertyName varchar(200),

    @ReturnValue varchar(200) output

    as

    begin

    declare @FS int,

    @rc int,

    @driveid int,

    @outvalue varchar(100)

    -- create fs object

    exec @rc = sp_OACreate

    @ScriptObject

    , @FS out

    if @rc <> 0

    begin

    raiserror('Error: Creating the file system object', 12, 1)

    return

    --print 'Error: Creating the file system object'

    end

    -- open text file for writing

    exec @rc = sp_OAMethod

    @FS,

    @InitMethod,

    @driveid output,

    @InitValue

    if @rc <> 0

    begin

    raiserror('Error: Unable to get drive info for c', 12, 1)

    return

    end

    -- write the message

    exec @rc = sp_OAGetProperty

    @driveid,

    @PropertyName

    , @outvalue out

    if @rc <> 0

    begin

    print cast(@rc as varchar)

    raiserror('Error: Error getting property: ', 12, 1)

    return

    end

    -- close file handle

    exec @rc = sp_OADestroy @driveid

    -- destroy object

    exec @rc = sp_OADestroy @FS

    set @ReturnValue = @outvalue

    --print 'value: ' + cast(@freespace as varchar)

    end

    /*

    begin

    declare @totalSize varchar(200),

    @freeSpace varchar(200)

    exec usp_GetOAProperty

    'Scripting.FileSystemObject',

    'GetDrive',

    'c',

    'TotalSize',

    @totalSize output

    exec usp_GetOAProperty

    'Scripting.FileSystemObject',

    'GetDrive',

    'c',

    'FreeSpace',

    @freeSpace output

    print 't=' + @totalSize

    print 'f=' + @freeSpace

    declare @pct float,

    @pctStr varchar(100)

    set @pct = (cast(@freeSpace as float) / cast(@totalSize as float)) * 100.0

    set @pctStr = cast(@pct as varchar(100))

    print 'pct=' + @pctStr

    end

    */

    GO

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • talentguy123 (9/7/2008)


    Create a SP to and schedule it as a job, I use this and works very fast..

    That works great, but if you go back to the very first post, the OP specifically stated that he could not use sp_OA* sprocs.

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

  • I like the powershell idea though, but I'm not proficient enough to evaluate it yet.

    [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 - 16 through 30 (of 44 total)

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