Checking SQL & SSIS Versions with Powershell

I'm currently in the process of applying SP3 + CU5 to all our SQL2005 Instances. I wanted to be able to quickly check whether the install was successful (we're experimenting with using SCCM to push to our standalone Instances).

Checking SQL was easy enough, just using @@version in a multi-query was enough. However SSIS is a little tougher, you can't check it via T-SQL and I don't have the patience to login to every machine and check it that way.

The solution I came up with uses SERVERPROPERTY('ProductVersion') to check the SQL build & [system.diagnostics.fileversioninfo]::GetVersionInfo() to check the Product version on the SSIS executable.

It returns a table with the Instance, Version, SQL & SSIS Builds and Notes (for example whether SSIS is installed & if installed do the SQL & SSIS Builds match). The code is here.

Finding Event ID's

A while back, I had the need to look for a particular EventID across all our SQL servers. The script I came up to do the work is here.

This was one of my earliest efforts so its a bit primitive, but works just fine. The main working portion of the script is a straighforward query of the Win32_NTLogEvent wmi class.

The fun part was converting the wmi time into something more usable/readable. I did this in a function that breaks the wmi time into discrete chunks & then strings them all back together in my preferred format. Additionally it modify's the new date/time to account for daylight savings.

Function Convert-WMITime
{
    param([string]$WMITime)

    #Break Date & Time into discreet elements
    $ds = [string]$WMITime.substring(21,4)
    $yr = [string]$WMITime.Substring(0,4)
    $mo = [string]$WMITime.substring(4,2)
    $dy = [string]$WMITime.substring(6,2)
    $tm = [string]$WMITime.substring(8,6)
    $hr = [string]$tm.Substring(0,2)
    $min = [string]$tm.substring(2,2)
    $sec = [string]$tm.substring(4,2)
    
    #Create string in desired format
    $s = "$mo/$dy/$yr " + $hr + ":" + $min + ":" + $sec
    
    #cast result as DateTime type
    $result = [DateTime]$s
    
    #Account for DST
    if ($cp = $ds.Contains("-240"))
    {
        $result = $result.AddHours(-1)
    }
    
    return $result
}

Get Files, Sizes & Percent Free by DB

This week I've been working on our space needs for the upcoming budget year. As part of that effort I spent a few minutes writing a script to return infrmation on database files; including file group, logical name, physical name, allocated size, used size & percent free space for each file. The code is here.

In the next few weeks I'm planning to do a write up on the entire process I used to generate my space requests, it involves several pieces, inluding a process to populate database size (this is not mine).

Generating log file shrinks

We had a situation where we needed to shrink all log files for all databases on a couple of drives preparatory to moving all the files to a new LUN which would then be mounted as the same drive letter. This was being done to correct improperly aligned partitions on the drives causing performance issues.

When disk partitions are not properly aligned there is a significant performance degradation (30-40%) on the disk, see Chad Miller's blog post on this for more detail. Additionally, correcting this will reduce throughput to the SAN array which can benefit overall performance of the array.

I don't normally recommend (and would typically strongly oppose) shrinking files, but in this case we had a limited time frame to make this move & shrinking these files meant 150GBs less data to move which was a make or break for the timeline we were allowed to complete the work in.

Since these were all log files I choose to shrink them all to 1MB for maximum benefit. For the sake of paranoia (I like to double/triple/quadruple check scripts prior to running in Production) I had it generate the statements to run & then ran them manually. To customize for your drives you would just change the T-SQL defined in the @sql variable. The script is here.