Time and Space: How to Monitor Drive Space in SQL Server

  • mhickin 11975

    SSC Journeyman

    Points: 87

    If you use mount points, then the approach outlined here isn't going to do do much for you.

    Also, if you are using clusters, then it's often useful to screen out any drives that don't apply to your instance.

    I use the following which does a pretty decent job of giving you the info you need, even if you're using mount points

    ;WITH FileIds

    AS

    ( SELECT DISTINCT database_id, file_id

    FROM master.sys.master_files AS mf

    )

    SELECT DISTINCT

    volume_mount_point AS VolumeName ,

    logical_volume_name,

    (total_bytes / 1073741824) SizeGB ,

    (available_bytes / 1073741824) FreeSpaceGB,

    CAST((available_bytes / 1073741824.0)/ (total_bytes / 1073741824.0)*100 AS NUMERIC( 5, 2)) PercentFree

    FROM FileIds f

    CROSS APPLY sys.dm_os_volume_stats (f.database_id, f.file_id)

  • SQLRNNR

    SSC Guru

    Points: 281243

    mhickin 11975 (1/21/2016)


    If you use mount points, then the approach outlined here isn't going to do do much for you.

    Also, if you are using clusters, then it's often useful to screen out any drives that don't apply to your instance.

    I use the following which does a pretty decent job of giving you the info you need, even if you're using mount points

    ;WITH FileIds

    AS

    ( SELECT DISTINCT database_id, file_id

    FROM master.sys.master_files AS mf

    )

    SELECT DISTINCT

    volume_mount_point AS VolumeName ,

    logical_volume_name,

    (total_bytes / 1073741824) SizeGB ,

    (available_bytes / 1073741824) FreeSpaceGB,

    CAST((available_bytes / 1073741824.0)/ (total_bytes / 1073741824.0)*100 AS NUMERIC( 5, 2)) PercentFree

    FROM FileIds f

    CROSS APPLY sys.dm_os_volume_stats (f.database_id, f.file_id)

    See my previous post about the use of this DMF. The query you have written is susceptible to duplicate rows for the same volume despite the use of distinct.

    Also, the use of the CTE to get distinct fileid and databaseid is unnecessary. The combination of fileid and databaseid is unique in sys.master_files already.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR

    SSC Guru

    Points: 281243

    For more info on the duplication from the dmf, this article outlines what is happening:

    http://bit.ly/1lzDKLO

    [/b]

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Ed Wagner

    SSC Guru

    Points: 286982

    SQLRNNR (1/21/2016)


    For more info on the duplication from the dmf, this article outlines what is happening:

    http://bit.ly/1lzDKLO

    [/b]

    Very nice article, Jason. Great explanation of why not to use the DMF.

  • SQLRNNR

    SSC Guru

    Points: 281243

    Ed Wagner (1/21/2016)


    SQLRNNR (1/21/2016)


    For more info on the duplication from the dmf, this article outlines what is happening:

    http://bit.ly/1lzDKLO

    [/b]

    Very nice article, Jason. Great explanation of why not to use the DMF.

    Thanks Ed.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Well written, thanks.

  • maroon-78

    Ten Centuries

    Points: 1014

    I had to change the CTE used to query the data out of the table. The PCT Free calc was not correct.

    replace

    CONVERT(Numeric(18, 1), 100 - (r.Free * 100.0 / r.Total)) FreePercent

    with

    CONVERT(Numeric(18, 1), 100.0 * (r.Free * 1.0) / (r.Total * 1.0)) FreePercent

    to get the correct FreePercent

    Thanks for the great article. I am using it to forecast space growth.

  • Ed Wagner

    SSC Guru

    Points: 286982

    That's so nice to hear. Thanks very much for the feedback.

  • Ricky Lively

    Say Hey Kid

    Points: 661

    If you don't want to enable xp_cmdshell, you can put the WMIC command in a job step that can capture the output to a file that you can bulk insert into a table.

  • bonagiris9

    Grasshopper

    Points: 16

    I recently implemented this procedure in our test environment, and I don't see any data in Drivespace. Not sure why it is not populating the data in Drive space table. I am using 2008 R2 version. If i query #incoming table i see the data attached in below picture. Can someone help here?

     

    Capture

  • Jeff Moden

    SSC Guru

    Points: 996622

    bonagiris9 wrote:

    I recently implemented this procedure in our test environment, and I don't see any data in Drivespace. Not sure why it is not populating the data in Drive space table. I am using 2008 R2 version. If i query #incoming table i see the data attached in below picture. Can someone help here?

    Capture

    I'm thinking that Drive A: is a floppy drive and drive D: is a CD and neither one of them have a disk inserted.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • bonagiris9

    Grasshopper

    Points: 16

    I don't think that is the issue, if i remove the line Create procedure dbo.trackdrivespace as and Begin it works fine. the data gets inserted into trackdrivespace.

    CREATE PROCEDURE dbo.TrackDriveSpace

    AS

    DECLARE @dtmNow Datetime = GETDATE();

    BEGIN

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

    --initialization

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

    --SET NOCOUNT ON;

    --create a temp table to capture our incoming rows from the dos command

    IF OBJECT_ID('tempdb.dbo.#incoming', 'u') IS NOT NULL DROP TABLE #incoming;

    CREATE TABLE #incoming (

    RawLine Varchar(255),

    N integer not null identity(1, 1));

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

    --shell out to dos and call wmic to get the disk space data needed

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

    INSERT INTO #incoming(rawline)

    EXECUTE xp_cmdshell 'wmic logicaldisk get deviceid,freespace,size';

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

    --extract the data and write the rows to the permanent table

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

    WITH cteHeader AS (

    --read our header row

    SELECT RawLine

    FROM #incoming

    WHERE N = 1

    ),

    cteStart AS (

    --determine the starting positions of each column to extract

    SELECT DriveStart = 1,

    FreeStart = CHARINDEX('FreeSpace', h.RawLine),

    TotalStart = CHARINDEX('Size', h.RawLine)

    FROM cteHeader h

    ),

    cteDriveInfo AS (

    --isolate each "column" of data, allowing for the return at the end of the last column

    SELECT Drive = SUBSTRING(i.RawLine, p.DriveStart, 1),

    FreeBytes = LTRIM(RTRIM(SUBSTRING(i.RawLine, p.FreeStart, p.TotalStart - p.FreeStart))),

    TotalBytes = RTRIM(LTRIM(RTRIM(REPLACE(SUBSTRING(i.Rawline, p.TotalStart, 99), CHAR(13), ''))))

    FROM #incoming i

    CROSS APPLY cteStart p

    WHERE i.N > 1

    AND RawLine IS NOT NULL

    AND NOT SUBSTRING(RawLine, 3, 99) = REPLICATE(SUBSTRING(RawLine, 3, 99), ' ')

    )

    INSERT INTO dbo.DriveSpace(Drive, Total, Free, EntryDate)

    SELECT LOWER(Drive), CONVERT(Bigint, TotalBytes), CONVERT(Bigint, FreeBytes), @dtmNow

    FROM cteDriveInfo

    WHERE NOT TotalBytes = ''

    ORDER BY Drive;

    END

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

    --termination

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

    DROP TABLE #incoming;

  • bonagiris9

    Grasshopper

    Points: 16

    Jeff Moden wrote:

    bonagiris9 wrote:

    I recently implemented this procedure in our test environment, and I don't see any data in Drivespace. Not sure why it is not populating the data in Drive space table. I am using 2008 R2 version. If i query #incoming table i see the data attached in below picture. Can someone help here?

    Capture

    I'm thinking that Drive A: is a floppy drive and drive D: is a CD and neither one of them have a disk inserted.

     

     

    I don't think that is the issue, if i remove the line Create procedure dbo.trackdrivespace as and Begin it works fine. the data gets inserted into trackdrivespace.

    CREATE PROCEDURE dbo.TrackDriveSpace

    AS

    DECLARE @dtmNow Datetime = GETDATE();

    BEGIN

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

    --initialization

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

    --SET NOCOUNT ON;

    --create a temp table to capture our incoming rows from the dos command

    IF OBJECT_ID('tempdb.dbo.#incoming', 'u') IS NOT NULL DROP TABLE #incoming;

    CREATE TABLE #incoming (

    RawLine Varchar(255),

    N integer not null identity(1, 1));

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

    --shell out to dos and call wmic to get the disk space data needed

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

    INSERT INTO #incoming(rawline)

    EXECUTE xp_cmdshell 'wmic logicaldisk get deviceid,freespace,size';

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

    --extract the data and write the rows to the permanent table

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

    WITH cteHeader AS (

    --read our header row

    SELECT RawLine

    FROM #incoming

    WHERE N = 1

    ),

    cteStart AS (

    --determine the starting positions of each column to extract

    SELECT DriveStart = 1,

    FreeStart = CHARINDEX('FreeSpace', h.RawLine),

    TotalStart = CHARINDEX('Size', h.RawLine)

    FROM cteHeader h

    ),

    cteDriveInfo AS (

    --isolate each "column" of data, allowing for the return at the end of the last column

    SELECT Drive = SUBSTRING(i.RawLine, p.DriveStart, 1),

    FreeBytes = LTRIM(RTRIM(SUBSTRING(i.RawLine, p.FreeStart, p.TotalStart - p.FreeStart))),

    TotalBytes = RTRIM(LTRIM(RTRIM(REPLACE(SUBSTRING(i.Rawline, p.TotalStart, 99), CHAR(13), ''))))

    FROM #incoming i

    CROSS APPLY cteStart p

    WHERE i.N > 1

    AND RawLine IS NOT NULL

    AND NOT SUBSTRING(RawLine, 3, 99) = REPLICATE(SUBSTRING(RawLine, 3, 99), ' ')

    )

    INSERT INTO dbo.DriveSpace(Drive, Total, Free, EntryDate)

    SELECT LOWER(Drive), CONVERT(Bigint, TotalBytes), CONVERT(Bigint, FreeBytes), @dtmNow

    FROM cteDriveInfo

    WHERE NOT TotalBytes = ''

    ORDER BY Drive;

    END

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

    --termination

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

    DROP TABLE #incoming;

  • Ed Wagner

    SSC Guru

    Points: 286982

    bonagiris9 wrote:

    I don't think that is the issue, if i remove the line Create procedure dbo.trackdrivespace as and Begin it works fine. the data gets inserted into trackdrivespace.

    This tells me that you've probably created the procedure, but haven't run it.  Once you create it, you have to run it to collect the data and write it to your table.  I do this with a database job that just fires the procedure and is scheduled to run once a day.

  • bonagiris9

    Grasshopper

    Points: 16

    Ed Wagner wrote:

    bonagiris9 wrote:

    I don't think that is the issue, if i remove the line Create procedure dbo.trackdrivespace as and Begin it works fine. the data gets inserted into trackdrivespace.

    This tells me that you've probably created the procedure, but haven't run it.  Once you create it, you have to run it to collect the data and write it to your table.  I do this with a database job that just fires the procedure and is scheduled to run once a day.

     

    Thanks much, It works fine now. how ever coming to the next part . I was just testing following snippet to see the data that gets inserted into drive info table and i see only one row . Attached serverDrives and drivespace output as well.Drivespace

    Driveinfo

     

     

    WITH cteDrives AS (

    --start with the list of drives to check for this server

    SELECT DriveLetter, Description

    FROM svr.ServerDrives

    WHERE ServerName = @@SERVERNAME

    ),

    WITH cteDrives AS (

    --start with the list of drives to check for this server

    SELECT DriveLetter, Description

    FROM svr.ServerDrives

    WHERE ServerName = @@SERVERNAME

    ),

    cteDriveInfo AS (

    --query a list of dates going back @Days days and use a crosstab to pivot the rows into columns for each date.

    --this gives a table with date and free space on the data, log and backup drives, along with an ascending

    --integer for each row.

    SELECT ROW_NUMBER() OVER(ORDER BY ds.EntryDate) RowNum, ds.EntryDate,

    DataFree = CONVERT(Numeric(18, 3), MAX(CASE WHEN d.Description = 'Data' THEN ds.Free * 1.0 / POWER(1024, 3) END)),

    LogFree = CONVERT(Numeric(18, 3), MAX(CASE WHEN d.Description = 'Logs' THEN ds.Free * 1.0 / POWER(1024, 3) END)),

    BackupFree = CONVERT(Numeric(18, 3), MAX(CASE WHEN d.Description = 'Backup' THEN ds.Free * 1.0 / POWER(1024, 3) END))

    FROM cteDrives d

    INNER JOIN dbo.DriveSpace ds ON ds.Drive = d.DriveLetter

    WHERE EntryDate >= DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()) - 0)

    GROUP BY ds.EntryDate

    ),

    cteDailyGrowth AS (

    --using the row number, join the drive free table to itself, offset by one day. this gets the free

    -- space for each drive on that day and also on the next day.

    SELECT di1.RowNum, di1.EntryDate, di1.DataFree CurrentData, di1.LogFree CurrentLog, di1.BackupFree CurrentBackup,

    di2.DataFree NextData, di2.LogFree NextLog, di2.BackupFree NextBackup

    FROM cteDriveInfo di1

    INNER JOIN cteDriveInfo di2 ON di2.RowNum = di1.RowNum + 1

    )

    --populate the temp table with the drive, date and free space for each drive on the date and the following date.

    --the growth of each drive is the current minus the next.

    INSERT INTO #DriveInfo(ID, EntryDate, CurrentData, CurrentLog, CurrentBackup,

    NextData, NextLog, NextBackup, DataGrowth, LogGrowth, BackupGrowth)

    SELECT RowNum, CAST(EntryDate AS Date), CurrentData, CurrentLog, CurrentBackup, NextData, NextLog, NextBackup,

    DataGrowth = CurrentData - NextData,

    LogGrowth = CurrentLog - NextLog,

    BackupGrowth = CurrentBackup - NextBackup

    FROM cteDailyGrowth

    ORDER BY EntryDate;

    • This reply was modified 9 months, 2 weeks ago by  bonagiris9.

Viewing 15 posts - 31 through 45 (of 46 total)

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