WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Thursday, October 4, 2012

Database Table Sizes in Powershell

Just wanted to share a very simple script that I have used far more than I ever thought I would.  The initial purpose was to get the average cost of a row of data, but this has been nice to find tables that are filling up quick, have a high row to space ratio, etc.


$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") "MySQLServerName"

$sqlServer.Databases | Where-Object{$_.Name -eq "BeerDB"} | 
    Select-Object Tables -ExpandProperty Tables | Sort-Object -Property RowCount -Descending  |
    Select Name, DataSpaceUsed , IndexSpaceUsed, RowCount,
         @{Label="TableSizeInMB";Expression={($_.DataSpaceUsed + $_.IndexSpaceUsed) / 1024}},
         @{Label="AvgSpacePerRowKB";Expression={($_.DataSpaceUsed + $_.IndexSpaceUsed) / $_.RowCount}}  | out-gridview -Title "SpaceUsed"

Wednesday, October 3, 2012

SQL Saturday 149 - Minnesota

A quick followup to SQL Saturday 149 at the University of MN.

First, my demos (the whole presentation is demos) are posted on the SQL Saturday site for anyone to play with.  The feedback was positive, my only non 4/5 scores made comments to the fact they didn't know Powershell, which is totally fine, given the session is labeled intermediate.

I had a few fascinating comments (ignoring someone taking a shot at my beard - Bob).

  1. Need more slides
  2. Refreshing!
  3. You are a natural speaker, its like you are having a conversation with me

Need more slides
1. I need to think about how I can put more "pictures" into my sessions I guess.  Currently, every picture is of beer bottles.  I don't like sessions where they just Powerpoint pictures the entire time when the session is about using a technology, like mine.  So I don't use Powerpoint at all, and just have lots of dialogue around scripts I have created or borrowed and their practicality.

2. This is what I am going for all the time - something useful, different and not too boring.

You are a natural speaker, its like you are having a conversation with me
3. Funny how I always feel nervous and think it is interesting that I seem to be watching myself speak while I am up there.  I get a little better each time, but comments like that throw you for a loop when you don't see yourself that way.  Very flattering, even when it comes from someone that has to work with you :)

I was able to learn some good information on Powerview, Index Stats and Dimensional Modeling and visit with several friends and some familiar faces, including coworkers past and future.  I helped with the Powershell lunch on a stick and we had some nice discussions.  I need to do a better job of facilitating discussions and talking to more people.  I only got a chance to say hi to some of the real luminaries in our industry and that is a bummer.

All in all, Mr @MNDBA, my opposite state counterpart in Twitter names, did a great job organizing and asked me specifically not to correlate the seedy motel I stayed at with SQL Saturday 149.  It was a Super 8 in Roseville that has a very colorful review - all I can say is I was happy no one stole my new tires :)  Note to self, if you need a hotel, don't wait till the day before to book it, you may end up in a place that actively advertises the fact your vehicle is not safe and there might be solicitation inside for services.

Tuesday, September 25, 2012

SQL Saturday 149, Beer, Powershell and SQL

I will be speaking at SQL Saturday 149 at the University of Minnesota campus on Saturday, September 29th.  This marks my third time presenting in Minnesota for this event and my first time ever delivering the same presentation twice.  I delivered the presentation in Madison in April and got reviews around the 4.5 out of 5 range.  I am hoping to improve this time on my pace (some said I talked to fast).  This is a fun presentation that will cover the myriad of bizarre and not so bizarre ways I use Powershell to get things done.    I can guarantee that beer will enter the presentation in some method.

In addition, I have been asked to help facilitate at the Powershell table during "lunch on a stick" as well.  Stop by if you want to talk about Powershell or beer or fishing or wild mushroom foraging. (aka things I know a little about)

The speaker lineup is really good this year and a nice blend of topics.  I will be trying to catch presentations by Dan English and Joe Sack for sure, not sure how the rest of my day will end up.

Hope to see you there -  stop me and say hi - I have a beard and will be wearing a speaker shirt (likely the only one with that combination :)

Thursday, August 2, 2012

Converting signed overpunch values in Powershell

Today I was faced with parsing some large text files to grab out numerical data (mainframe type source files) that are formatted with the signed "overpunch" technique.  Since I was doing the parsing in Powershell, I made a shorter version of the function I found online (a switch statement for each value) using a little regex.

Here it is, hope it saves you some time if ever needed.

    OverPunch is a mainframe technique to save one character for the + or - value on a numeric field by swapping the right most digit 
    {,A,B,C,D,E,F,G,H,I -> represent positive numbers 0-9
    },J,K,L,M,N,O,P,Q,R -> represent negative numbers 0-9 
    0000052} = -5.20
Function Convert-OverPunch

    #Grab everything passed in but the overpunch char(last char in field) and add implied decimal
    $mainval = "{0}.{1}" -f $inputval.Trim().Substring(0,($inputVal.Length - 2)) , $inputval.Trim().Substring($inputval.Length-2,1)
    #Grab the overpunch the character
    $punchChar = $inputval.Substring(($inputVal.Length -1),1)
    Write-Verbose "Punch Char $punchChar from $mainval"

    $mainval +=  switch -regex ($punchChar) 
                    ['{}'] {0} 
                    [AJ] {1} 
                    [BK] {2} 
                    [CL] {3} 
                    [DM] {4} 
                    [EN] {5} 
                    [FO] {6} 
                    [GP] {7}
                    [HQ] {8} 
                    [IR] {9}
                    default {$null}

    [decimal]$NumberVal = [System.Decimal]::Parse($mainval)

    IF($punchChar -match "[}JKLMNOPQR]") {
        $NumberVal *= -1;

    return $NumberVal

Thursday, July 12, 2012

Using Powershell to Monitor Files

Just another quick script, I had been left some instructions by a previous coworker that I need to check about 70 some folders each week to see if files had been placed there.  I got through about 20 of them and the lazy gene started to kick in, by the time I would have finished checking them the first week this script was done.  This particular script only looks in the root of each folder for a file (archive, etc subfolders) for a file that has been modified in the past 180 days.

$basepath = "\\mynetworkshare\ftpfiles\"
$path = Get-ChildItem -Path $basepath

# only want to get files that have been modified in the past 180 days
$dt = (Get-Date).AddDays(-180)

# Loop through each folder and only look in that folder for a file (use -recurse if you want to go deeper)
foreach($flder in $path)
    # Verify the $flder is a folder and not something else
    if((Test-Path $flder.FullName -pathtype container) -eq $true)
        $flder.GetFiles() | WHERE{$_.LastWriteTime -gt $dt} | Select Fullname, LastWriteTime

Sunday, April 22, 2012

SQL Saturday 118 - Madison WI followup

This might be the closest thing I will ever write to a blog post.  I just returned from my fourth SQL Saturday, this one in Madison, WI.  The MADPASS group did a great job putting together a really fun event (Jes, Leonard, Tony, Tim, Gina (and more I am sure).

Their were speakers from all over including several from Wisconsin.  I got to meet Powershell MVP Steve Murawski which was really cool and connect with some folks I know from the other side of the state.

I attended David Peterson Demystifying Powerpivot for the Sharepoint Admin presentation first, knowing nothing about either topic I learned some tips and tricks from someone who has clearly been in the trenches with the technology.  After that, I had to unfortunately miss the next set of presentations so I could get myself prepared before I hosted the Powershell "Cows of a Spot" lunch.  The post lunch presentation belonged to a fellow Minnesota PASS member Mark Vaillancourt and his MDX Trek presentation.  Mark is always funny and did a really nice job putting together a beginners course in MDX.  Given my career is headed that way I found it more intriguing, as he approached the topic from the beginners standpoint.  After that I attended Steve Hughes presentation on performance tuning SSAS.  I had no business taking up a seat in there as most of the conversation was over my head being my SSAS skills are nil.  Either way since I am moving that way in my career I went for osmosis.

Then it was my turn.

My presentation A Powershell Driven Life was really well attended, not sure if there was a chair left.  This was incredible considering I was going up against some good topics and speakers and it was the last session of the day.  I ended up with 34 evaluations turned in - 22 scores of 5, 8 scores of 4 and 4 scores of  3.  The average came out to 4.52 so I definitely did a better job than my previous presentation.  The comments I got were primarily positive with one person noting I went to fast - which I know I tend to do and need to work on.

My fourth SQL Saturday presentation involving a beer theme seems to resonate as folks left comments about the bad and good beers and talked with me afterwards.  It helps to add a fun element to the presentation to tie along with what I am trying to get across.  Speaking is really fun and ends up being a good benefit when job searching, as I have learned.  Given my career switch I am not sure when I will come up with a topic again that people will find useful.

So thanks again to the organizers and sponsors.  (Red Gate, Idera, PASS, Melissa Data, Tushaus, Rubix Consulting, I hope I got all of them.)

Monday, March 26, 2012

Putting on your SOX - CHECK_POLICY

Here is another quick one for those of us who need to prove that all SQL logins on a SQL Instance have the "Enforce password policy" checked.  I couldn't find the T-SQL way to get a list, but Powershell gave me the solution in -le 1 minute.

$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") "YourSQLInstance"

# Get All SQL Logins that don't have the enforce bit set to true.

$sqlServer.Logins | Where{$_.LoginType -eq "SqlLogin" -and $_.PasswordPolicyEnforced -eq $false} | Select Parent, Name, LoginType , PasswordPolicyEnforced

Happy Auditing!

Friday, January 27, 2012

"Trust but Verify" .... Your Backups.

Here is yet another script used in enterprise monitoring of SQL Server.  If you are in a big shop, you likely have a corporate backup solution in place, but what about development, test, etc?  You can use this script to checkout all the other servers in your environment to make sure they are getting a backup at least occasionally.

First up, include the SMO library and create a SQL SMO object.
$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") $sqlInstance

  This function takes an SMO object [$SmoSqlServer]
  The number of days before alerting the backup is old [$DaysToConsiderOld]
  Array of databases names to ignore.[$DBIgnoreList]
Function Get-DatabasesWithOldFullBackups([Microsoft.SqlServer.Management.Smo.Server] $SmoSqlServer
, [int] $DaysToConsiderOld, [array]$DBIgnoreList)
if($DaysToConsiderOld -gt 0) {$DaysToConsiderOld *= -1;}
$bkupDate = (Get-Date).AddDays($DaysToConsiderOld);

$SmoSqlServer.Databases | Where-Object {$_.IsSystemObject -eq $false -and $_.ReadOnly -eq "False" -and $_.IsAccessible -eq "True" } `
| Where {$DBIgnoreList -notcontains $_.Name}`
| Where {($_.LastBackupDate -le $bkupDate)}`
| Select-Object Name,RecoveryModel,@{Label="LastFull";Expression={$_.LastBackupDate};}`
,@{Label="LastDiff";Expression={$_.LastDifferentialBackupDate};}, Size

Make sure you verify those backups!

Sunday, January 22, 2012

Size Matters, especially when your database is full.

You know you can't trust developers to alert the DBA group when they are about to stuff 50 million orders into a database.  They think disk space is unlimited and the database has no constraints.  If you are in a shop that controls DB size, mostly to catch rogue processes (and developers) before they fill up a drive and affect more applications, you use the max size setting.  Here is the function, it accepts an SMO object for the SQL Server you are monitoring and the threshold you specify.  It returns any database data file that is over 85%(default threshold) full based on the max size.

$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") $sqlServerName

## Get Data Files with used space within threshold of max size
Function Get-DatabaseFilesBySpaceAvailable ([Microsoft.SqlServer.Management.Smo.Server] $SmoSqlServer
, [decimal] $sizeThreshold=0.85)
$sqlServer.Databases | Where-Object{$_.Status -eq "Normal"} `
| Select-Object FileGroups -ExpandProperty FileGroups `
| Select-Object Files -ExpandProperty Files  `
| Where-Object {$_.MaxSize -gt -1} `
| Where-Object {$_.UsedSpace -gt ($_.MaxSize * $sizeThreshold)} `
| Select @{Label="UsedMB";Expression={$_.UsedSpace/1024}},@{Label="SizeMB";Expression={$_.Size/1024}},@{Label="MaxSizeMB";Expression={$_.MaxSize/1024}},FileName

This function is one of many I use for my daily monitoring reports of all servers in the environment.