question

Nash9991 avatar image
Nash9991 asked

How do you get disk drive stats without the use of OLE Automation or XP_CMDSHELL?

Is there a way to get below drive information from SQL without the use of OLE Automation or xp_cmdshell? Drive Letter | Total Size | Space Used | Available Free Space I'm trying to gather this information without compromising SQL Server Security.
securityadministration
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

eghetto avatar image
eghetto answered
Give powershell a try :) $Instance = "localhost" # or what ever your name is... foreach ($db in Get-SqlDatabase $Instance | Select Name) { $Results = Get-SqlDatabase $Instance $db.name | Get-SqlDataFile | Select Server, dbname, FileGroup, FileName, Size, UsedSpace, FreeSpace, timestamp | Format-Table; $Results += Get-SqlDatabase $Instance $db.name | Get-SqlLogFile | Select Server, dbname, FileGroup, FileName, Size, UsedSpace, FreeSpace, timestamp | Format-Table; $Results; } or Param([string]$ComputerName="$env:computername") function GetDrivetypeName([int]$DriveType) { switch($DriveType) { 0 {'Unknown'} 1 {'No Root Directory'} 2 {'Removable Disk'} 3 {'Local Disk'} 4 {'Network Drive'} 5 {'Compact Disk'} 6 {'RAM Disk'} default {'?'} } } get-wmiobject -Computername $ComputerName win32_volume | format-table Driveletter,` Label,` @{Name="DriveType"; Expression = {GetDrivetypeName($_.DriveType)}},` @{Name="Capacity [GB]"; Expression = {[Math]::Round($_.Capacity/1024/1024/1024, 1)}},` @{Name="Free Space [GB]"; Expression = {[Math]::Round($_.FreeSpace/1024/1024/1024, 1)}},` @{Name="Free Space [%]"; Expression = {[Math]::Round(($_.FreeSpace/$_.Capacity)*100)}}` -autosize
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nash9991 avatar image Nash9991 commented ·
Hi Eghetto, I'm not familiar with powershell but your second option works. however, how do I export the results into a temp table in sql in order for me to query it in my report?
0 Likes 0 ·
Nash9991 avatar image Nash9991 commented ·
Thanks Eghetto. Will take a look and let you know.
0 Likes 0 ·
ramesh 1 avatar image
ramesh 1 answered
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nash9991 avatar image Nash9991 commented ·
Hi Ramesh, This won't help because it uses xp_cmdshell. I'm trying to get the information without the use of xp_cmdshell. Any suggestions?
1 Like 1 ·
ruancra avatar image
ruancra answered
This only gives you the drive letter and available free space: EXEC master..xp_fixeddrives
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.