November 21, 2014 at 3:24 am
we can find free space on disks with 'xp_fixeddrives'.
i need script to find all disk size(total size\capacity) in the servers, any help ?
November 21, 2014 at 3:59 am
You don't need xp_fixeddrives any more:
SELECT DISTINCT vs.volume_mount_point
, vs.logical_volume_name
, vs.total_bytes
, vs.available_bytes
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
-- Gianluca Sartori
November 21, 2014 at 5:48 am
spaghettidba (11/21/2014)
You don't need xp_fixeddrives any more:
SELECT DISTINCT vs.volume_mount_point
, vs.logical_volume_name
, vs.total_bytes
, vs.available_bytes
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
I must admit I was excited when I saw this, but sys.dm_os_volume_stats is for 2012 and later. I'm still on 2008, so I'm out of luck.
Thank you, Gianluca - I've added this DMV to the things I have to look forward to if/when we migrate.
November 21, 2014 at 6:40 am
Ed Wagner (11/21/2014)
I must admit I was excited when I saw this, but sys.dm_os_volume_stats is for 2012 and later. I'm still on 2008, so I'm out of luck.Thank you, Gianluca - I've added this DMV to the things I have to look forward to if/when we migrate.
You're welcome. I think that this DMV is one of the improvements everybody had been waiting for years. When I first saw it, I had the same reaction as you 🙂
-- Gianluca Sartori
November 21, 2014 at 6:53 am
spaghettidba (11/21/2014)
Ed Wagner (11/21/2014)
I must admit I was excited when I saw this, but sys.dm_os_volume_stats is for 2012 and later. I'm still on 2008, so I'm out of luck.Thank you, Gianluca - I've added this DMV to the things I have to look forward to if/when we migrate.
You're welcome. I think that this DMV is one of the improvements everybody had been waiting for years. When I first saw it, I had the same reaction as you 🙂
Yeah, that's pretty powerful. I have a procedure that monitors the drive space on the drives on the SQL Server, but I don't use xp_fixeddrives. I use xp_cmdshell to fire fsutil instead. It lets me monitor all the drives and fire email if they get below a certain threshold. I'm thinking that using a DMV would probably be cheaper that having to shell out to DOS. If we ever get to upgrade, I'll definitely be comparing the two.
Thanks again.
November 21, 2014 at 7:03 am
On the few 2008 servers we still have, we're using PowerShell in a SQLAgent job.
-- Gianluca Sartori
November 21, 2014 at 7:06 am
charipg (11/21/2014)
we can find free space on disks with 'xp_fixeddrives'.i need script to find all disk size(total size\capacity) in the servers, any help ?
easy via Powershell
Get-Volume | ?{$_.DriveType -EQ "Fixed" -and $_.FileSystemLabel -notin
"System Reserved", "Q", "MSDTC" -and $_.DriveLetter -notin "C", "D"} |
sort-object FileSystemLabel
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 21, 2014 at 7:36 am
Ed Wagner (11/21/2014)
spaghettidba (11/21/2014)
You don't need xp_fixeddrives any more:
SELECT DISTINCT vs.volume_mount_point
, vs.logical_volume_name
, vs.total_bytes
, vs.available_bytes
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
I must admit I was excited when I saw this, but sys.dm_os_volume_stats is for 2012 and later. I'm still on 2008, so I'm out of luck.
Thank you, Gianluca - I've added this DMV to the things I have to look forward to if/when we migrate.
It works on 2008 R2. I didn't know it existed though, thanks Gianluca!
Am I right in thinking it will only return information for volumes that contain DB files?
November 21, 2014 at 7:41 am
You can use below script for the same, however you need to enable 'Ole Automation Procedures' to successfully run this script.
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB Numeric;
SET @MB = 1048576
CREATE TABLE #drives (
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD FOR SELECT drive from #drives ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,"TotalSize", @TotalSize OUT
IF @hr <> 0
EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives SET TotalSize=@TotalSize/@MB WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
Close dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
---=====================================
SELECTDrive, TotalSize as "Total(MB)", FreeSpace as "Free(MB)",
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as "Free(%)"
FROM #drives ORDER BY drive
DROP TABLE #drives
P.S.: Test the script on some test server before you use it in production.
November 21, 2014 at 7:54 am
Gazareth (11/21/2014)
It works on 2008 R2. I didn't know it existed though, thanks Gianluca!
Thanks, I didn't know it worked on 2008 R2. Maybe it arrived with a later SP? Which @@version are you on?
Am I right in thinking it will only return information for volumes that contain DB files?
Right: no way to inspect volumes with no database files.
-- Gianluca Sartori
November 21, 2014 at 8:52 am
spaghettidba (11/21/2014)
Gazareth (11/21/2014)
It works on 2008 R2. I didn't know it existed though, thanks Gianluca!Thanks, I didn't know it worked on 2008 R2. Maybe it arrived with a later SP? Which @@version are you on?
Ha, meant to put my SP level on but forgot! Can confirm it works as early as SP1, don't have any RTM servers around to test on.
November 25, 2014 at 2:25 pm
spaghettidba (11/21/2014)
On the few 2008 servers we still have, we're using PowerShell in a SQLAgent job.
Same here
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply