Help on writing a script for check ndf file size

Hi Experts,
I have the below PowerShell SQL query to check SQL ndf file sizes.  Database abc have 4 ndf files. I have to generate an email alert if any ndf file size is more than 20000MB. As of now we are checking manually, but we want to do automate this. Please help me how to write the logic using powershell .

PS Query:
Invoke-SQLCmd -Query “Select
      a.FILEID,
      [FILE_SIZE_MB] =
            convert(decimal(12,2),round(a.size/128.000,2)),
      [SPACE_USED_MB] =
            convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
      [FREE_SPACE_MB] =
            convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
      NAME = left(a.NAME,15),
      FILENAME = left(a.FILENAME,30)
from
      dbo.sysfiles a” -Database abc  -ServerInstance Sing

Thanks,
Sreenivasa
tscharyAsked:
Who is Participating?
 
oBdAConnect With a Mentor Commented:
This should do the trick:
$Database = 'abc'
$ServerInstance = 'Sing'
$Limit = 20000	## MB
$Type = 'ndf'

$MailServer = "smtp.acme.com"
$MailFrom = "dbadmin@acme.com" 
$MailTo = "administrator@acme.com"
$MailSubject = "Warning: limit for database file size reached."

$MailBodyTemplate = @"
The following '$($Type)' database files of $($Database) on $($ServerInstance) are bigger than $($Limit)MB:
{0}
Report created by $($MyInvocation.MyCommand.Path) on $($ENV:ComputerName)
"@

$Query = @"
Select
	[FileId] = a.FILEID,
	[Name] = a.NAME,
	[FileName] = a.FILENAME,
	[FileSizeMB] = a.size / 128,
	[SpaceUsedMB] = fileproperty(a.name, 'SpaceUsed') / 128,
	[SpaceFreeMB] = (a.size - fileproperty(a.name, 'SpaceUsed')) / 128
From
	dbo.sysfiles a
"@

$AllFiles = Invoke-SQLCmd -Database $Database -ServerInstance $ServerInstance -Query $Query

If ($LargeFiles = $AllFiles | Where-Object {([System.IO.Path]::GetExtension($_.FileName) -eq ".$($Type)") -and ($_.FileSizeMB -gt $Limit)}) {
	$MailBody = $MailBodyTemplate -f ($LargeFiles | Format-List | Out-String)
	"Sending message to $($MailTo):" | Write-Host -ForegroundColor White
	$MailBody | Write-Host -ForegroundColor Gray
	Send-MailMessage -From $MailFrom -To $MailTo -Subject $MailSubject -Body $MailBody -SmtpServer $MailServer
}

Open in new window

1
 
tscharyAuthor Commented:
Excellent. I have tested and it is working fine. thanks a lot oBdA
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.