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

  • Perry Whittle

    SSC Guru

    Points: 233824

    For more info on mounted volumes see my article at this link[/url]

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

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Ed Wagner

    SSC Guru

    Points: 286975

    ramya.sqlservr (1/19/2016)


    Ed Wagner (1/19/2016)


    ramya.sqlservr (1/19/2016)


    Does this work for mounted volumes as well?

    We have mounted volumes on our servers and I generally use fsutil to get free space. I want to know if this procedure works on mounted volumes too.

    Thanks!

    If, by mounted volumes, you mean drive letters that point to LUNs on a SAN, then yes, it works fine. In fact, that's exactly the way I'm using it. I have local drives, but the ones I'm really interested in for growth are the data, log and backup, which are all SAN volumes.

    Actually, we have drives like D:\dbxx_data\ , D:\dbxx_log etc., And the command I use is like below.

    xp_cmdshell 'fsutil volume diskfree D:\dbxx_data'

    The approach could work, but you'd have to adapt it to accommodate the longer drive strings. Because I'm using different drive letters, I'm using a Char(1) and you would need a wider column. I'm familiar with fsutil, but I'm not sure how WMIC would output your results, as I don't have an environment where I could test it. Give the command a try and see what it does. If it gives you a good, consistent output, I see no reason why you couldn't adapt the approach.

    Better yet, if you're already capturing the total space and free space per volume, you could use the forecasting approach from the article to run over the data you already have. Just change the math where appropriate (example: you're capturing MB or GB) and you're off and running.

  • Ed Wagner

    SSC Guru

    Points: 286975

    Perry Whittle (1/19/2016)


    For more info on mounted volumes see my article at this link[/url]

    Very cool. Thanks, Perry.

  • Robert Sterbal

    SSChampion

    Points: 10984

    This is my ad hoc drive space query I use:

    /* drive space */ if (object_id('tempdb..#drivespace') is not null) drop table #drivespace create table #drivespace (spaceinfo varchar(256)) insert into #drivespace(spaceinfo) exec ('master..xp_cmdshell ''WMIC LogicalDisk Where DriveType="3" Get DeviceID,Size''') if (object_id('tempdb..#drive2') is not null) drop table #drive2 select left(spaceinfo,2) 'drive', convert(varchar, cast((right(spaceinfo,len(spaceinfo)-2)) as money)/1000000000, 1) 'Drive Space (GB)' into #drive2 from #drivespace where spaceinfo like '%C:%' or spaceinfo like '%D:%' or spaceinfo like '%F:%' if (object_id('tempdb..#freespace') is not null) drop table #freespace create table #freespace (spaceinfo varchar(256)) insert into #freespace(spaceinfo) exec ('master..xp_cmdshell ''WMIC LogicalDisk Where DriveType="3" Get DeviceID,Freespace''') if (object_id('tempdb..#free2') is not null) drop table #free2 select left(spaceinfo,2) 'drive', convert(varchar, cast((right(spaceinfo,len(spaceinfo)-2)) as money)/1000000000, 1) 'Free Space (GB)' into #free2 from #freespace where spaceinfo like '%C:%' or spaceinfo like '%D:%' or spaceinfo like '%F:%' select #free2.drive, #drive2.[Drive Space (GB)], #free2.[Free Space (GB)], left(convert(varchar,(convert(money,#free2.[Free Space (GB)]) / convert(money,#drive2.[Drive Space (GB)]))*100),4) 'Free %' from #free2 join #drive2 on #free2.drive = #drive2.drive-- select convert(varchar, cast((10000000000) as money)/1000000000, 1)

    I take advantage of the fact that line wrap is off in SSMS and it only take one line.

    Our setup only uses C, D and F for drives.

    412-977-3526 call/text

  • Perry Whittle

    SSC Guru

    Points: 233824

    ramya.sqlservr (1/19/2016)


    Ed Wagner (1/19/2016)


    ramya.sqlservr (1/19/2016)


    Does this work for mounted volumes as well?

    We have mounted volumes on our servers and I generally use fsutil to get free space. I want to know if this procedure works on mounted volumes too.

    Thanks!

    If, by mounted volumes, you mean drive letters that point to LUNs on a SAN, then yes, it works fine. In fact, that's exactly the way I'm using it. I have local drives, but the ones I'm really interested in for growth are the data, log and backup, which are all SAN volumes.

    Actually, we have drives like D:\dbxx_data\ , D:\dbxx_log etc., And the command I use is like below.

    xp_cmdshell 'fsutil volume diskfree D:\dbxx_data'

    Ed your wmic commamd will not handle mounted volumes. You need to use the command i posted above

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

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Ed Wagner

    SSC Guru

    Points: 286975

    Perry Whittle (1/19/2016)


    ramya.sqlservr (1/19/2016)


    Ed Wagner (1/19/2016)


    ramya.sqlservr (1/19/2016)


    Does this work for mounted volumes as well?

    We have mounted volumes on our servers and I generally use fsutil to get free space. I want to know if this procedure works on mounted volumes too.

    Thanks!

    If, by mounted volumes, you mean drive letters that point to LUNs on a SAN, then yes, it works fine. In fact, that's exactly the way I'm using it. I have local drives, but the ones I'm really interested in for growth are the data, log and backup, which are all SAN volumes.

    Actually, we have drives like D:\dbxx_data\ , D:\dbxx_log etc., And the command I use is like below.

    xp_cmdshell 'fsutil volume diskfree D:\dbxx_data'

    Ed your wmic commamd will not handle mounted volumes. You need to use the command i posted above

    Not only will the WMIC command not handle it, but the tables would have to be changed as well to allow more space for the drive column. I've never worked with mounted volumes, only drive letters than point to SAN volumes. Thanks for the help on this.

  • SQLRNNR

    SSC Guru

    Points: 281243

    Arjan Fraaij (1/19/2016)


    Hi very nice article.

    Only xp_cmdshell is not always enabled, because of possible security reasons. From SQL Server 2008R2 the Dynamic Management view sys.dm_os_volume_stats can be used. So I would prefer this one..

    SELECT DISTINCT

    @@SERVERNAME AS SQLInstance

    , DOVS1.logical_volume_name AS VolumeName

    , DOVS1.volume_mount_point AS DiskDrive

    , DOVS1.available_bytes AS FreeSpace

    , DOVS1.total_bytes AS CurrentSize

    FROM sys.master_files MF1

    CROSS APPLY sys.dm_os_volume_stats(mf1.database_id, mf1.FILE_ID) DOVS1

    Sizes are in bytes so devide by 1048576.0 to get sizes in MB..

    Regards,

    Arjan Fraaij

    I'd be careful using this dmf (which also requires elevated permission like xp_cmdshell). You can get duplicate results even with the distinct? Why is that? Well it sometimes reports different sizes for the remaining FreeSpace for the same volume (I have seen it do three or more rows per volume). If using the dmf, a rewrite to something like the following would be more resilient and duplicate proof:

    WITH presel AS (SELECT database_id, file_id,LEFT(mf1.physical_name,3) AS Volume, ROW_NUMBER() OVER (PARTITION BY LEFT(mf1.physical_name,3) ORDER BY mf1.database_id) AS RowNum

    FROM sys.master_files mf1)

    ,roundtwo AS (SELECT DISTINCT pr.database_id, pr.file_id

    FROM presel pr

    WHERE pr.RowNum = 1)

    SELECT @@SERVERNAME AS SQLInstance

    , ovs.logical_volume_name AS VolumeName

    , ovs.volume_mount_point AS DiskDrive

    , ovs.available_bytes AS FreeSpace

    , ovs.total_bytes AS CurrentSize

    FROM roundtwo mf

    CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) ovs;

    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

  • Perry Whittle

    SSC Guru

    Points: 233824

    Ed Wagner (1/19/2016)


    Perry Whittle (1/19/2016)


    ramya.sqlservr (1/19/2016)


    Ed Wagner (1/19/2016)


    ramya.sqlservr (1/19/2016)


    Does this work for mounted volumes as well?

    We have mounted volumes on our servers and I generally use fsutil to get free space. I want to know if this procedure works on mounted volumes too.

    Thanks!

    If, by mounted volumes, you mean drive letters that point to LUNs on a SAN, then yes, it works fine. In fact, that's exactly the way I'm using it. I have local drives, but the ones I'm really interested in for growth are the data, log and backup, which are all SAN volumes.

    Actually, we have drives like D:\dbxx_data\ , D:\dbxx_log etc., And the command I use is like below.

    xp_cmdshell 'fsutil volume diskfree D:\dbxx_data'

    Ed your wmic commamd will not handle mounted volumes. You need to use the command i posted above

    Not only will the WMIC command not handle it, but the tables would have to be changed as well to allow more space for the drive column. I've never worked with mounted volumes, only drive letters than point to SAN volumes. Thanks for the help on this.

    you're welcome

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

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Alan Burstein

    SSC Guru

    Points: 61075

    Just finished reading this. Excellent article Ed!

    Extra cudos on the clever title and the naming of #DatesOfDeath made me chuckle.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Ed Wagner

    SSC Guru

    Points: 286975

    Alan.B (1/20/2016)


    Just finished reading this. Excellent article Ed!

    Extra cudos on the clever title and the naming of #DatesOfDeath made me chuckle.

    Thanks, Alan. Hopefully the #DatesOfDeath name drives the point home. 😀

  • SQLRNNR

    SSC Guru

    Points: 281243

    Where are my manners?

    Well done on the article, 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

  • Eirikur Eiriksson

    SSC Guru

    Points: 182424

    Ed Wagner (1/19/2016)


    Eirikur Eiriksson (1/19/2016)


    Nice write-up Ed, good job!

    😎

    Just to mention, xp_fixeddrives is very handy for doing a quick check.

    Thanks, Eirikur. Yes, there are many approaches to getting the free space. What xp_fixeddrives doesn't give me is the volume size. I don't know of any parameters that'll give it to me. One of my goals was to be able to forecast when a volume will run out of space, so I had to be able to excluded days where the network team extended the volume or when we archived databases that were no longer needed.

    Edit: I picked WMIC because it gives me everything I need without having to loop through anything. On a side note, I think WMIC is such an awesome utility and has so much power that I was nearly beside myself when I learned of its existence. I was like a kid in a candy store. 😉

    I agree Ed, WMIC is a very powerful tool and combined with xp_cmdshell one almost never has to remote on to the servers. In fact so powerful that theoretically one could run the servers headless. Drawback is that the former relies on the latter plus SQL Server Service account with the permissions to run the former.

    😎

  • Brain2000

    Old Hand

    Points: 353

    Ed Wagner (1/19/2016)


    Network operations frequently has a monitor in place, but it doesn't do forecasting or keep history. By the time the drive space is so low that an alert is generated, the network team doesn't have time to allocate more space. Besides, what's going to happen when their alert is raised? They're going to call you and ask what's going on.

    History is built into these monitoring programs. You can choose when you want to be alerted. If DevOps configured the alert to go off at 1% free, then it's time to get a new DevOps employee.

    The old way of thinking is the hardware guys and software guys are at the opposite ends of the company. These days, not so much.

  • Ed Wagner

    SSC Guru

    Points: 286975

    SQLRNNR (1/20/2016)


    Where are my manners?

    Well done on the article, Ed!

    Thanks, Jason. I know it's not all things to all people in all scenarios, but it does one thing and I think it does it well. It's one of those things that grew out of necessity into something I now rely on.

  • Perry Whittle

    SSC Guru

    Points: 233824

    For me very good article, only down side was the lack of support for mounted volumes, but Ed has recognised this :satisfied:

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

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

Viewing 15 posts - 16 through 30 (of 46 total)

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