WIDBA

WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Tuesday, March 19, 2013

Solving the Space Wars via Powershell

The constant battle as the consumption of data grows is storage space.  When a disk space alert goes off, you generally will jump on the server over RDP and start hunting through the drive for what has caused the growth to hit the "wake me up" limit.  

This has been an occurrence in my career for a number of years, but seems to be happening more and more often.  As my career has progressed, I create more and more monitoring for various things that cause outages ultimately but this was never put together.  Given the simplicity of this script, I wish I had done it when my primary job was being a DBA.  Sure would have helped on our development and QA servers with finding what data file grew out of sight, or what log file had gotten carried away.

So here it is, a pretty basic thing that just dumps the information to the out-gridview control.  This could be shortened and condensed but that would sacrifice the readability and that is more important :)

Note: You may need a second variable for the SQL Instance name if not default
 $Server = "DBServer"  
 [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")  
 
 #Load up the SMO for the server      
 $sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") $Server  
 
 # Grab list of drives - this should get us mount points as well as regular drives (not tested)  
 [Array]$Drives = Get-WmiObject -ComputerName $server -Class Win32_Volume |   
     Select -Property DriveLetter, Label, @{Name=’FreeSpaceMB’;Expression={$_.FreeSpace/1MB} } |  
     Where-Object{$_.DriveLetter -ne $null}   

 # Grab the data/index files for each database  
 $dbFiles = $sqlServer.Databases |   
   Select-Object FileGroups -ExpandProperty FileGroups |  
   Select-Object Files -ExpandProperty Files |  
   Select FileName,Name,UsedSpace,Size  

 # Grab the log Files for each database    
 $dbFiles += $sqlServer.Databases |   
   Select-Object LogFiles -ExpandProperty LogFiles |  
   Select FileName,Name,UsedSpace,Size  

 #Container to hold the information in    
 [Array]$diskSummary=@()  

 #Loop through each drive ordered by free space on the drive  
 foreach($drive in $drives | Sort-Object FreeSpaceMB )  
 {  
   Write-Host $drive.DriveLetter  
   Write-Host $drive.Label   
   #Grab the size and used space for each database file on the current drive  
   $size = ($dbFiles | Where-Object{$_.FileName.StartsWith($drive.DriveLetter)} | Measure-Object Size -sum).Sum   
   $used = ($dbFiles | Where-Object{$_.FileName.StartsWith($drive.DriveLetter)} | Measure-Object UsedSpace -sum).Sum      
   #Add that information to the array for output later  
   $diskSummary += $drive | Select DriveLetter,Label,FreeSpaceMb,@{Label="DBFileSizeMB";Expression={$size/1024}},@{Label="DBFileUsedMB";Expression={$used/1024}}  
 }    
 #Show the files and disk information  
 $dbFiles | Sort-Object DriveLetter,FreeSpaceMB -descending | out-gridview -Title "DB Files"  
 $diskSummary | out-gridview -Title "Disk Summary"  

So there is the basics to get you started.

I will be speaking on this and a lot more bright and early on April 6th, 2013 at SQL Saturday 206 in


Hope to see you there.

No comments:

Post a Comment