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:-
Prashanth Jayaram 
Hi Prashanth,
Seems to be issue at logical part.I’m not getting exact DBSize and DBSpaceAvailable. out put
I have cross checked script out put and sp_hepdb database name out put, both are not matching.
I’m looking for Available free space in log file.not for LogSpaceUsedPer
LikeLike
Hi RK,
The size are in GB. You need to convert the sp_helpdb output to GB and verify.
Also, the reason for not including the logspaceUsedPer….I want you to learn and put some extra effort in making a small piece of code. It will help you in some way going forward.
Anyways,I have added the logic to find LogSpaceUsedPer for you.
-Prashanth
LikeLike
Hi,
I get this error:
Add-Content : Cannot bind argument to parameter ‘Path’ because it is null.
At line:24 char:13
+ Add-Content $fileName “”
+ ~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [Add-Content], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.AddContentCommand
Pls advise. Thanks.
LikeLike
Hi Michael,
Its a problem with the wrong file name.
Can you try now? I have corrected the script. Let me know in case of any further issue.
–Prashanth
LikeLike