One of my blog reader requested me to get the DB details(Server,DBName,Size,SpaceAvailable,LogSize,PercentUsed Log Space,compatiblilty mode, recovery model etc:-) using PowerShell hence this post is written.
The power of PowerShell is to capture an output of DBCC commands and store it an variable for later processing of the desired result
I’m storing the log usage details from DBCC SQLPERF SQL command. Executing the below command gives us the required details and then join with DB to fetch other requested details
$db.ExecuteWithResults("DBCC SQLPERF(LOGSPACE)").Tables[0]
CODE: Change the below marked fields to get an output as per your environment
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
#let's get our list of servers. For this, create a .txt files with all the server names you want to check.
$sqlservers = Get-Content "f:\powersql\server.txt";
$FileOutput= "f:\PowerSQL\FileOutput.htm"
New-Item -ItemType file $FileOutput -Force
#we'll get the long date and toss that in a variable
$datefull = Get-Date
#and shorten it
$today = $datefull.ToShortDateString()
#let's set up the email stuff
$from="pjayaram@appvion.com"
$to="pjayaram@appvion.com"
$smtp_address = "Valid SMTP Address"
#Formatting a message body
Function writeHtmlHeader
{
param($fileName)
$date = ( get-date ).ToString('yyyy/MM/dd')
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>SQL Job Failure 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='10' height='25' align='center'>"
add-content $fileName "<font face='tahoma' color='#003399' size='4'><strong>FileOutput - $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='LEFT'>ServerName</td>"
Add-Content $fileName "<td width='50%' align='left'>DBName</td>"
Add-Content $fileName "<td width='10%' align='left'>DBSizeGB</td>"
Add-Content $fileName "<td width='10%' align='left'>DBSpaceAvailableGB</td>"
Add-Content $fileName "<td width='10%' align='left'>LogSizeMB</td>"
Add-Content $fileName "<td width='10%' align='left'>LogSpaceUsedPer</td>"
Add-Content $fileName "<td width='10%' align='left'>LogFreeSpaceMB</td>"
Add-Content $fileName "<td width='10%' align='left'>Collation</td>"
Add-Content $fileName "<td width='10%' align='left'>RecoveryModel</td>"
Add-Content $fileName "<td width='10%' align='left'>AutoShrink</td>"
Add-Content $fileName "</tr>"
}
Function writeHtmlFooter
{
param($fileName)
Add-Content $fileName "</body>"
Add-Content $fileName "</html>"
}
Function writeInfo
{
param($filename,$sqlserver,$Name,$DBsize,$dbSpaceAvailable,$logSizeMB,$SpaceUsedPercent,$collation,$recoveryModel,$autoShrink)
Add-Content $fileName "<tr>"
Add-Content $fileName "<td align=left ><b>$instanceName</td>"
Add-Content $fileName "<td align=left ><b>$name</td>"
Add-Content $fileName "<td align=left ><b>$DBsize</td>"
Add-Content $fileName "<td align=left ><b>$dbSpaceAvailable</td>"
Add-Content $fileName "<td align=left ><b>$logSizeMB</td>"
Add-Content $fileName "<td align=left ><b>$logFreeSpaceMB</td>"
Add-Content $fileName "<td align=left ><b>$SpaceUsedPercent</td>"
Add-Content $fileName "<td align=left ><b>$collation</td>"
Add-Content $fileName "<td align=left ><b>$recoveryModel</td>"
Add-Content $fileName "<td align=left ><b>$autoShrink</td>"
Add-Content $fileName "</tr>"
}
writeHtmlHeader $FileOutput
Add-Content $FileOutput "<table width='100%'><tbody>"
Add-Content $FileOutput "<tr bgcolor='#CCCCCC'>"
Add-Content $FileOutput "<td width='100%' align='center' colSpan=10><font face='tahoma' color='#003399' size='2'><strong> DB Details</strong></font></td>"
Add-Content $FileOutput "</tr>"
writeTableHeader $FileOutput
foreach($sqlserver in $sqlservers)
{
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$serverInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "$sqlserver"
foreach($db in $serverInstance.Databases)
{
$db1=$db.ExecuteWithResults("DBCC SQLPERF(LOGSPACE)").Tables[0]
$dbSpaceAvailable = $db.SpaceAvailable/1KB
$collation=$db.Collation
$compatiblity=$db.CompatibilityLevel
$size=$db.Size
$db.RecoveryModel
if ($db.AutoShrink -eq "True")
{
$autoshrink="Yes"
}
else
{
$autoshrink="No"
}
if($db.RecoveryModel -eq 1)
{
$RecoveryModel='Full'
}
elseif($db.RecoveryModel -eq 3)
{
$RecoveryModel='Simple'
}
elseif($db.RecoveryModel -eq 2 )
{
$RecoveryModel='Bulk Logged'
}
foreach($db2 in $db1)
{
if($db.name -eq $db2."database Name")
{
$Name=$db.name
$DBSize= $size/1KB
$DBSize = “{0:N3}” -f $DBSize
$dbSpaceAvailable="{0:N3}" -f ($dbSpaceAvailable/1KB)
$LogSizeMB = "{0:N3}" -f ($db2."Log Size (MB)")
$SpaceUsedPercent = $db2."Log Space Used (%)"
$logFreeSpaceMB="{0:N3}" -f ($db2."Log Size (MB)" -($db2."Log Size (MB)"*$db2."Log Space Used (%)")/100)
$collation=$collation
$recoveryModel=$recoveryModel
$autoShrink=$autoShrink
}
}
write-host $FileOutput, $sqlserver,$Name,$DBsize,$dbSpaceAvailable,$logSizeMB,$logFreeSpaceMB,$SpaceUsedPercent,$collation,$recoveryModel,$autoShrink
writeInfo $FileOutput $serverInstance $Name $DBsize $dbSpaceAvailable $logSizeMB $logFreeSpaceMB $SpaceUsedPercent $collation $recoveryModel $autoShrink
}
}
Add-Content $FileOutput "</table>"
writeHtmlFooter $FileOutput
$date = ( get-date ).ToString('yyyy/MM/dd')
$body = Get-Content $FileOutput
$body = New-Object System.Net.Mail.MailMessage $from,$to, "Job Status - $Date", $body
$body.isBodyhtml = $true
$smtp = new-object Net.Mail.SmtpClient($smtp_address)
$smtp.Send($body)
Output:-
