getting total disk space

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » getting total disk space

15 posts, Page 2 of 2

««1 2

getting total disk space

Rate Topic

Display Mode

Topic Options

Author

Message

anthony.green

Posted Tuesday, January 29, 2013 2:51 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

opc.three (1/26/2013)

anthony.green (1/22/2013)

Powershell will probably be the best bet on that

Get-WmiObject win32_logicaldisk  | where-Object {$_.providername -like ''} | select deviceid, size

Win32_LogicalDisk will work in most cases but know that it does not pickup mount points. For a more comprehensive view (post Windows 2000 Server) use the Win32_Volume class instead.

Get-WmiObject Win32_Volume | gm

Many thanks for that, as I have not worked with mount points before (well not to my better knowledge) I was un aware of that, added to the knowledge bank.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1412843

Perry Whittle

Posted Tuesday, January 29, 2013 4:03 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:53 AM
Points: 5,204, Visits: 11,158

anthony.green (1/29/2013)

Many thanks for that, as I have not worked with mount points before (well not to my better knowledge) I was un aware of that, added to the knowledge bank.

Check my article at this link for more info on mount points and SQL Server.

The problem with mount points is that when you execute XP_FIXEDDRIVES it only enumerates the root drives and won't return information from the volume level. It's all detailed in my article, post back if you're unsure of the workings of it all.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1412884

anthony.green

Posted Tuesday, January 29, 2013 4:07 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Perry Whittle (1/29/2013)

anthony.green (1/29/2013)

Many thanks for that, as I have not worked with mount points before (well not to my better knowledge) I was un aware of that, added to the knowledge bank.

Thanks Perry, added to my list of further reading.

Post #1412887

Andrew Pruski

Posted Tuesday, January 29, 2013 6:29 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:15 AM
Points: 841, Visits: 500

One really good powershell script is one a colleague sent me that emails me total disk space and free disk space for all servers listed in a text file:-

Have a look Smile

# First lets create a text file, where we will later save the freedisk space info
$freeSpaceFileName = "<LOCATION>"
$serverlist = "<LOCATION>\Servers.txt"
$warning = 20
$critical = 10
$date = Get-Date -format d
New-Item -ItemType file $freeSpaceFileName -Force
# Getting the freespace info using WMI
#Get-WmiObject win32_logicaldisk | Where-Object {$_.drivetype -eq 3} | format-table DeviceID, VolumeName,status,Size,FreeSpace | Out-File FreeSpace.txt
# Function to write the HTML Header to the file
Function writeHtmlHeader
{
param($fileName)
$date = Get-Date -format d
Add-Content $fileName "<html>"
Add-Content $fileName "<head>"
Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>"
Add-Content $fileName '<title> $Date DiskSpace Report</title>'
add-content $fileName '<STYLE TYPE="text/css">'
add-content $fileName ""
add-content $fileName "</style>"
Add-Content $fileName "</head>"
Add-Content $fileName "<body>"

add-content $fileName "<table width='100%'>"
add-content $fileName "<tr bgcolor='#CCCCCC'>"
add-content $fileName "<td colspan='7' height='25' align='center'>"
add-content $fileName "<font face='tahoma' color='#003399' size='4'><strong>DiskSpace Report - $date</strong></font>"
add-content $fileName "</td>"
add-content $fileName "</tr>"
add-content $fileName "</table>"

}

# Function to write the HTML Header to the file
Function writeTableHeader
{
param($fileName)

Add-Content $fileName "<tr bgcolor=#CCCCCC>"
Add-Content $fileName "<td width='10%' align='center'>Drive</td>"
Add-Content $fileName "<td width='50%' align='center'>Drive Label</td>"
Add-Content $fileName "<td width='10%' align='center'>Total Capacity(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Used Capacity(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Free Space(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Freespace %</td>"
Add-Content $fileName "</tr>"
}

Function writeHtmlFooter
{
param($fileName)

Add-Content $fileName "</body>"
Add-Content $fileName "</html>"
}

Function writeDiskInfo
{
param($fileName,$devId,$volName,$frSpace,$totSpace)
$totSpace=[math]::Round(($totSpace/1073741824),2)
$frSpace=[Math]::Round(($frSpace/1073741824),2)
$usedSpace = $totSpace - $frspace
$usedSpace=[Math]::Round($usedSpace,2)
$freePercent = ($frspace/$totSpace)*100
$freePercent = [Math]::Round($freePercent,0)
if (($freePercent -le $warning) -and ($FreePercent -gt $critical))
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td align=center>$devid</td>"
Add-Content $fileName "<td align=center>$volName</td>"

Add-Content $fileName "<td align=center>$totSpace</td>"
Add-Content $fileName "<td align=center>$usedSpace</td>"
Add-Content $fileName "<td align=center>$frSpace</td>"
Add-Content $fileName "<td bgcolor='#FFE600' align=center>$freePercent</td>"
Add-Content $fileName "</tr>"
}
elseif ($freePercent -le $critical)
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td align=center>$devid</td>"
Add-Content $fileName "<td align=center>$volName</td>"
Add-Content $fileName "<td align=center>$totSpace</td>"
Add-Content $fileName "<td align=center>$usedSpace</td>"
Add-Content $fileName "<td align=center>$frSpace</td>"
Add-Content $fileName "<td bgcolor='#FF0000' align=center>$freePercent</td>"
Add-Content $fileName "</tr>"
}
else
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td align=center>$devid</td>"
Add-Content $fileName "<td align=center>$volName</td>"
Add-Content $fileName "<td align=center>$totSpace</td>"
Add-Content $fileName "<td align=center>$usedSpace</td>"
Add-Content $fileName "<td align=center>$frSpace</td>"
Add-Content $fileName "<td align=center>$freePercent</td>"
Add-Content $fileName "</tr>"
}
}
Function sendEmail
{ param($from,$to,$subject,$smtphost,$htmlFileName)
$body = Get-Content $htmlFileName
$smtp= New-Object System.Net.Mail.SmtpClient $smtphost
$msg = New-Object System.Net.Mail.MailMessage $from, $to, $subject, $body
$msg.isBodyhtml = $true
$smtp.send($msg)

}

writeHtmlHeader $freeSpaceFileName
foreach ($server in Get-Content $serverlist)
{
Add-Content $freeSpaceFileName "<table width='100%'><tbody>"
Add-Content $freeSpaceFileName "<tr bgcolor='#CCCCCC'>"
Add-Content $freeSpaceFileName "<td width='100%' align='center' colSpan=6><font face='tahoma' color='#003399' size='2'><strong> $server </strong></font></td>"
Add-Content $freeSpaceFileName "</tr>"

writeTableHeader $freeSpaceFileName

$dp = Get-WmiObject win32_logicaldisk -ComputerName $server | Where-Object {$_.drivetype -eq 3}
foreach ($item in $dp)
{
Write-Host $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size
writeDiskInfo $freeSpaceFileName $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size

}
Add-Content $freeSpaceFileName "</table>"
}

writeHtmlFooter $freeSpaceFileName
$date = Get-Date -format d
sendEmail <SENDER EMAIL ADDRESS> <RECIPIENT EMAIL ADDRESS> "Disk Space Report - $Date" <SMTP SERVER> $freeSpaceFileName

Post #1412992

ALZDBA

Posted Tuesday, January 29, 2013 6:58 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 6,862, Visits: 8,049

Did you try this ?

SELECT distinct
       vs.volume_mount_point
      , vs.volume_id
      , vs.logical_volume_name
      , vs.file_system_type
      , vs.total_bytes / 1048576 AS 'MBTotal'
      , vs.available_bytes / 1048576 AS 'MBAvailable'
      , CAST( 1.00 * vs.available_bytes / vs.total_bytes * 100 as decimal(5,2) ) as pctFree
      , vs.supports_compression
      , vs.supports_alternate_streams
      , vs.supports_sparse_files
      , vs.is_read_only
      , vs.is_compressed
FROM    [sys].[master_files] AS f
CROSS APPLY [sys].[dm_os_volume_stats]([f].[database_id], [f].[file_id]) AS vs
ORDER BY [vs].[logical_volume_name] ;

Of course, this will only provide information about drives to which the sqlinstance has databases allocated.

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #1413024

« Prev Topic | Next Topic »

15 posts, Page 2 of 2

««1 2

Permissions