January 19, 2016 at 12:19 am
Comments posted to this topic are about the item Time and Space: How to Monitor Drive Space in SQL Server
January 19, 2016 at 1:11 am
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
@@SERVERNAMEAS SQLInstance
,DOVS1.logical_volume_nameAS VolumeName
,DOVS1.volume_mount_pointAS DiskDrive
,DOVS1.available_bytesAS FreeSpace
,DOVS1.total_bytesAS 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
January 19, 2016 at 4:30 am
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.
January 19, 2016 at 5:13 am
Nice write-up Ed, good job!
😎
Just to mention, xp_fixeddrives is very handy for doing a quick check.
January 19, 2016 at 5:44 am
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.
January 19, 2016 at 5:44 am
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
January 19, 2016 at 7:01 am
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.
January 19, 2016 at 7:11 am
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. 😉
January 19, 2016 at 7:16 am
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.
January 19, 2016 at 7:18 am
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 completeMike
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.
January 19, 2016 at 8:10 am
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.
January 19, 2016 at 10:44 am
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!
January 19, 2016 at 10:54 am
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.
January 19, 2016 at 11:03 am
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'
January 19, 2016 at 11:22 am
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
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 55 total)
You must be logged in to reply to this topic. Login to reply