SQLvariations: SQL Server, a little PowerShell, maybe some Hyper-V Rotating Header Image

PowerShell 2.0 for XP and Up

If you haven’t heard the news, PowerShell 2.0 for Windows XP, Windows Server 2003, Vista, and Windows Server 2008 R1 has now been released.

This is huge.  Especially for SQL folks.

It’s huge because  I can once again share my PowerShell scripts with colleagues who use different operating systems.   More on why it’s huge hereHalr9000.com

Where Can I get it?!?!  Right here:
http://support.microsoft.com/kb/968929

I’m a SQL Server DBA, why do I care about PowerShell?..  

The other day one of the Sys. Admins. came to me and asked me how large our data files are so that he could calculate the drive spaced need for migrating to our new SAN.  I of course dropped what I was doing to gather this info for him.  Now I went through SQL to get the info but because we still have a few SQL Server 2000 machines, a few of them were undercounted!  When the Sys. Admin.  compared our lists he found one discrepancy amidst the over 600 data and log files I listed.  One database only had 4 files but they were 4 massive files (100+GB).  If Only I could cross-reference this information with what the O/S thought was there!  I’m working on a recusive script to cycle through mount points and give me a system wide total.  What I’ve put together so far is a script to calculate the total the size of all the .mdf’s in any directory you point to: 

cd C:\Program` Files\Microsoft` SQL` Server\MSSQL10.KATMAI\MSSQL\DATA #Change me to YOUR directory

$c=0
$l=0
foreach ($f in dir *.mdf) {$c += 1; $l += $f.length}
"File Count: $c"
"Total size of .mdf files in Bytes: $l"
Need to count your .ndf and .ldf files too? Just append a “, *.ndf” or “*.ldf”  immediately following the *.mdf
Now I don’t know if a PowerShell expert would suggest this approach, I just know that I tried it on my machine and it worked! (However, if  you’re still  storing your .ndf’s and .ldf’s on the same drive let alone same directory as your .mdf files there is a whole other conversation we should have.)
Oh you need to output that to a text file so that you can email it to someone?  Just change out the last two lines (the ones in brown) for this:
  
"File Count: $c Total size of .mdf files in Bytes: $l" > c:\Temp\MydbFileSized.txt

Now I know that formatting that bytes number with some commas or at least translating it into MB would be nice but that will have to wait until another post.  Until then, have fun with what you got and tell me what you need.

@SQLvariant

Comments are closed.