Solved

Help on writing a script for check ndf file size

Posted on 2016-07-27
2
91 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 83

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

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
A procedure for exporting installed hotfix details of remote computers using powershell
The viewer will learn how to count occurrences of each item in an array.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

772 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