[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Help on writing a script for check ndf file size

Posted on 2016-07-27
2
Medium Priority
?
141 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
[X]
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
2 Comments
 
LVL 85

Accepted Solution

by:
oBdA earned 2000 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

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
What we learned in Webroot's webinar on multi-vector protection.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Suggested Courses

656 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