Solved

Help on writing a script for check ndf file size

Posted on 2016-07-27
2
75 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 82

Accepted Solution

by:
oBdA earned 500 total points
Comment Utility
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
Comment Utility
Excellent. I have tested and it is working fine. thanks a lot oBdA
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A procedure for exporting installed hotfix details of remote computers using powershell
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now