Powershell Script for Verifying Space

, 2010-04-19

First let me say, I know my Powershell skills are sub-par. I’m working on it. Slowly but surely. That said, I had a problem to solve. It’s one that I could have done with TSQL, but it would be very ugly TSQL, probably involving dynamic queries, and even for admin scripts, I try to avoid that. So, I went for SMO and WMI wrapped by Powershell to solve the problem.

What was the problem you ask? We automate as many of our processes as we can. One process we do is resetting databases from production or other sources. Our processes work very well, but we occasionally run into a problem where the source system db has grown and the target system doesn’t have the required disk space. So, we needed a method for validating it. This is my first pass at that method. I know it needs work, but it’s functional, so I thought I’d share.

param($sourceserver,$targetserver,$databasename)

[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null

$source = New-Object (“Microsoft.SqlServer.Management.Smo.Server”) “$sourceserver”

$target = New-Object (“Microsoft.SqlServer.Management.Smo.Server”) “$targetserver”

$sourcedb = $source.Databases[$databasename]

$targetdb = $target.Databases[$databasename]

$sourcelogfiles = $sourcedb.logfiles

$targetlogfiles = $targetdb.logfiles

## walk through all the log files

foreach ($slf in $sourcelogfiles)

{

$tlf = $targetlogfiles[$slf.name]

##See if the target is smaller than the source

if ($slf.Size -gt $tlf.size)

{

##if the target is smaller, check the drive for free space

$drive = Split-Path $tlf.FileName -Qualifier

$server = $targetserver.Substring(0,$targetserver.IndexOf(“\”))

$driveinfo = gwmi win32_logicaldisk -computername $server |where-object {$_.name -like “$drive”}|select name,freespace

if ($slf.size -gt ($driveinfo.freespace + $tlf.Size))

{

Write-Output ”Drive: $drive has insufficient space. $databasename Source: $slf.size, Target: $tlf.size + $driveinfo.size”

}

}

}

$sourcedatagroups = $sourcedb.FileGroups

$targetdatagroups = $targetdb.FileGroups

##walk through all the data files

foreach ($sdg in $sourcedatagroups)

{

foreach ($sdf in $sdg.Files)

{

$tdg = $targetdatagroups[$sdg.name]

$tdf = $tdg.Files[$sdf.name]

if ($sdf.Size -gt $tdf.Size) {

$drive = Split-Path $tdf.FileName -Qualifier

$driveinfo = gwmi win32_logicaldisk -ComputerName $server | where-Object{$_.name -like “$drive”}|select name,freespace

if ($sdf.Size -gt ($driveinfo.freespace + $tlf.Size)) {

Write-Output ”Drive: $drive has insufficient space. $databasename Source: $slf.size, Target: $tlf.size + $driveinfo.size”

}

}

}

}

It’s pretty straight forward. It gets a connection to each SQL instance it’s passed, goes to the database in question, which in our situation will always have the same name, and walks the log files and data files, which again, will always have the same logical names and the same file groups. If there’s insufficient space, it kicks out a message. That’s it. Seems to work.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads