Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

getting total disk space Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2013 2:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 5,146, Visits: 4,956
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
Posted Tuesday, January 29, 2013 4:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:53 AM
Points: 6,196, Visits: 13,353
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"
Post #1412884
Posted Tuesday, January 29, 2013 4:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 5,146, Visits: 4,956
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.

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.


Thanks Perry, added to my list of further reading.




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 #1412887
Posted Tuesday, January 29, 2013 6:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:17 AM
Points: 1,139, Visits: 811
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

# 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 "td {"
add-content $fileName "font-family: Tahoma;"
add-content $fileName "font-size: 11px;"
add-content $fileName "border-top: 1px solid #999999;"
add-content $fileName "border-right: 1px solid #999999;"
add-content $fileName "border-bottom: 1px solid #999999;"
add-content $fileName "border-left: 1px solid #999999;"
add-content $fileName "padding-top: 0px;"
add-content $fileName "padding-right: 0px;"
add-content $fileName "padding-bottom: 0px;"
add-content $fileName "padding-left: 0px;"
add-content $fileName "}"
add-content $fileName "body {"
add-content $fileName "margin-left: 5px;"
add-content $fileName "margin-top: 5px;"
add-content $fileName "margin-right: 0px;"
add-content $fileName "margin-bottom: 10px;"
add-content $fileName ""
add-content $fileName "table {"
add-content $fileName "border: thin solid #000000;"
add-content $fileName "}"
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
Posted Tuesday, January 29, 2013 6:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
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


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1413024
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse