SQL Server Database Reports

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 FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA' $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:USERPROFILEDesktopDatabaseReport.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:USERPROFILEDesktopDatabaseReport.csv" $DetailsArray = $null 

submitted by /u/PerfectImpact
[link] [comments]

Leave a Reply