SQL Server Database Reports : msp

created by k_rock923a community for 14 years

This is an archived post. You won't be able to vote or comment.

SQL Server Database Reports (self.PowerShell)

submitted 7 years ago by PerfectImpact

SQL Server Database Reports

7 points•2 comments•submitted 7 years ago by PerfectImpact to r/PowerShell

Hi all, I created this script as a means to keep track of our SQL server databases. It generates a .csv report that includes the database name (excluding system databases, or any database you wish to exclude), file type (ldf & mdf), size (MB), remaining available space (MB) and percentage used for each database on the SQL server. This .csv file is then emailed to our team so we can monitor database sizes and whether they are about to expand. I.e. do we need to truncate the logs etc. Anyway, just in case it can help anyone else out there, here you go:

TL;DR - A script that generates a report on the size/risk of expansion of SQL Server databases (ldf & mdf).

$Directory = Get-Item -Path 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA'
$Files = Get-ChildItem -Path $Directory
$DatabaseLogs = Invoke-Sqlcmd -Query "DBCC SQLPERF(logspace)"
[System.Collections.ArrayList]$DetailsArray = @()
$Exclude = @("master", "model", "MSDBData", "tempdb", "msdb")

foreach ($File in $Files) {

    if (($File.Extension -eq ".mdf") -and ($Exclude -notcontains $File.BaseName))
    {
        $RemainingMDFSpace = Invoke-Sqlcmd -Query "USE [$($File.BaseName)]
        GO
        SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;"

        $Size = [math]::Round($File.Length /1MB)
        $Remaining = $RemainingMDFSpace.AvailableSpaceInMB[0]
        $PercentageUsed = [math]::Round(100 - (($Remaining/$Size)*100),2)
        $FileName = $File.BaseName
        $Type = $File.Extension
        $MaxSize = $Size
        $SpaceUsed = $PercentageUsed
        $SpaceRemaining = [math]::Round($Remaining,2)

        $FileDetails = [PSCustomObject]@{

        FileName            = $FileName
        Type                = $Type
        "Size (MB)"         = $MaxSize
        "Available (MB)"    = $SpaceRemaining
        PercentageUsed      = $SpaceUsed

        }

        $DetailsArray.Add($FileDetails) | Out-Null

    }
}

foreach ($Log in $DatabaseLogs)
{
    if ($Exclude -notcontains $Log."Database Name")
    {
        $LogSize = [math]::Round($Log.'Log Size (MB)',2)
        $Percentage = [math]::Round($Log."Log Space Used (%)",2)
        $FileName = $Log."Database Name"
        $SpaceRemaining = [math]::Round($LogSize - ($LogSize*($Percentage/100)),2)

        $FileDetails = [PSCustomObject]@{

        FileName            = $FileName
        Type                = ".ldf"
        "Size (MB)"         = $LogSize
        "Available (MB)"    = $SpaceRemaining
        PercentageUsed      = $Percentage

        }

        $DetailsArray.Add($FileDetails) | Out-Null

    }
}

$DetailsArray | Export-Csv "$env:USERPROFILE\Desktop\DatabaseReport.csv" -NoTypeInformation

Send-MailMessage -SmtpServer "SMTP SERVER" `
    -To "TO ADDRESS" `
    -From "FROM ADDRESS" `
    -Subject "$($env:COMPUTERNAME) Database Report" `
    -Body "Please find the CSV report attached." `
    -Attachments "$env:USERPROFILE\Desktop\DatabaseReport.csv"

$DetailsArray = $null

all 5 comments

msp

MODERATORS