Sunday, June 17, 2012

Disk Size Report of all SharePoint Databases using PowerShell into Mailbox


As an administrator I always use to find the database size of all the SharePoint DB. Which I use to do with the PowerShell script, but lately I got pissed off doing this same activity everyday so I decided finally, write a PowerShell script and using a windows scheduler automate this. So now I will get all these details on my mail every morning when I come to office.
 


So what the below script do………………

The script first section (1) is used to get information about the mail exchange. 

The script second section (2) is, for-each loop which gets content database of all the web application. This then writes this information on to a file.

The script third Section (3) is, for-each loop which get all the SharePoint database size details which is also appended to the file.

Finally the fourth section (4) sends this information on a mail as an attachment.
The result of this code will be:

So the full code for download is :
#Get SharePoint Content database sizes 
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue 
$date = Get-Date -Format "dd-mm-yyyy"
 
#Variables that you can change to fit your environment 
$TXTFile = "C:\ContentDatabase_$date.txt" 
$SMTPServer = "yourmailserver" 
$emailFrom = "SharePointReports@company.com" 
$emailTo = "youradmin@company.com" 
$subject = "Content Database size reports" 
$emailBody = "Daily/Weekly/Monthly report on Content databases"
 
$webapps = Get-SPWebApplication 
foreach($webapp in $webapps) 
{ 
    $ContentDatabases = $webapp.ContentDatabases 
    Add-Content -Path $TXTFile -Value "";
    Add-Content -Path $TXTFile -Value "----------------------------------------";
    Add-Content -Path $TXTFile -Value "Content databases for $($webapp.url)" 
    Add-Content -Path $TXTFile -Value "----------------------------------------";
    Add-Content -Path $TXTFile -Value "";
    foreach($ContentDatabase in $ContentDatabases) 
    { 
        $ContentDatabaseSize = [Math]::Round(($ContentDatabase.disksizerequired/1GB),2) 
        Add-Content -Path $TXTFile -Value "-     $($ContentDatabase.Name): $($ContentDatabaseSize)GB" 
    } 
} 
 
$databases = Get-SPDatabase
Add-Content -Path $TXTFile -Value "";
Add-Content -Path $TXTFile -Value "------------------------------------";
Add-Content -Path $TXTFile -Value "Content Size of Individual Databases";
Add-Content -Path $TXTFile -Value "------------------------------------";
Add-Content -Path $TXTFile -Value "";
foreach($database in $databases)
{
    Add-Content -Path $TXTFile -Value  " $($database.Name)  ---- $($database.disksizerequired/1024/1024) MB"    
}
 
if(!($SMTPServer) -OR !($emailFrom) -OR !($emailTo)) 
{ 
Write-Host "No e-mail being sent, if you do want to send an e-mail, please enter the values for 
            the following variables: $SMTPServer, $emailFrom and $emailTo." 
} 
else 
{ 
Send-MailMessage -SmtpServer $SMTPServer -From $emailFrom -To $emailTo -Subject $subject -Body $emailBody 
                 -Attachment $TXTFile 
}
Now How to use windows scheduler to automate this mailer on a daily basis you can refer to my earlier post http://sharepointdrive.blogspot.sg/2012/04/automating-sharepoint-site-backup-using_14.html

Please let me know if you face any difficulty in understanding or running this up. Also for more information on PowerShell go to http://sharepointrelated.com/