Thursday, August 27, 2015

[Presenting] WMI in KC in Oct at SQLSat!

Got the nod.  So, I'm happy to announce I'll be presenting at SQL Saturday 444, Aka SQL Sat KC 2015 (#SQLSatKC, @KCSQLSaturday), on October 3rd, 2015, at Rockhurst University. 

My presentation is a new one, and so I'm still finding neat new things to show everyone.  The subject is WMI, which most SQL folk don't know.  Which makes sense, as it's mostly a Windows technology, meant to give sysadmins info about their boxen.  HOWEVER!  We'll be tackling why you care about it, as well as cool things you can do with it.

More details forthcoming.

But overall, if you're anywhere near Kansas City, I heartily encourage you to attend: flights there are cheap, the day-before BBQ is EPIC, and they have a really good selection of speakers.  In part it's because the KC folk are awesome, partly because their SQLSat is well organized, and finally because they love having speakers from far and wide, which encourages people like me to drive 10 hours to present there.  Oh, and BBQ.  MmmmmMMmmmm BBQ.

Offtopic: code is available on github

Just an FYI, since I ran into the problem today. 

Many of my posts contain code.  In order to make it easier to view/copy the code, and so I have a backup of it somewhere, I store my code on GitHub.  Specifically, I use gists.

So, most of my newer code is available via

Additionally, I use inline scripts to make this code available.  So if you're using a cross-site-blocker such as RequestPolicy, you'll need to enable in order to view the code.

Wednesday, July 29, 2015

[Eventlog_Tracking] Send a report out when more than 100 messages in a minute

I'm trying to curb the excess eventlog messages in my environment.  Now that I'm using WQL/WMI/PoSH to capture it (writing the presentation now - tentative name is "WMI for the DB Guy/WQL for the DB Gal"), and filtering it as it comes in, I need a way to *AHEM* encourage people to suggest filters, and get rid of error messages. 

So, they're about to get emails with details when it's over 100 in a minute.  With the most frequent errors listed.  It's got a @threshold parameter so you can turn it down if you don't have too many errors.

Monday, July 27, 2015

[SSIS 2012] SQL Sentry - using custom code to send detailed SSISDB failure emails (part 2)

Since, going back through my old post (, I found that it was hard to figure out where you add the condition to SQL Sentry event manager. I need to add this to SQLSentry's just-announced condition repository.

Step 1: grab code from below, and put it in a database of your choosing.

Step 2: index!  You probably need something like this index.  Mind the blocking during creation

CREATE NONCLUSTERED INDEX ncidx__internal_event_messages__event_message_id
ON [internal].[event_messages] ([package_name])
INCLUDE ([event_message_id])

Step 3: open SQL Sentry, go to the SSISDB computer in your list of servers, then expand.  Click on the main subgroup, which has the DB icon, the server name , the SQL Server Version, then "(EM/PA)" if you're running both.

Step 4: expand "Conditions".  View->Conditions, or it's already a tab.

Step 5: Under "Explicit", there's an "Add" button.  Click

Step 6: In "Actions Selector: General Conditions", on the right side of the window find "SQL Server Agent Job", then "SQL Server Agent Job: Failure", then select "Execute SQL".

Step 7: Under server, choose the same server (you could run the code on a different box, but the data we need is on that box).

Step 8: For T-SQL Command Text, enter (I use a different name than in the original post, since I use both variants, depending on the server)

exec dba_utils.dbo.ssis_job_failure_info_fromjobname_SqlSentry @job_name = '<%ObjectName%>'

Step 9: Click in some other server or window, so that the details are saved. 

Step 10: Test! 

Tuesday, July 7, 2015

[Presenting] 24 Hours of PASS 2015 - how I did

Had to share.  From my presentation, here were the results of the survey (if you stuck around until the very end).  It included the comments as well, which were quite complimentary.  I smirked/winced at the comment about chatting me up at PASS, since my presentation wasn't selected and so the odds of me going are pretty damn slim.

But, like I said, I'm very pleased with this.  I know I had 237 people at one point, so it probably peaked at 250-275.  126 responses is better than I'd hoped for.  And Obviously I need to work on my presentation skills some more!  : )