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

  • Ed Wagner

    SSC Guru

    Points: 286958

  • Arjan Fraaij

    SSC-Addicted

    Points: 464

    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

  • Brain2000

    Old Hand

    Points: 353

    I'm currently using an external monitoring software to let me know when I'm getting low on space. In this case I am using Nagios. It keeps the free space history for two years (configurable) and has other health checks as well. I have it making sure sqlservr.exe is running, that the logs don't contain anything devastating, the SAN health, and it even performs a simple end to end test every minute. The configuration is "cookie cutter" so I can apply it to a new Sql instance in seconds. I highly recommend monitoring software written just for this task.

    These days, it should be offered automatically by the hardware guys, who in a lot of cases also administrater the Sql server, which fills the disconnect that you were referring to in the article.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182347

    Nice write-up Ed, good job!

    😎

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

  • Sam Catchesides

    SSC Rookie

    Points: 36

    I implemented a similar solution at work, logging the disk free space (along with database free space) to a table along with when it was recorded. Simple SSRS report front end to graph out the last 30 days is emailed every morning, and the report allows selection of longer time periods and more specific drives/servers.

    Works well for us, and we can at a glance see if there is a worrying trend in disk usage occurring, and then we can drill down and see if a specific database is causing it. Has saved our bacon a couple of times.

  • Mike Cabolet

    Valued Member

    Points: 74

    I receive no results. When I check the procedure TrackDriveSpace, after creating the procedure, it is never used. It seems the source you show and the download source is not complete

    Mike


    MCA

  • Ed Wagner

    SSC Guru

    Points: 286958

    Brain2000 (1/19/2016)


    These days, it should be offered automatically by the hardware guys, who in a lot of cases also administrater the Sql server, which fills the disconnect that you were referring to in the article.

    Yes it should, but so many times, it is not. 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. I like to be the one to bring it to them instead of being hands-off and wait for them to ask me about it. Besides, as the DBA, I care more about space on the SQL Servers than they do because I'm the one who's going to catch it if they run out.

    If I tell my network team I need more space, I can provide them with a history of the volume going back however long they need it. They really appreciate it and also like the fact that I give them decent notice.

    There are, of course, many ways to approach this problem. This is only of them.

  • Ed Wagner

    SSC Guru

    Points: 286958

    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. 😉

  • Ed Wagner

    SSC Guru

    Points: 286958

    Sam Catchesides (1/19/2016)


    I implemented a similar solution at work, logging the disk free space (along with database free space) to a table along with when it was recorded. Simple SSRS report front end to graph out the last 30 days is emailed every morning, and the report allows selection of longer time periods and more specific drives/servers.

    Works well for us, and we can at a glance see if there is a worrying trend in disk usage occurring, and then we can drill down and see if a specific database is causing it. Has saved our bacon a couple of times.

    Awesome - That's the whole point of monitoring your drive space. It prevents you from getting into trouble in the first place.

  • Ed Wagner

    SSC Guru

    Points: 286958

    Mike Cabolet (1/19/2016)


    I receive no results. When I check the procedure TrackDriveSpace, after creating the procedure, it is never used. It seems the source you show and the download source is not complete

    Mike

    Did you execute the procedure? I have it scheduled to run in a database job once a day, which creates your space history. If it never runs, you won't have any data to query.

  • rchantler

    SSCrazy

    Points: 2091

    I think your approach will work on non-SQL servers as well. We are a small shop and we do something similar but include all servers. We collect statistics daily and generate a daily email to stay informed re diminishing space available. We also pull the data into Excel for quick graphing and analysis to do projections re when we'll run out.

  • ramya.sqlservr

    Ten Centuries

    Points: 1021

    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!

  • Ed Wagner

    SSC Guru

    Points: 286958

    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.

  • ramya.sqlservr

    Ten Centuries

    Points: 1021

    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'

  • Perry Whittle

    SSC Guru

    Points: 233784

    Ed Wagner (1/19/2016)


    If, by mounted volumes, you mean drive letters that point to LUNs on a SAN, then yes, it works fine.

    No the user is referring to volumes that mount under a folder on a root drive rather than use a specific drive letter

    Eirikur Eiriksson (1/19/2016)


    Nice write-up Ed, good job!

    😎

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

    Erm, no it's not it's useless

    ramya.sqlservr (1/19/2016)


    Does this work for mounted volumes as well?

    No, use this instead

    wmic volume get name, "free space", capacity

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

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

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

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