SQL DB Details Of Multiple Servers Using PowerShell

 

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

SQLDBSize

 

Advertisements

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell, SQL and tagged , . Bookmark the permalink.

4 Responses to SQL DB Details Of Multiple Servers Using PowerShell

  1. RK says:

    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

    • 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

  2. Michael says:

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s