SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Powershell Script for Verifying Space

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.

[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.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Posted by Anonymous on 20 April 2010

Pingback from  Dew Drop – April 20, 2010 | Alvin Ashcraft's Morning Dew

Leave a Comment

Please register or log in to leave a comment.