January 4, 2012 at 12:40 pm
I went into this thinking this should be an easy report using DMV's being that most of the info I need is already present on the canned disk space usage that comes with ssms..
I would like to see
Database name, Data File Name, Size, Amount Free (in file), Amount Free on Disk (or total size of disk)
to make it a little complicated each datafile is on a mount point on a san so xp_fixeddrives is no help. Basically I want to track how fast a database is growing and compare it to Disk availability so I know when to request more space from our SAN guys.
I have looked through various forums and blogs.. and found scripts to look at the database usage or the mount space usage using powershell and while very slick .. nothing that combines the two.
January 4, 2012 at 12:49 pm
This gets a little complicated with mount points and I think you'll need to get that info from Windows. We use a WMI query to capture this.
Select * from Win32_Volume
January 4, 2012 at 1:01 pm
Thank you for your quick reply .. haven't worked much with WMI I will have to look into how to use that ..
January 4, 2012 at 2:47 pm
It's kind of interesting, but you may be better off following up with the Powershell solution that you looked at. If you put this in a text file and save it with a .vbs extension (i.e. DriveInfo.vbs, you can run it from the CMD prompt. Navigate to where you saved the file and type this cscript command, it will save the output to C:\Temp\DriveInfo.txt.
cscript.exe DriveInfo.vbs > C:\Temp\DriveInfo.txt
' Return Server Volume Information in comma-separated list (CSCRIPT //NOLOGO only)
If Right(WScript.FullName,11)<>"cscript.exe" Then WScript.Quit 0
strServer = "."
If WScript.Arguments.Count > 0 Then strServer = WScript.Arguments(0)
For i=1 To WScript.Arguments.Count -1
sHeader = sHeader & WScript.Arguments(i) & ","
Next
Set wmi = GetObject("winmgmts:\\" & strServer & "\root\CIMV2")
Set volumes = wmi.ExecQuery("Select * from Win32_Volume")
For Each v In volumes
WScript.Echo sHeader & v.DriveLetter &","& v.Name &","& v.Label &","& v.Capacity &","& v.FreeSpace &","& v.DriveType
Next
Set volumes=Nothing
Set wmi=Nothing
January 4, 2012 at 2:50 pm
jspatz (1/4/2012)
I went into this thinking this should be an easy report using DMV's being that most of the info I need is already present on the canned disk space usage that comes with ssms..I would like to see
Database name, Data File Name, Size, Amount Free (in file), Amount Free on Disk (or total size of disk)
to make it a little complicated each datafile is on a mount point on a san so xp_fixeddrives is no help. Basically I want to track how fast a database is growing and compare it to Disk availability so I know when to request more space from our SAN guys.
I have looked through various forums and blogs.. and found scripts to look at the database usage or the mount space usage using powershell and while very slick .. nothing that combines the two.
I use the following via a sql server agent job and then suck the resulting file into sql server and process it to get the mount point space usage.
wmic /output:"C:\myfolder\myfile.txt" volume get capacity, "free space", name
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 5, 2012 at 1:52 pm
Thanks guys for you help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply