Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Help on writing a script for check ndf file size

Posted on 2016-07-27
2
105 Views
Last Modified: 2016-07-27
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
0
Comment
Question by:tschary
2 Comments
 
LVL 84

Accepted Solution

by:
oBdA earned 500 total points
ID: 41731193
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
 

Author Closing Comment

by:tschary
ID: 41731297
Excellent. I have tested and it is working fine. thanks a lot oBdA
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A brief introduction to what I consider to be the best editor for PowerShell.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question