January 19, 2016 at 11:25 am
January 19, 2016 at 11:27 am
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.
January 19, 2016 at 11:29 am
Very cool. Thanks, Perry.
January 19, 2016 at 12:41 pm
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
January 19, 2016 at 1:35 pm
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
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 19, 2016 at 4:20 pm
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.
January 19, 2016 at 5:13 pm
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
@@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
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
January 20, 2016 at 7:09 am
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
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 20, 2016 at 7:11 am
Just finished reading this. Excellent article Ed!
Extra cudos on the clever title and the naming of #DatesOfDeath made me chuckle.
-- Itzik Ben-Gan 2001
January 20, 2016 at 7:43 am
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. 😀
January 20, 2016 at 10:57 am
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
January 20, 2016 at 11:36 am
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.
😎
January 20, 2016 at 12:32 pm
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.
January 20, 2016 at 6:56 pm
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.
January 21, 2016 at 5:27 am
For me very good article, only down side was the lack of support for mounted volumes, but Ed has recognised this :satisfied:
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 16 through 30 (of 55 total)
You must be logged in to reply to this topic. Login to reply